Data model

A table-by-table map of the database. If you’re trying to figure out where some piece of state lives, this is the page.

Inventory tables

These are the natural-key tables — everything else hangs off them.

servers

One row per managed host. Natural key: spinupwp_id. Notable columns:

• Identity: name, hostname, spinupwp_id.
• Cloud provider: provider (digitalocean|hetzner|…), provider_id, size_slug, vcpus, memory_mb, disk_gb. Populated daily by clockwork:import-spinupwp from SpinupWP’s provider_name field, cross-referenced against the matching cloud’s API.
• SSH: ssh_user, ssh_port, ssh_private_key (encrypted), ssh_password (encrypted), ssh_password_updated_at (tracked for sudo password 90-day hygiene).
• Monitoring state: status (green|yellow|red|unknown), last_polled_at, last_alert_at, last_ssh_ok_at, provider_missing_since (nullable timestamp — set the first time clockwork:poll-servers finds CloudProvider::isDeletedAtProvider() true, a precise “genuinely gone from the cloud provider’s own inventory” signal distinct from a generic polling error; drives a red “Remove from Clockwork Control” banner on the server show page and is cleared automatically the next time a poll succeeds).
• Provisioning: clockwork_jail_provisioned_at, last_provision_log.
• Operations: is_ignored, ignore_reason, auto_ban_llar, auto_ban_wordfence, last_llar_pull_at, last_wordfence_pull_at, upgrade_required, reboot_required, update_status, scheduled_reboot_at.

sites

One row per WordPress (or non-WP) site. Natural key: domain (unique). Notable columns:

