Documentation · Technical Documentation

Database Schema

This document describes the custom database tables created by the ADP Car Market Hub plugin. All tables are managed with WordPress's dbDelta() helper and are created on plugin activation. Their schema versions are tracked in wp_options so that dbDelta can apply column changes safely on upgrade.

When to use this document

Read this document if you need to:

Plan backups, retention or compliance work for plugin data.
Build custom reports against the plugin's tables.
Diagnose failed schema upgrades or missing columns.
Understand which tables are dropped on uninstall and which are not.

For the logical entity model that uses these tables, see Data Model.

Overview

The plugin creates five custom tables:

Table suffix	Purpose	Schema-version option
`as24_vehicles`	Vehicle field data, replacing the bulk of `wp_postmeta` usage.	`as24ci_vehicles_db_version`
`as24ci_analytics`	Analytics events for vehicle pages and global filter actions.	`as24ci_analytics_db_version`
`as24ci_search_agents`	Visitor search-alert subscriptions (Smart Stock Alerts).	`as24ci_search_agent_db_version`
`as24ci_content_studio_jobs`	Content Studio generation jobs (one row per job).	`as24ci_content_studio_db_version`
`as24ci_content_studio_assets`	Content Studio generated assets, linked to a job by `job_id`.	`as24ci_content_studio_db_version`

Real table names use the WordPress prefix {$wpdb->prefix} (typically wp_). The analytics table's schema version is stored in as24ci_analytics_db_version (constant AS24CI\Analytics::DB_VERSION_KEY). The two Content Studio tables share a single schema-version option (as24ci_content_studio_db_version, current value 1.3.0) defined on AS24CI\Content_Studio_Options::DB_VERSION and managed by AS24CI\Content_Studio_Repository::maybe_create_tables().

The plugin also relies on the WordPress core tables wp_posts, wp_postmeta, wp_term_relationships, wp_term_taxonomy and wp_terms for the as24ci_car and as24ci_lead post types and the 15 vehicle taxonomies. Those tables follow the standard WordPress schema and are not redefined by the plugin.

All five custom tables are dropped automatically on uninstall (see the Uninstall behaviour section below), regardless of the as24ci_delete_data_on_uninstall setting.

Table: `{$wpdb->prefix}as24_vehicles`

Owner: AS24CI\Vehicle_Repository. Created via Vehicle_Repository::maybe_create_table() on activation and re-checked on subsequent runs. The current schema version constant is 1.1, stored in the as24ci_vehicles_db_version option.

The table holds one row per vehicle, linked to its as24ci_car post by the post_id column. The as24_id column holds the AutoScout24 listing identifier. Both columns have unique indexes.

Columns

Column	Type	Notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment.
`post_id`	`bigint(20) unsigned`	WordPress post ID for the linked `as24ci_car`. Unique.
`as24_id`	`varchar(64)`	AutoScout24 listing identifier. Unique. Defaults to empty string.
`seller_id`	`varchar(64)`	AutoScout24 seller identifier.
`make`	`varchar(100)`
`model`	`varchar(100)`
`version_full_name`	`varchar(255)`	Full trim/version label as supplied by the API.
`condition_type`	`varchar(50)`	E.g. `new`, `used`, `demo` (values mirror the source API).
`body_type`	`varchar(50)`
`fuel_type`	`varchar(50)`
`transmission_type`	`varchar(50)`
`drive_type`	`varchar(50)`
`color_exterior`	`varchar(100)`
`color_interior`	`varchar(100)`
`doors`	`tinyint(3) unsigned`	Defaults to `0`.
`seats`	`tinyint(3) unsigned`	Defaults to `0`.
`mileage`	`int(10) unsigned`	Defaults to `0`.
`cubic_capacity`	`int(10) unsigned`	Engine displacement.
`range_km`	`int(10) unsigned`	Electric range in kilometres.
`first_registration`	`date`	Nullable.
`first_reg_year`	`smallint(5) unsigned`	Year component of `first_registration`. Defaults to `0`.
`power_kw`	`smallint(5) unsigned`
`power_hp`	`smallint(5) unsigned`
`co2_emission`	`smallint(5) unsigned`	g/km.
`price`	`decimal(10,2)`	Sale price.
`list_price`	`decimal(10,2)`	List/MSRP price (if supplied).
`currency`	`char(3)`	ISO 4217 code; defaults to `EUR`.
`emission_standard`	`varchar(20)`	E.g. Euro 6.
`energy_label`	`char(5)`	A-G energy label.
`battery_capacity`	`decimal(6,2)`	kWh.
`vin`	`varchar(20)`	Vehicle identification number.
`status`	`varchar(30)`	Listing status as supplied by the API.
`is_live`	`tinyint(1)`	`1` if the listing is currently live, `0` otherwise.
`had_accident`	`tinyint(1)`
`equipment_codes`	`longtext`	JSON array of equipment codes. Nullable.
`equipment_standard`	`longtext`	JSON list of standard equipment items. Nullable.
`equipment_optional`	`longtext`	JSON list of optional equipment items. Nullable.
`images`	`longtext`	JSON list of image URLs as supplied by the API.
`raw_data`	`longtext`	Full source payload retained for diagnostics.
`manual_overrides`	`longtext`	JSON object of administrator-edited fields that override imported values.
`content_hash`	`varchar(64)`	Hash used to skip re-imports of unchanged listings.
`imported_at`	`datetime`	Defaults to `CURRENT_TIMESTAMP`.
`updated_at`	`datetime`	Defaults to `CURRENT_TIMESTAMP`. See the implementation note below.

