Data model

CaptainCore does not use custom post types. It creates its own tables and treats them as the fleet’s state. Models in app/ are thin classes over those tables sharing one query layer, autoloaded through Composer PSR-4.

All tables are created and migrated in CaptainCore\DB::upgrade() with dbDelta, using $wpdb->base_prefix, so on multisite there is one shared set of tables rather than one per site.

How models map to tables

DB::_table() derives the table name from the class name. It splits the fully qualified name on the namespace separator, lowercases the namespace, converts the class name from CamelCase to snake_case, and joins the two with an underscore, then prefixes it with $wpdb->prefix.

So CaptainCore\Sites becomes wp_captaincore_sites, and CaptainCore\SiteAuditFindings becomes wp_captaincore_site_audit_findings. No mapping table, no configuration.

Each model declares a static $primary_key, which is what get(), update() and delete() key on:

Not everything in app/ is a table. Site, Account, Domain, Provider and User are single-record wrappers with business logic. Quicksave, Run and the Remote\* classes talk to the CLI or third-party APIs and store nothing locally.

Sites and environments

captaincore_sites

One row per managed site.

site_id, account_id (who is billed), customer_id (who owns it), name (the domain), site (short slug), provider_id, provider_site_id, provider, token, status, details (longtext JSON), screenshot, created_at, updated_at.

details is where most of the interesting state lives: cached core version, home URL, subsite count, removal flag and site labels.

captaincore_environments

One row per environment on a site, typically Production and Staging.

Connection: environment (the name), address, username, password, protocol, port, home_directory, token.

Database: database_name, database_username, database_password.

Offload storage: offload_enabled, offload_provider, offload_access_key, offload_secret_key, offload_bucket, offload_path.

Inventory: themes, plugins, users (all longtext JSON), core, core_verify_checksums (default 1), subsite_count, home_url, php_memory, storage, visits, fathom.

Behaviour: capture_pages, screenshot, monitor_enabled, updates_enabled, updates_exclude_themes, updates_exclude_plugins.

details (longtext JSON) carries everything the sync collects that does not have its own column: core_checksum_details, plugin_checksum_details, mu_plugins, core_file_hashes, loose_file_hashes, security_log, error_logs, php_version, db_size, default_role, registration, restic_cache, screenshot_base, audit_summary, and the one-shot alert flags (checksum_alerted, default_role_alerted, registration_role_alerted).

Accounts and permissions

captaincore_accounts

account_id, billing_user_id, name, defaults (JSON: default email, timezone, recipes, users applied to new sites), plan (JSON), metrics, status.

captaincore_account_portals

White-labelled customer portals. account_portal_id, account_id, domain, configurations (JSON: name, colors, logo, DNS copy). Matched against the CAPTAINCORE_CUSTOM_DOMAIN constant at runtime.

The three join tables

captaincore_account_user carries account_id, user_id and level (full-billing, full, sites-only, domains-only). captaincore_account_site and captaincore_account_domain are plain joins. Together these decide what every non-administrator can see.

captaincore_invites

Pending account invitations. account_id, email, token, level, and an accepted_at timestamp.

captaincore_keys

SSH public keys. user_id, title, fingerprint (varchar 47), main boolean for the primary key.

Domains

captaincore_domains

domain_id, remote_id (the Constellix zone id), provider_id and provider_domain_id (the registrar), status, price, name, details (JSON: nameservers, WHOIS contacts, lock and privacy state, Mailgun and forwarding configuration).

History and history-like tables

captaincore_captures

Visual capture history. site_id, environment_id, git_commit, pages (JSON array of {name, image}), indexed on (site_id, environment_id).

captaincore_snapshots

Downloadable full-site snapshots. user_id, site_id, environment_id, snapshot_name, storage, email, notes, expires_at and a token (varchar 32) used in the public download URL.

captaincore_session_snapshots

Append-only per-environment user and privilege telemetry, one row per environment per sync.

Summary columns for querying: collected_at, total_users, session_token_rows, admin_users, admin_sessions, admin_unique_ips, admin_capable_users, injected_caps_count, super_admin_count.

Detection columns: anomaly_count, max_severity (default none), anomalies (JSON), alerted_at (null until the hourly digest picks it up).

payload holds the full collector JSON. Indexed on site_id, environment_id, created_at, injected_caps_count, max_severity and alerted_at.

SessionSnapshots::latest_for() deliberately orders by the auto-increment primary key rather than created_at, because two snapshots can share a timestamp and the delta baseline must be the genuinely previous row.

Process logs

captaincore_processes

Handbook processes: name, description, time_estimate, repeat_interval, repeat_quantity, roles.

captaincore_process_logs

One recorded run of a process, or a freeform note. process_id, user_id, description (Markdown), public boolean, status, and completed_at.

captaincore_process_log_site

Which sites a log entry is attached to, indexed on site_id. This is what puts a note on a site’s timeline.