• Linkage: server_id (nullable — null for Pressable sites, which have no server concept), spinupwp_id, hosting_provider (spinupwp | pressable | gridpane | wpengine | kinsta | cloudways), pressable_site_id (nullable, unique — mirrors spinupwp_id), site_user, wp_path, consolidated_into_site_id (nullable FK to a canonical sites row, used to consolidate secondary www/non-www domain aliases). hosting_provider has no default as of a 2026-09-08 migration — every creation path (ImportSpinupWp, ImportPressable, ImportGridPane, SiteFactory, …) now sets it explicitly, so a future path that forgets it fails loudly (NOT NULL violation) instead of silently mislabeling the site as spinupwp and falling into the orphan-sites query.
• Database: db_host, db_port, db_name, db_user, db_password (encrypted), table_prefix.
• Identity: is_wordpress, wordfence_enabled, llar_enabled, wp_plugins_detected_at, wp_core_update, wp_theme_updates, wp_plugin_updates. The wp_core_update/wp_theme_updates/wp_plugin_updates booleans are SpinupWP-inventory-only — always false for Pressable sites even when real pending updates exist (visible instead via companion_snapshot). UI hides those specific pills for Pressable rather than showing a false “up to date.”
• Cert: cert_source, cert_expires_at, cert_renews_at, cert_state, cert_state_changed_at, cert_notes.
• Domain expiration: domain_expires_at, domain_registrar, domain_rdap_status, domain_rdap_checked_at, domain_rdap_error (all nullable, populated by the RDAP domain-expiration watchdog), plus domain_expiration_state (default none) + domain_expiration_state_changed_at tracking the alerting lifecycle for an about-to-expire domain.
• Cloudflare: cloudflare_state, cloudflare_checked_at, resolved_a_record, resolved_ns_record.
• Lifecycle: is_inactive (default false) + inactive_reason (nullable). Distinct from archived_at: an archived site is hidden from every listing entirely, an inactive site stays fully visible (Sites list, server page, search) but is excluded from the Issues page / nav badge counting and from every site-scoped Mattermost/Slack alert — same “still visible, not monitored” shape as Server.is_ignored, generalized across every issue category instead of one narrow toggle. Toggled via ActionLog::TYPE_SITE_DEACTIVATED/TYPE_SITE_REACTIVATED. Archiving a SpinupWP or Pressable site also writes site_ingest_exclusions (see below) so clockwork:import-spinupwp / clockwork:import-pressable will not refill host ids onto the hidden row.
• Care plan: care_plan_enabled, care_plan_override (NULL = Bill.com may write; bool = manual override sync respects).
• SEO indexability: seo_indexable (default true), seo_blocked_reason + seo_blocked_snippet (the single current top-priority reason/evidence), seo_checked_at, seo_monitoring_enabled (default true), seo_state_changed_at, plus per-vector last-known snapshots seo_meta_snippet / seo_header_snippet / seo_robots_snippet — kept independently because the meta-tag, X-Robots-Tag header, and robots.txt vectors are checked by different code paths at different cadences, so fixing the higher-priority one doesn’t silently “forget” a lower-priority one is still blocking. Suppressible per-site via ignored_issues (see below).
• Site Command Center: notes (free-text, operator-editable), dashboard_layout (nullable JSON array of widget keys ordering the per-site Overview dashboard’s draggable cards — Site::resolvedDashboardLayout() falls back to Site::DEFAULT_DASHBOARD_LAYOUT and appends any new default widgets missing from an older saved layout), screenshot_path + screenshot_captured_at (locally cached fleet-grid-view thumbnail, captured async via CaptureSiteScreenshotJob on site creation and by the clockwork:capture-site-screenshots command; Site::screenshotUrl() falls back to Automattic’s free mShots renderer when no local capture exists yet).
• Backup relay: backup_relay_enabled (boolean, default false) marks whether the site is enrolled in scheduled off-host backup archiving to S3 Glacier Instant Retrieval. backup_relay_frequency (daily / twice_weekly / weekly, nullable) is a per-site cadence for unhosted/custom sites; null inherits the fleet setting on /settings/backup-relay. backup_relay_last_archived_at (nullable timestamp) tracks when an archive run was most recently completed. Companion/direct-to-S3 runs also store backup_relay_last_size_bytes and backup_relay_last_sha256.
• Bill.com: bill_com_customer_id, bill_com_customer_name, bill_com_linked_via_invoice, bill_com_linked_at.
• Companion: companion_installed, companion_version, companion_capabilities, companion_secret (encrypted), companion_last_seen_at, companion_snapshot (JSON), companion_snapshot_at, companion_stuck_since + companion_stuck_reason (both nullable). Set by clockwork:detect-stuck-companion-state (daily sweep) when a site transitions into a stuck state — a failed install never retried past 24h, or an installed Companion gone silent past the snapshot-staleness threshold (3 days). Cleared back to null on recovery. The pair exists purely to make that transition detectable for alerting. Companion traffic visibility can also be conditionally gated via canViewCompanionTraffic().
• Uptime: uptime_monitoring_enabled, uptime_state, uptime_last_checked_at, uptime_last_up_at, uptime_last_status_code, uptime_consecutive_failures, uptime_down_since, uptime_require_keyword (optional homepage string the 5-minute probe must find), uptime_skip_body_check (skip the white-screen length heuristic; fatals and required keyword still apply).
• Work logs: site_work_logs (worked_on, hours, description, user_id) feed the Client Reports work_log section.
• Flagged WP admins: ignored_wp_admins acknowledges a specific snapshot admin (site_id + subject). Site-level /issues mute uses ignored_issues.issue_type = wp_admin_flagged.
• Performance: performance_scan_region (GTmetrix location ID override; NULL = global default), psi_unavailable_at + psi_unavailable_reason (circuit-breaker after 3 consecutive failed scans — name is historical, applies to any engine; auto-cleared on the next successful scan).

Site::query() excludes archived rows by the notArchived global scope. Bypass with Site::withoutGlobalScopes() in import / migration paths — see Concepts → Server, Site, Care plan, Hosting tier.

Site::isPressable() / Site::isSpinupWp() branch on hosting_provider — both @deprecated as of the modularization roadmap’s Phase 5 in favor of Site::host()->supports(HostingProvider::CAP_*) for behavior gating (e.g. CAP_SSH, CAP_CERT_SYNC, CAP_BACKUP_RELAY), though for display-only “which provider is this” checks they’re still fine and most call sites haven’t migrated yet. Site::scopeHostMonitored() is true for a SpinupWP site whose server isn’t ignored/staging (the old check), OR any Pressable site (which has no server-level ignore concept to check) — this replaced whereHas('server', fn ($q) => $q->monitored()) across every fleet-wide monitoring command (uptime, SSL, security scans, Companion snapshot/capabilities refresh, resource metrics, action-log backfill) so Pressable sites get the same coverage without provider-specific branching in each command.

site_ingest_exclusions