Implementation note: updated_at is created with only DEFAULT CURRENT_TIMESTAMP inside the dbDelta SQL because adding ON UPDATE CURRENT_TIMESTAMP directly causes dbDelta to re-issue an ALTER on every plugin load. The ON UPDATE semantics are applied through a one-shot ALTER after dbDelta runs.

Indexes

Index	Type	Columns	Purpose
`PRIMARY`	Primary	`id`	Surrogate primary key.
`uk_as24_id`	Unique	`as24_id`	One row per AutoScout24 listing.
`uk_post_id`	Unique	`post_id`	One row per WordPress vehicle post.
`idx_make_model`	Index	`make`, `model`	Drives make/model archive filters.
`idx_price`	Index	`price`	Price sort/range filtering.
`idx_mileage`	Index	`mileage`	Mileage sort/range filtering.
`idx_first_reg_year`	Index	`first_reg_year`	Year sort/range filtering.
`idx_fuel_condition`	Index	`fuel_type`, `condition_type`	Common combined filter.
`idx_status_live`	Index	`status`, `is_live`	Live-listing queries.
`idx_content_hash`	Index	`content_hash`	Lookups during change detection.

Safety net for `manual_overrides`

After dbDelta runs, the repository performs a SHOW COLUMNS ... LIKE 'manual_overrides' check and, if the column is missing, issues an explicit ALTER TABLE ... ADD COLUMN manual_overrides longtext DEFAULT NULL AFTER raw_data. This compensates for occasional column-detection issues in some MySQL/MariaDB combinations and ensures the manual override layer always has a place to store data.

Table: `{$wpdb->prefix}as24ci_analytics`

Owner: AS24CI\Analytics. Created via Analytics::maybe_create_table() during activation and re-checked on every admin page load (the method is also wired to admin_init so existing installations pick up new columns without a manual re-activation).

Columns

Column	Type	Notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment.
`post_id`	`bigint(20) unsigned`	Vehicle post ID. `0` for global events such as filter searches.
`event_type`	`varchar(20)`	Event identifier; defaults to `view`. Validated against `ALLOWED_EVENTS`.
`extra_data`	`longtext`	Optional JSON-encoded payload. Nullable.
`created_at`	`datetime`	Defaults to `CURRENT_TIMESTAMP`.

Indexes

Index	Type	Columns
`PRIMARY`	Primary	`id`
`post_event_date`	Index	`post_id`, `event_type`, `created_at`
`event_type`	Index	`event_type`, `created_at`
`created_at`	Index	`created_at`

Retention

Inserts only happen when Options::ANALYTICS_ENABLED is 1. A daily retention job runs through the as24ci_daily_cleanup cron hook (scheduled on activation, removed on deactivation). The retention window is configured through the analytics admin tab.

Table: `{$wpdb->prefix}as24ci_search_agents`

Owner: AS24CI\Search_Agent. Created via Search_Agent::maybe_create_table() on init (priority 1) so it is available for both admin and frontend AJAX handlers. The current schema version is 1.1, stored in the as24ci_search_agent_db_version option. Creation is gated by the Options::FEATURE_SEARCH_AGENT toggle, but the schema is registered unconditionally when the feature class is loaded.

Columns

