Database schema

Quizably stores its data in eight custom tables plus a few WordPress options and transients. This page documents them as of plugin version 1.2.0, schema version 1.3.0 (unchanged since 1.0.0), taken from Schema.php and the repository classes.

Read this first

• Treat the schema as read-only. Do not write to these tables directly. Rows are tied together in code (for example, deleting a quiz deletes its questions, answers, results, submissions and leads in the right order, and imports remap IDs). Use the REST API to create and change data and the hooks to react to events.
• Reading with SQL for reporting is reasonable, but column layouts can change in a minor release. The plugin upgrades itself through dbDelta() when quizably_db_version differs from the code’s schema version.
• All table names are {$wpdb->prefix}quizably_{name}. With the default prefix: wp_quizably_quizzes and so on.
• Timestamps are stored as DATETIME in UTC (current_time( 'mysql', true )). The REST API converts them to ISO 8601 with a Z.
• JSON columns are LONGTEXT holding wp_json_encode() output. They are not MySQL JSON type columns.

ER-style summary

quizzes 1 ---- * questions 1 ---- * answers
   |  \                                 ^ personality_result_id (soft)
   |   \---- * results <----------------+
   |
   +---- * submissions * ---- 0..1 leads      (submissions.lead_id, soft)
   |          (result_id -> results.id, soft)
   +---- * leads                              UNIQUE (quiz_id, email)

question_bank        standalone templates, copied into questions on insert
settings             key/value store for global settings

No foreign key constraints exist. All relations are by convention, enforced in PHP. Orphans are possible, for example a submission keeps a dangling lead_id after the lead is deleted from the admin.

Tables

quizzes

Column	Type	Meaning
id	BIGINT UNSIGNED, PK, auto increment	Numeric ID used by admin routes and the [quizably_quiz] shortcode.
uuid	CHAR(36), unique	Public identifier used by public routes and hydration markup.
title	VARCHAR(255)	Quiz title. Stored as submitted.
slug	VARCHAR(255), unique	Must be unique.
type	VARCHAR(32), default personality	personality, trivia, survey or poll have built-in scorers.
status	VARCHAR(16), default draft	draft, published, archived. Only published quizzes render and accept submissions.
template	VARCHAR(64), default classic	Template key.
settings	LONGTEXT NULL	JSON object, see below.
design	LONGTEXT NULL	JSON object, see below.
view_count	BIGINT UNSIGNED, default 0	Incremented on each PHP render of the shortcode, block or iframe embed page. Cached pages do not count.
author_id	BIGINT UNSIGNED, default 0	Creating user ID. Never sent to the browser.
created_at, updated_at	DATETIME	UTC.

Keys: PRIMARY (id), UNIQUE uuid, UNIQUE slug, KEY status_type (status, type), KEY author (author_id).

questions

Column	Type	Meaning
id	BIGINT UNSIGNED, PK
quiz_id	BIGINT UNSIGNED	Parent quiz.
bank_origin_id	BIGINT UNSIGNED NULL	Set when the question was copied from the question bank.
type	VARCHAR(32), default single	The player renders single, multi or multiple, true_false or truefalse, dropdown, short_text and rating.
title	TEXT	Question text. Stored as submitted and rendered as HTML by the player.
description	TEXT NULL	Rich text, filtered with wp_kses_post on save through the REST API.
media_url	VARCHAR(2048) NULL
media_type	VARCHAR(16) NULL
required	TINYINT(1), default 0
position	INT, default 0	Sort order.
settings	LONGTEXT NULL	JSON, see below.
logic	LONGTEXT NULL	JSON branching rules, see below.

Keys: PRIMARY (id), KEY quiz_pos (quiz_id, position), KEY bank_origin (bank_origin_id).

answers

Column	Type	Meaning
id	BIGINT UNSIGNED, PK
question_id	BIGINT UNSIGNED	Parent question.
label	TEXT	Option text shown to the visitor.
value	VARCHAR(255), default empty	Slug-like value.
is_correct	TINYINT(1), default 0	Used by the trivia scorer. Never sent to the browser.
points	INT, default 0	Trivia points for a correct pick. Never sent to the browser.
weights	LONGTEXT NULL	JSON. Stored and returned to admin, not read by the built-in scorers.
personality_result_id	BIGINT UNSIGNED NULL	Result that gets +1 when this answer is picked (personality quizzes). Sent to the browser.
media_url	VARCHAR(2048) NULL
position	INT, default 0

Keys: PRIMARY (id), KEY question_pos (question_id, position), KEY personality (personality_result_id).

results

Column	Type	Meaning
id	BIGINT UNSIGNED, PK
quiz_id	BIGINT UNSIGNED
title	VARCHAR(255)	Stored as submitted. Rendered as HTML by the player and sent raw in webhook payloads.
content	LONGTEXT NULL	Rich text, wp_kses_post on save.
image_url, redirect_url, cta_url	VARCHAR(2048) NULL	redirect_url sends the visitor away instead of showing the result.
cta_label	VARCHAR(255) NULL
score_min, score_max	INT NULL	Inclusive trivia range. NULL means open ended on that side.
conditions	LONGTEXT NULL	JSON. Stored, never read by the built-in scorers, never sent to the browser.
settings	LONGTEXT NULL	JSON per-result presentation (background, overlay, chart, share and alignment options).
position	INT, default 0	Tie-break and match order (position, id).