Sticky denylist for host-API inventory. One row per (hosting_provider, domain) when an operator removes a SpinupWP or Pressable site from Clockwork Control while the WordPress site is still live at the host. Columns: hosting_provider, provider_site_id (the SpinupWP / Pressable id at drop time), domain, nullable site_id (the archived sites row; nullOnDelete so a later hard-delete of the site cannot recreate it), reason, excluded_by.

clockwork:import-spinupwp and clockwork:import-pressable skip a listing when the domain matches any exclusion (so a dropped SpinupWP site is not silently converted to Pressable) or when this provider’s host site id matches (a rename at the host stays dropped). Unarchive deletes the matching rows so the next import can re-attach. Custom / WP Engine / Kinsta / Cloudways / GridPane archive does not write a row — those importers are not the resurrection path this table exists to block.

tags + server_tag

Three canonical tags: Dedicated, Shared, Staging. The Capacity page filters on Shared. Server tier lives here, not as a column on servers.

users

The auth allowlist. A row exists ⇒ allowed. revoked_at IS NULL ⇒ active. password (nullable, hashed-cast Bcrypt/Argon2) now backs local email/password sign-in — NULL means the account is SSO-only. Supports multiple OAuth providers via google_id, github_id, and microsoft_id (handled via App\Services\Auth\OAuthLoginHandler); local password and OAuth are independent and either/both can be set per user. See Architecture → Security model for the auth flow. theme (nullable string, default 'light') stores the user’s UI theme preference — light, dark, high-contrast, or system (labeled “Auto” in the UI) — validated against AppearanceSettingsController::VALID_THEMES on POST /settings/appearance. Midnight mode was removed; a stored midnight value is mapped to dark for backward compatibility rather than migrated.

installed_modules

One row per tracked module (slug, is_enabled, timestamps) via App\Models\InstalledModule. Manages runtime module activation and deactivation (ModuleStateResolver::isEnabled($slug)), driven by /settings/modules and the setup picker at /setup/modules.

app_settings

Singleton key/value store via App\Support\Settings. Holds the ingest schedule, per-source enable + last_run_at, logs.threat_retention_* (raw nginx keep-window), auto_approve_repeats_enabled, monitoring thresholds, fleet-wide domain ignore wildcards (monitoring.ignored_domain_patterns), custom issue category priority tiers (issues.category_levels), and the global care plan policy toggle (care_plans.enabled). Driven by /settings/ingest, /monitoring/settings, /settings/care-plans, and the Issues category visibility modal.

integration_credentials

One row per (integration, key) pair — e.g. ('pressable', 'client_secret'), ('digitalocean', 'token') — via App\Models\IntegrationCredential, value encrypted at rest ('encrypted' cast) and $hidden so it’s never serialized back out. Deliberately a separate table from app_settings: that one is plain unencrypted JSON, fine for toggles but wrong for secrets. unique(['integration', 'key']). Read through App\Support\CredentialResolver — DB-first, falling back to config('clockwork.{integration}.{key}')/.env when no row exists, so every client keeps working .env-only forever; /settings/integrations is the UI. Every credential-holding client resolves through this instead of reading config() directly.

Threat-detection + bans

nginx_log_cursors

One row per (site_id, log_path). Tracks inode + offset so the tailer survives logrotate. Without this, every rotation re-reads from byte 0 and we’d double-ingest.

threat_logs

Append-only stream of every nginx access-log line. Indexed on (site_id, event_at). Pruned nightly to the /settings/ingest window (default 30 days). On MySQL the table is monthly RANGE (UNIX_TIMESTAMP(event_at)) partitions so DROP PARTITION frees disk; the primary key is (id, event_at) and there is no foreign key to sites (MySQL forbids FKs on partitioned InnoDB). Raw is the prune candidate; rollups are durable forever.

allowed_bots

Refreshed daily from arcjet/well-known-bots. UA-string match only in v1 — known to be spoofable; reverse-DNS verification is planned.

review_queue

Pending lockouts/blocks waiting on a human (or the auto-approve gate). Promoted to queued_for_ban on approve.

blocked_ips

What’s currently or historically banned. source ∈ wordfence|llar|nginx|manual. decision ∈ approved|dismissed|auto. unbanned_at set on undo so the UI stays honest.

Metrics + rollups