Column	Type	Notes
`id`	`BIGINT UNSIGNED`	Primary key, auto-increment.
`name`	`VARCHAR(255)`	Visitor display name; defaults to empty string.
`email`	`VARCHAR(255)`	Visitor email address.
`criteria`	`TEXT`	Saved search criteria (serialised representation).
`token`	`VARCHAR(64)`	Token for confirmation and unsubscribe links.
`frequency`	`VARCHAR(20)`	Notification frequency; defaults to `daily`.
`status`	`VARCHAR(20)`	One of `pending`, `active`, `inactive`, `paused`. Defaults to `pending`.
`created_at`	`DATETIME`	Defaults to `CURRENT_TIMESTAMP`.
`confirmed_at`	`DATETIME`	Nullable; set when the visitor confirms via Double-Opt-In.

Indexes

Index	Type	Columns
`PRIMARY`	Primary	`id`
`idx_status`	Index	`status`
`idx_token`	Index	`token`

Privacy

Because the table contains personal data (name, email, search preferences), it is dropped during uninstall regardless of the as24ci_delete_data_on_uninstall setting. Site operators are responsible for additional retention or export tooling required by local privacy regulations.

Table: `{$wpdb->prefix}as24ci_content_studio_jobs`

Owner: AS24CI\Content_Studio_Repository. Created via Content_Studio_Repository::maybe_create_tables() (registered as its own activation hook in the main plugin file and also called lazily by most repository methods). Holds one row per Content Studio generation job. Status values are draft, queued, processing, ready and failed.

Columns

Column	Type	Notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment.
`vehicle_post_id`	`bigint(20) unsigned`	Source vehicle post. Defaults to `0`.
`vehicle_snapshot_hash`	`varchar(64)`	Hash of the vehicle snapshot used for the job.
`content_type`	`varchar(40)`	Requested content type.
`platform_preset`	`varchar(40)`	Target platform preset.
`output_format`	`varchar(20)`	Output format.
`preset_key`	`varchar(80)`	Preset catalogue key.
`status`	`varchar(20)`	Defaults to `draft`.
`prompt_payload`	`longtext`	JSON prompt payload. Nullable.
`provider`	`varchar(40)`	Defaults to `google`.
`model`	`varchar(80)`	Provider model.
`language`	`varchar(20)`	Output language.
`tone`	`varchar(40)`	Tone of voice.
`attempts`	`smallint(5) unsigned`	Processing attempts. Defaults to `0`.
`last_error`	`text`	Last error message. Nullable.
`created_by`	`bigint(20) unsigned`	WordPress user ID. Defaults to `0`.
`created_at`	`datetime`	Defaults to `CURRENT_TIMESTAMP`.
`updated_at`	`datetime`	Defaults to `CURRENT_TIMESTAMP`.
`started_at`	`datetime`	Nullable.
`completed_at`	`datetime`	Nullable.

Indexes: PRIMARY (id), vehicle_post_id, status_created (status, created_at), platform_preset, content_type.

Table: `{$wpdb->prefix}as24ci_content_studio_assets`

Owner: AS24CI\Content_Studio_Repository. Holds the generated assets (text, images, copy packages) belonging to a job, linked by job_id.

Columns

Column	Type	Notes
`id`	`bigint(20) unsigned`	Primary key, auto-increment.
`job_id`	`bigint(20) unsigned`	Parent job. Defaults to `0`.
`vehicle_post_id`	`bigint(20) unsigned`	Source vehicle post. Defaults to `0`.
`asset_type`	`varchar(40)`	Asset type.
`file_path`	`text`	Local file path. Nullable.
`file_url`	`text`	Public URL. Nullable.
`mime_type`	`varchar(120)`	MIME type.
`file_size`	`bigint(20) unsigned`	Bytes. Defaults to `0`.
`width`	`int(10) unsigned`	Image width. Defaults to `0`.
`height`	`int(10) unsigned`	Image height. Defaults to `0`.
`duration_seconds`	`decimal(8,2)`	Media duration. Defaults to `0.00`.
`caption`	`longtext`	Nullable.
`hashtags`	`longtext`	Nullable.
`alt_text`	`text`	Nullable.
`thumbnail_path`	`text`	Nullable.
`payload_json`	`longtext`	Structured payload (e.g. copy packages). Nullable.
`scheduled_at`	`datetime`	Nullable.
`schedule_status`	`varchar(32)`	Defaults to `unscheduled`.
`created_at`	`datetime`	Defaults to `CURRENT_TIMESTAMP`.