captaincore_process_log_file

File-level diffs attached to a log entry. file_path (varchar 512), change_type, hunks (longtext), lines_added, lines_removed.

Automation

captaincore_recipes

Saved scripts. user_id, title, content, public boolean. public is administrator only to set.

captaincore_scripts

Scheduled code against an environment. environment_id, code, details, status.

captaincore_job_tokens

Maps a CLI job token to the user who created it. token (varchar 64, unique), task_id, user_id, site_id, command, indexed on user_id. This is what makes GET /my-jobs/{token} refuse someone else’s token.

captaincore_scheduled_reports

site_ids (longtext), account_id, interval, recipient, user_id, next_run, last_run. Note that interval is a reserved word and is backticked in the schema.

Providers

captaincore_providers

One row per connected hosting or registrar account. user_id (0 means the shared house row), name, provider (the adapter slug), status, details, credentials (JSON array of {name, value}), configurations.

captaincore_provider_actions

The provisioning chain. provider_id, provider_key, user_id, status, action (JSON describing the step). Long provider operations, creating a site, cloning, deploying, are stepped through here rather than held open in one request.

Security operations

captaincore_security_threat_tracking

slug, version, type, status (default new, moving through investigating, reported, resolved), notes, resolved_at. Unique on (slug, version, type), indexed on status.

captaincore_security_patch

Rebuilt packages that fix a vulnerable build. slug, version, type (default plugin), title, patched_version, download_url (varchar 500), description, severity. Unique on (slug, version, type).

captaincore_site_audits

Stored reports. site_id, environment_id, status (default in_progress), filesystem_status, wp_version, php_version, issues_count, plugins_count, scan_checks, site_config, admin_accounts, timeline_events, user_id, notes, completed_at, report_path, report_type (default security_audit), dashboard_metrics, summary, sections, report_title, section_order. Indexed on site_id, environment_id, status and created_at.

captaincore_site_audit_findings

site_audit_id, severity, status (default open), title, description, evidence, recommendation, resolution (varchar 512), resolved_at.

captaincore_web_risk_logs

One row per Google Web Risk fleet run. total_sites, threats_found, errors_count, details.

captaincore_activity_logs

The audit trail. Covered in detail in Activity log.

Fleet core updates

captaincore_core_update_runs

The parent row for one probe or apply pass: target, flags, version_requested, version_resolved, parallel, duration_seconds, total, updated_count, skipped_count, failed_count, probed_count, status (default completed).

captaincore_core_update_results

One row per site in that run: core_update_run_id, site, site_id, environment_id, home_url (varchar 500), result, action, stage, core_before, core_after, reason, excerpt, exit_code, error_class, status (default open, for triage) and notes. Indexed on run, result, stage, error class, status and site, so failures can be grouped by shape.

Schema version and migrations

The schema level this build expects is a constant:

class DB {
    /** Schema level this build expects. Bump when a migration is added. */
    const REQUIRED_VERSION = 52;

The current level is stored in the captaincore_db_version site option. DB::upgrade() returns early if the stored version is already at or above REQUIRED_VERSION, unless called with $force = true.

Migrations run automatically. captaincore_maybe_upgrade_db() is hooked to both init and admin_init, because the four ways CaptainCore is used, the /account front-end rewrite, the CLI ingest, the fleet cron shelling out to wp captaincore, and wp-admin itself, do not all reach admin_init. Without the init hook a schema release could sit un-migrated until somebody happened to open wp-admin.

The function guards carefully, in this order:

1. WP-CLI is allowed through unconditionally: it has no user context and is already root-equivalent.
2. Everything else bails on AJAX, cron, or a logged-out request, then requires manage_network on multisite or manage_options otherwise. Both init and admin_init run before the authentication check in admin-ajax.php, admin-post.php and the REST API, so without this an unauthenticated request could trigger the migration.
3. If the stored version already meets the requirement, it returns.
4. It takes a lock with add_site_option( 'captaincore_db_upgrade_lock', time() ). add_site_option only succeeds when the option does not exist, so exactly one request takes it. A lock older than 15 minutes is reclaimed, so a fatal error mid-migration cannot wedge upgrades permanently.
5. The upgrade runs inside a try/finally with output buffering, so a dbDelta warning cannot corrupt the page it fired on and a failure cannot white-screen the dashboard. The lock is always released.
6. Success is determined by re-reading captaincore_db_version, not by parsing the return string. A shortfall is written to the error log.

DB::upgrade() returns a string rather than echoing, because it runs from the activation hook where stray output makes WordPress report unexpected output.

To run a migration by hand:

wp eval 'echo CaptainCore\DB::upgrade();'

One data migration ships alongside the schema. At version 39, account owners were backfilled into captaincore_account_user.level as full-billing from each account’s plan.billing_user_id, and every remaining blank level was set to full.