server_metrics

Append-only DO-API metrics. Indexed on (server_id, recorded_at). Pruned to 90 days.

server_update_snapshots

One row per server, unique on server_id. Latest poll wins — populated by clockwork:poll-system-updates (daily 04:15) which SSHs to every server SpinupWP flagged with upgrade_required=true. Stores total_updates + security_updates (from apt-check), reboot_required + reboot_required_pkgs[] (from /var/run/reboot-required[.pkgs]), upgradable_pkgs[] (parsed apt list --upgradable), poll_status (ok|ssh_failed|parse_failed), poll_error, polled_at. Fills the count + per-package gap that SpinupWP’s API doesn’t expose.

site_traffic_daily

Per-site WPE-style daily rollup. Unique on (site_id, date). Idempotent upsert. Includes requests, unique_ips, visits, bytes_sent, status-code buckets, top_paths + top_ips JSON.

The “visit” definition is DISTINCT IP per UTC day, excluding 403s and static-asset paths. We deliberately don’t filter known bots at rollup time — adding that made the per-day query 50–100× slower.

Site lifecycle

site_uptime_events

Transition log (down/up), NOT one row per probe. Indexed on (site_id, event_at).

site_security_scans

Polymorphic by scan_type ∈ sitecheck|core_checksums|blacklist|companion_malware. One coarse status (clean|issues_found|failed) drives every dashboard regardless of scan type. Adding a Phase-2 type is a new constant on SiteSecurityScan, not a migration.

companion_malware is the in-WP scanner that bypasses Cloudflare — runs via the Companion plugin’s HMAC-signed endpoint when available, falls back to SSH wp-cli. Replaces SiteCheck’s role on Cloudflare-fronted sites where Sucuri’s external scanner gets 403’d at the edge.

site_performance_scans

One row per Lighthouse run. Stores Performance score + LCP/FCP/TBT/SI/CLS + page weight + request count, plus engine (gtmetrix / psi / psi-fallback / pressable) — GTmetrix is primary, PSI only fills in when GTmetrix errors; pressable is Pressable’s own always-available report (no fallback chain). CLS is stored ×1000 as integer to avoid float drift in MySQL — divide on read.

accessibility_score / best_practices_score / seo_score (nullable) — extracted for pressable and psi/psi-fallback rows (PageSpeedInsightsClient and PressableLighthouseClient extract them, while GTmetrix’s current tier focuses primarily on Performance metrics). source_generated_at (nullable) — the report’s own generation time, distinct from poll time; null for GTmetrix/PSI (every call is a fresh scan), set for Pressable (whose report only regenerates ~monthly). PerformanceScanRecorder uses it to skip inserting a duplicate row when Pressable’s underlying report hasn’t actually changed since the last poll — see Features → Performance scans.

site_field_metrics

One row per site × form factor (phone/desktop) × scope (origin/url) × collection period. Stores real-user Core Web Vitals measured from Chrome browsers over rolling 28-day windows from Google’s Chrome UX Report (CrUX) API. Columns: lcp_p75_ms, inp_p75_ms, cls_p75_x1000, fcp_p75_ms, ttfb_p75_ms, cwv_pass (bool pass assessment), good_pct (JSON distribution of “good” bucket per metric), status (ok/no_data/failed), period_start, period_end. Unique on (site_id, form_factor, scope, period_end).

email_auth_checks + email_auth_domains

Modular email security posture verification (modules/EmailAuth). email_auth_checks stores historical DNS validation runs per apex domain (180-day retention): overall status (pass/warn/fail/unknown), SPF record & 10-lookup count, DMARC policy & record, DKIM probed selectors found, MX presence, and advisory findings JSON. email_auth_domains stores per-domain settings (custom DKIM selectors, ignore status) and tracks last_overall_status for state-transition alerting (email_auth_degraded).

contact_form_tests + contact_form_test_runs

Per-site contact-form configuration and smoke-test history (the $9/mo care-plan feature, decoupled into modules/ContactForms). contact_form_tests stores configured forms (up to 3 per care-plan site, frequency weekly/daily, failure streak, status), while contact_form_test_runs logs every execution attempt and result.

Audit + ops

action_logs