Indexes: PRIMARY (id), job_id, vehicle_post_id, asset_type, created_at, schedule_status, scheduled_at.

WordPress core tables used by the plugin

The plugin does not modify the schema of WordPress core tables. It relies on them as follows:

wp_posts — stores as24ci_car (vehicles) and as24ci_lead (leads) posts.
wp_postmeta — stores backwards-compatibility postmeta for vehicle posts (_as24ci_listing_id, _as24ci_content_hash, _as24ci_images_hash, _as24ci_image_ids, _as24ci_manual_image_ids) and the lead status (_as24ci_lead_status).
wp_terms, wp_term_taxonomy, wp_term_relationships — store the 15 vehicle taxonomies registered by AS24CI\Taxonomies.
wp_options — stores all plugin settings (keys defined as constants on AS24CI\Options) plus the schema-version markers and the IDs of the activation-created pages.
wp_users and wp_usermeta — used indirectly via WordPress capabilities; no plugin-specific writes.

The custom roles and capabilities created by the plugin (as24ci_editor, the as24ci_car/as24ci_cars capability set and manage_as24_imports) are stored through the standard WordPress roles option (wp_user_roles).

Uninstall behaviour

The uninstall.php script always issues DROP TABLE IF EXISTS for the five custom tables and removes the matching schema-version options:

{$wpdb->prefix}as24_vehicles (and as24ci_vehicles_db_version)
{$wpdb->prefix}as24ci_analytics
{$wpdb->prefix}as24ci_search_agents (and as24ci_search_agent_db_version)
{$wpdb->prefix}as24ci_content_studio_assets
{$wpdb->prefix}as24ci_content_studio_jobs (and as24ci_content_studio_db_version)

On multisite installations the script switches to each site and repeats the cleanup. Posts, attachments and activation-created pages are only removed when the administrator has set as24ci_delete_data_on_uninstall to 1.

Operational notes

Schema upgrades are idempotent. Each owner class checks its schema-version option and quick-exits when the table already matches the expected version. The vehicles repository additionally checks that the table physically exists before quick-exiting.
Direct wpdb queries against these tables exist in the repository, analytics, search-agent and uninstall code paths. They are guarded with PHPCS ignore comments where appropriate. Custom code that bypasses the repository should treat the schema as part of the plugin's internal contract and be prepared for column changes between releases.
The raw_data, equipment_* and images columns hold serialised data. Treat them as opaque blobs and access them through the repository helpers when possible.
The manual_overrides column is the one place that mixes administrator-controlled and importer-controlled state. Avoid writing to it directly without going through the override layer.

Troubleshooting

maybe_create_table() did not create the vehicles table. Confirm that dbDelta() reported no errors. The repository writes errors to error_log() because it runs before the plugin Logger is available. Re-running activation (deactivate, activate) re-runs maybe_create_table().
A new column is missing after a plugin upgrade. Check the schema-version option for the affected table. If it equals the current value but the column is missing, delete the option to force dbDelta to re-run on the next admin page load. The vehicles table has an explicit manual_overrides safety net for this case.
The analytics table grows quickly. Reduce the retention window or disable analytics via Options::ANALYTICS_ENABLED. The daily cleanup job is required to keep the table bounded.
Search-agent rows persist after uninstall. Verify that WordPress executed uninstall.php. If only deactivate() ran (for example because the plugin was deactivated but not deleted), the table is intentionally retained until uninstall.

When to use this document

Overview

Table: {$wpdb->prefix}as24_vehicles

Columns

Indexes

Safety net for manual_overrides

Table: {$wpdb->prefix}as24ci_analytics

Columns

Indexes

Retention

Table: {$wpdb->prefix}as24ci_search_agents

Columns

Indexes

Privacy

Table: {$wpdb->prefix}as24ci_content_studio_jobs

Columns

Table: {$wpdb->prefix}as24ci_content_studio_assets

Columns

WordPress core tables used by the plugin

Uninstall behaviour

Operational notes

Troubleshooting

Related documents

Table: `{$wpdb->prefix}as24_vehicles`

Safety net for `manual_overrides`

Table: `{$wpdb->prefix}as24ci_analytics`

Table: `{$wpdb->prefix}as24ci_search_agents`

Table: `{$wpdb->prefix}as24ci_content_studio_jobs`

Table: `{$wpdb->prefix}as24ci_content_studio_assets`