Keys: PRIMARY (id), KEY quiz_pos (quiz_id, position).

submissions

One row per quiz run. A row is created when the player mounts, so many rows stay in_progress forever.

Column	Type	Meaning
id	BIGINT UNSIGNED, PK
uuid	CHAR(36), unique	The only handle the browser holds.
quiz_id	BIGINT UNSIGNED
user_id	BIGINT UNSIGNED NULL	Column exists. The plugin never sets it.
lead_id	BIGINT UNSIGNED NULL	Set when the lead form is submitted.
answers	LONGTEXT NULL	JSON array, see below.
score	INT NULL
result_id	BIGINT UNSIGNED NULL	Matched result. NULL when none matched.
result_breakdown	LONGTEXT NULL	JSON, shape depends on scorer.
ip_hash	CHAR(64) NULL	Salted, truncated hash. Only 16 hex characters are written.
user_agent	VARCHAR(255) NULL	Raw User-Agent header, cut to 255 characters.
utm	LONGTEXT NULL	JSON with up to five utm_* keys.
started_at, completed_at	DATETIME NULL	UTC.
status	VARCHAR(16), default in_progress	in_progress or completed. There is no abandoned status.

Keys: PRIMARY (id), UNIQUE uuid, KEY quiz_status (quiz_id, status, completed_at), KEY lead (lead_id).

leads

Column	Type	Meaning
id	BIGINT UNSIGNED, PK
quiz_id	BIGINT UNSIGNED
email	VARCHAR(320)
name	VARCHAR(255) NULL
phone	VARCHAR(64) NULL	Accepted by the API. The bundled form does not collect it.
extra_fields	LONGTEXT NULL	JSON object of up to 20 string fields.
consent_gdpr	TINYINT(1), default 0
double_optin_verified	TINYINT(1) NULL	NULL the quiz did not use double opt-in when the lead was saved, 0 pending (confirmation email sent), 1 confirmed. Analytics counts only NULL or 1. The Leads screen lists all three.
integration_sync_status	LONGTEXT NULL	JSON map { integration: status }. The plugin does not write it during normal operation.
created_at	DATETIME	UTC.

Keys: PRIMARY (id), UNIQUE quiz_email (quiz_id, email), KEY quiz (quiz_id). The unique key means one lead per email per quiz. Repeat submissions update the existing row (name, phone, extra fields and consent), and created_at is kept. The same email on a different quiz is a separate lead.

settings