Generic audit trail. action_type is a string; details is freeform JSON. Indexed on (site_id, ran_at) and (action_type, ran_at). Written by App\Services\ActionLog\ActionLogger from controllers; surfaces on the per-site Overview Recent activity card and /maintenance-history. Logger never throws — logging an action must not break the action.

ActionLog::UPDATE_TYPES groups the four update-job constants (plugin_update, theme_update, core_update, translations_update) — the maintenance-history “All updates” quick filter and the /updates page’s History link both use it instead of re-enumerating the list at each call site. Recent additions to the constant list: TYPE_COMPANION_UNINSTALL and TYPE_SERVER_UPDATE_FAILED (a live apt-get failure now logs distinctly from the reaper’s stuck-and-timed-out server_update_reaped case), TYPE_SITE_DEACTIVATED/TYPE_SITE_REACTIVATED (logged when a site’s is_inactive flag is toggled — see the sites table above), TYPE_THEME_CHANGED (recorded when a user changes their UI theme), and TYPE_BACKUP_RELAY_SYNCED/TYPE_BACKUP_RELAY_ARCHIVED (recorded during backup relay sync and archival runs).

backup_relay_runs

One row per run of the backup relay pipeline that archives off-host site backups across supported hosting providers (Pressable, SpinupWP, and any provider implementing HostingProvider::CAP_BACKUP_RELAY via Modules\Core\Contracts\BackupRelayAdapter) directly to S3 Glacier Instant Retrieval, replacing host-limited retention windows. Supports two operational modes: in-repo mode (default, executing Modules\BackupRelay\Jobs\ArchiveSiteBackupJob natively via Laravel queues) and external-agent mode (coordinating with a standalone droplet via S3 targets.json and last-report.json). Columns: sites_total, sites_archived, sites_skipped, sites_failed, failures (JSON array of {domain, error}), started_at, finished_at. pull-backup-relay-report (in external-agent mode) or the in-repo runner also detects relay silence — 6+ days since the last row fires a Mattermost/Slack alert once, cleared on the next fresh run. See Features → Backup relay.

bill_com_customers + bill_com_care_plan_items

Local cache of the daily Bill.com sync. Customers PK is Bill.com’s own ID (0cu...); items PK is Bill.com’s Item ID. Drives sites.care_plan_enabled automation.

ignored_issues

Suppresses one specific issue type on an active, fully-monitored site or server without archiving it or flipping is_inactive — see Features → Inactive sites for how this compares to the other “quiet down” mechanisms. Columns: issue_type (indexed string, e.g. IgnoredIssue::TYPE_SEO_INDEXABILITY = seo_indexability), site_id (nullable FK to sites, cascades on delete), server_id (nullable FK to servers, cascades on delete), reason (nullable, operator’s free-text note), ignored_by_user_id (nullable FK to users, null on delete). A unique constraint on (issue_type, site_id) means re-ignoring the same issue on the same site updates the existing row instead of duplicating it. Written and read by IssuesController::ignore()/unignore(); IssueCounter and the /issues index both exclude matching rows from their active counts.

ai_remedy_runs

Audit trail and diagnostic state for AiRemedy incident diagnoses (modules/AiRemedy). Stores incident trigger telemetry, prompt parameters, root cause diagnosis, proposed and approved remediation bash commands, execution outputs, and token costs. Columns include actor (watch_mode/interactive/autonomous/simulation/manual), status (pending/analyzed/resolved/failed/rejected/expired), safety_tier (1–3), verdict (correct/partial/wrong/unsure), verdict_note, verdict_by_user_id, verdict_at, outcome (self_resolved/human_resolved/persisted/escalated/unknown), outcome_details JSON, outcome_evaluated_at, is_fixable, is_maintenance, maintenance_type, hidden_at, and hidden_by_user_id. Automatically classified by clockwork:ai-remedy-evaluate-outcomes at +60 minutes.

Encrypted columns

Anything sensitive is Crypt-cast in the model, ciphertext at rest:

• servers.ssh_private_key, servers.ssh_password
• sites.db_password, sites.companion_secret

Never log them. Never render them in views or API responses.

Migrations

Migrations live in database/migrations/. They are deliberately renumbered (192640, 192641, …) so foreign keys resolve in dependency order, not chronological order. Don’t reorder without checking the FK chain.

For a fresh dev DB:

php artisan migrate:fresh

That destroys data — confirm before using outside dev.