Database schema (version 1)

Prefix: {wpdb->prefix} + ad_form_. Example: wp_ad_form_forms.

Charset: site charset/collate via $wpdb->get_charset_collate() (utf8mb4).

Schema version is stored in option ad_form_db_version and table {prefix}ad_form_migrations.

Tables

{prefix}ad_form_forms

Column	Type	Notes
id	BIGINT UNSIGNED PK
uuid	CHAR(36) UNIQUE	Stable public id
title	VARCHAR(255)
slug	VARCHAR(191) UNIQUE	Shortcode / REST
status	VARCHAR(20)	draft, published, disabled, archived
definition	LONGTEXT	JSON schema of the form
version	INT UNSIGNED	Optimistic form version
parent_id	BIGINT UNSIGNED NULL	Revision parent
created_by / updated_by	BIGINT UNSIGNED	WP user ids
created_at / updated_at	DATETIME	UTC
published_at / starts_at / ends_at	DATETIME NULL	Scheduling
submission_count	BIGINT UNSIGNED	Denormalized counter

Indexes: status_updated, created_by, parent_id.

{prefix}ad_form_form_meta

EAV for rarely queried form options. Hot data stays on forms.

Indexes: (form_id, meta_key), meta_key.

{prefix}ad_form_submissions

Column	Type	Notes
id	BIGINT UNSIGNED PK
uuid	CHAR(36) UNIQUE
form_id	BIGINT UNSIGNED	Indexed with status + created_at
form_version	INT UNSIGNED	Definition version at submit time
status	VARCHAR(20)	unread, read, spam, trash
is_starred	TINYINT(1)
user_id	BIGINT UNSIGNED	0 = guest
ip_hash	CHAR(64) NULL	Hash/anonymized; raw IP is a settings choice
user_agent	VARCHAR(255) NULL	Off by default
source	VARCHAR(50)	shortcode, elementor, gutenberg, api
payment_status	VARCHAR(20) NULL
payload	LONGTEXT	Full JSON snapshot
created_at / updated_at	DATETIME
deleted_at	DATETIME NULL	Soft delete / trash

Indexes: form_status_created, form_created, user_id, ip_hash, status_created, is_starred.

{prefix}ad_form_submission_meta

Normalized, queryable field values. form_id is denormalized to avoid joins on large lists.

Column	Type	Notes
value_text	VARCHAR(191)	Indexed equality / prefix search
value_num	DECIMAL(20,6)	Numeric filters and calculations
value_long	LONGTEXT	Full value when longer than 191 chars

Indexes: submission_id, (form_id, field_key, value_text), (form_id, field_key, value_num), (field_key, value_text).

{prefix}ad_form_logs

Action, mail, webhook, REST and payment logs. Debug rows are not written unless enabled.

Indexes include created_at for retention jobs.

{prefix}ad_form_payments

Amounts in the smallest currency unit (integer). Client-supplied totals are never trusted; Phase 16 recalculates server-side.

{prefix}ad_form_migrations

Applied schema versions. Unique on version.

Scale notes

• List screens filter on (form_id, status, created_at) — covered by one composite index.
• Search by email uses submission_meta.form_field_value, not LIKE on payload.
• Pagination is mandatory; never load unbounded entry lists.
• JSON definition / payload stay off hot WHERE clauses.

Foreign keys

None at the engine level. Repositories delete children when a parent is removed.