Column	Type	Meaning
setting_key	VARCHAR(128), PK
value	LONGTEXT NULL	Scalars as strings. Arrays and objects are JSON. On read, any value starting with { or [ is JSON decoded.

This is a plugin-owned key/value table, separate from wp_options.

question_bank

Column	Type	Meaning
id	BIGINT UNSIGNED, PK
uuid	CHAR(36), unique
type	VARCHAR(32), default single
title	TEXT
description	TEXT NULL
media_url	VARCHAR(2048) NULL
media_type	VARCHAR(16) NULL
settings	LONGTEXT NULL	JSON.
answers	LONGTEXT NULL	JSON array of answer objects. There is no per-bank answers table.
tags	VARCHAR(512) NULL	Comma separated, lowercase slugs.
author_id	BIGINT UNSIGNED, default 0
usage_count	BIGINT UNSIGNED, default 0	Incremented on each insert into a quiz.
created_at, updated_at	DATETIME	UTC.

Keys: PRIMARY (id), UNIQUE uuid, KEY type_updated (type, updated_at), KEY author (author_id). Inserting into a quiz copies the data. There is no live link back.

JSON column shapes

These are the shapes the code reads and writes. The builder app owns most of the free-form settings and design objects, so treat unknown keys as private.

quizzes.settings

Keys read by the player or server (not exhaustive):

{
  "randomize_questions": true,
  "question": { "auto_advance": true },
  "result": { "review_answers": true },
  "screens": { "intro_title": "Optional override of the quiz title" },
  "optin": {
    "placement": "end",
    "skippable": false,
    "double_optin": false
  },
  "integrations": {
    "webhook": { "enabled": true, "url": "https://example.com/hook", "secret": "optional" }
  }
}

• optin is the stored key name for the lead form. placement is start, end or none. The legacy values gate (read as start) and optional (read as end, skippable) still exist in old rows.
• integrations.<key> entries are what the dispatcher uses to decide whether to call an integration. See Hooks and filters.
• The integrations.webhook.secret is stored in plain text in this column and is returned by GET /quizzes/{id}.

quizzes.design

Keys the player reads include colors (primary, background, text, accent), background_opacity, font_family, font_size, button_style (rounded, pill, sharp), show_border, layout_mode (card or full), card and inner width and height values with unit keys, and the split screen layout values. See Front-end JavaScript API for the CSS custom properties they produce.

questions.settings and questions.logic

{ "required": true, "randomize_answers": false, "image_height": 300,
  "timer": { "enabled": true, "seconds": 30 } }

required here defaults to true unless it is explicitly false. The logic column holds branching rules the player evaluates client side. The first matching rule wins and go_to_question_id: null ends the quiz:

{ "rules": [
    { "if": { "answer_id": 5 }, "then": { "go_to_question_id": 12 } },
    { "default": { "go_to_question_id": null } }
] }

submissions.answers

[
  { "question_id": 11, "answer_ids": [31], "text_value": null,
    "time_spent_ms": 0, "elapsed_ms": 4200, "answer_order": [32, 31, 33] }
]

elapsed_ms and answer_order are present only when the client sent them. Rating and short text answers live in text_value with an empty answer_ids.

submissions.result_breakdown

Quiz type	Shape
personality	{ "tally": { "<result_id>": votes }, "total_answers": n }
trivia	{ "score": n, "correct_count": n, "total_questions": n }
survey, poll	{ "answered_questions": n, "total_questions": n }

submissions.utm

An object with any of utm_source, utm_medium, utm_campaign, utm_term, utm_content.

IP hashing and personal data

• The raw IP address is never written to the database.
• submissions.ip_hash and the rate limit key are substr( sha256( REMOTE_ADDR . '|' . wp_salt() ), 0, 16 ). Only REMOTE_ADDR is used, optionally replaced through the quizably_rate_limit_ip filter before hashing.
• The column is CHAR(64) but 16 hex characters are stored. The hash is salted per site and is not used to block repeat submissions.
• Personal data in the tables: leads (email, name, phone, extra fields, consent), submissions (hashed IP, User-Agent, UTM and the visitor’s typed answers). See Data and privacy.

Options

Option	Purpose
quizably_db_version	Installed schema version. Compared on admin boot to decide whether to run dbDelta() again.
quizably_version	Plugin version written at activation.
quizably_doi_secret	Random 32 character secret created on first double opt-in use. Signs confirmation tokens.
quizably_integration_{key}	Connector credentials saved through PUT /settings/integrations. Not used by the built-in webhook.

The quizably_manage_quizzes capability is stored in the Administrator role (the {prefix}user_roles option) when the plugin is activated.

Settings table keys

The settings REST route accepts exactly these keys, and each is stored as a row in {prefix}quizably_settings. All of them are read by the plugin (see Settings).

default_template, default_optin_placement, branding_primary_color, branding_accent_color, defaults_font_family, defaults_button_radius, gdpr_default_consent_text, notifications_email_on_submission, notifications_email_on_lead, notifications_recipient, email_from_name, email_from_address, email_reply_to_lead, email_template_subject, email_template_body, default_webhook_url.

Boolean keys (notifications_email_on_submission, notifications_email_on_lead, email_reply_to_lead) are stored as the strings "1" and "0". Version 1.2.0 removed the keys for the logo URL, animation speed, lazy loading and the Editor and Author role toggles. If a site saved values for them earlier, those rows stay in the table and nothing reads them.

Transients and scheduled events

Name	Purpose	Lifetime
quizably_rl_m_{hash}_{minute}	Per-minute request counter, where {minute} is floor( time() / 60 ).	65 seconds
quizably_rl_h_{hash}_{hour}	Per-hour request counter, where {hour} is floor( time() / 3600 ).	3700 seconds
quizably_poll_{quiz_id}	Cached vote counts for a poll, shown to visitors after they vote. Cleared whenever a submission completes.	20 seconds
WP-Cron quizably_integration_deliver	Single event that sends a webhook in the background, scheduled to run immediately.	Until it runs
WP-Cron quizably_integration_retry	Single events scheduled to retry failed webhook deliveries.	60, 120 and 240 seconds after each failure

Queued cron events keep the full webhook payload and the webhook settings (including any signing secret) in the WordPress cron option until they run. Earlier versions also kept a quizably_doi_used_* transient for confirmation tokens. Version 1.2.0 no longer writes it.

With a persistent object cache, transients live in the cache and not in wp_options.

What uninstall deletes

By default, deleting the plugin from the Plugins screen removes nothing. Every table, setting and option stays in the database, so deleting and re-uploading the plugin never loses your work. Deactivation also keeps everything.

To erase everything on delete, add this line to wp-config.php before you delete the plugin:

define( 'QUIZABLY_REMOVE_ALL_DATA', true );

With that constant set, uninstall.php drops all eight tables (DROP TABLE IF EXISTS) and deletes every option whose name starts with quizably_. That includes quizably_db_version, quizably_version, quizably_doi_secret and the quizably_integration_{key} options.

Not removed even then: leftover transients (they expire on their own), pending cron events and the quizably_manage_quizzes capability on the Administrator role.

Related

• REST API reference
• Hooks and filters
• Capabilities, permissions and security
• Data and privacy
• Settings