Skip to content

R3 Database + alembic audit — 2026-05-31

Scope: alembic revisions 014–027 in services/wp-pulse-server/alembic/versions/, all model files under api/models/, plus live LXC schema (192.168.0.211, db wp_pulse, alembic head 027).

Summary

  • Linear chain: PASS — 014 → 015 → 016 → 017 → 018 → 019 → 020 → 021 → 022 → 023 → 024 → 025 → 026 → 027. Every down_revision lines up with the previous head; no branching, no orphan revisions.
  • Models without migrations: 0
  • Migrations without models: 0 (33 __tablename__ definitions match 33 application tables on the live LXC; the 34th table alembic_version is alembic-internal).
  • Critical findings: 0
  • High: 2
  • Medium: 6
  • Low: 7

Live schema baseline

Live LXC 192.168.0.211 reports SELECT version_num FROM alembic_version → 027. Public tables (34): alembic_version, approvals, backup_drills, commands, fleet_alerts, fleet_batch_items, fleet_batches, heartbeats (TimescaleDB hypertable on observed_at, 3 chunks, compression off), pending_db_changes, pending_user_actions, plugin_vulnerabilities, pulls, pushes, pushes_v2, rehearsals, schedule_runs, scheduled_applies, site_branches, site_feedback, site_integrity_checks, site_lighthouse_runs, site_login_failures, site_manifests, site_metrics_daily, site_reports, site_schedules, site_status_pages, site_versions, sites, snapshot_ingests, version_diff_jobs, version_previews, version_reference, wpcli_audit.

Findings

[HIGH] FK without ON DELETE policy — rehearsals.source_version_id

  • Table/column: rehearsals.source_version_id → site_versions.id
  • File: alembic/versions/016_f7_rehearsal.py:67, model api/models/rehearsal.py:38
  • Live constraint: rehearsals_source_version_id_fkey FOREIGN KEY (source_version_id) REFERENCES site_versions(id) (no ON DELETE clause — defaults to NO ACTION).
  • This is the only application-level FK in the entire schema without an explicit ON DELETE policy (catalog-wide audit ran against pg_constraint for contype='f'; the other 5 hits are all internal _timescaledb_catalog.* rows). Sibling references on the same parent (pushes_v2.pre_apply_version_id ondelete SET NULL, backup_drills.version_id ondelete SET NULL) use SET NULL. So a manual DELETE FROM site_versions for an old snapshot row that is still referenced by a rehearsal will fail with violates foreign key constraint, including the case where the parent site itself was deleted — the site_versions cascade-delete fires first but cannot complete because rehearsals.source_version_id still points at it. Practical consequence: deleting a site whose rehearsals lane was ever exercised against a saved version will fail mid-cascade.
  • Recommendation: add ondelete="SET NULL" to match siblings. New migration: 
    op.drop_constraint("rehearsals_source_version_id_fkey", "rehearsals", type_="foreignkey")
op.create_foreign_key(
    "rehearsals_source_version_id_fkey", "rehearsals", "site_versions",
    ["source_version_id"], ["id"], ondelete="SET NULL",
)
    and update api/models/rehearsal.py:38 to ForeignKey("site_versions.id", ondelete="SET NULL").

[HIGH] pending_db_changes has no index supporting the unapplied-rows lookup

  • Table: pending_db_changes
  • File: alembic/versions/022_wp_console_state.py:70-74
  • The table's only secondary index is ix_pending_db_changes_site_applied (site_id, applied_at). The dominant query per api/routers/pending.py / apply-all-to-prod is WHERE site_id = :s AND applied_at IS NULL ORDER BY created_at. A B-tree on (site_id, applied_at) does cover IS NULL but the planner cannot rely on the second column for ordering: rows are returned in applied_at order, not created_at. With realistic operator console traffic this is a Sort on top of an index scan. Worse, the index is bloated by rows whose applied_at is non-NULL (already replayed) — those should never participate in the hot lookup.
  • Recommendation: replace with a partial index, e.g. CREATE INDEX ix_pending_db_changes_site_pending ON pending_db_changes (site_id, created_at) WHERE applied_at IS NULL;. Same shape would help pending_user_actions (same migration, lines 105-109).

[MEDIUM] wpcli_audit index uses idx_ prefix instead of ix_

  • Table: wpcli_audit
  • File: predates 014 (013), present on the live DB as idx_wpcli_audit_site_time.
  • Every other public index in the database uses the SQLAlchemy/alembic default ix_ prefix (84/85 indexes). This one is idx_…. Cosmetic but means scripts that filter on ix_* (e.g. monitoring or repository linters) miss this row.
  • Recommendation: rename in a future migration: ALTER INDEX idx_wpcli_audit_site_time RENAME TO ix_wpcli_audit_site_executed.

[MEDIUM] sites.premium_licenses is declared in 020 + model but has no readers/writers in code

  • Column: sites.premium_licenses (JSONB NOT NULL default '{}'::jsonb)
  • File: alembic/versions/020_plugin_manager.py:37-45, model api/models/sites.py:148-152
  • Grep across api/ for premium_licenses returns only the model declaration. No router, service, cron or migration reads or writes the column. The ADR-0014 §A8 contract ("Auto-applied via wp option update after install/update of the matching slug") is unrealised at code level.
  • Recommendation: either ship the writer (plugin-manager install/update hook) or drop the column. The default '{}'::jsonb means production rows are not wasting space, but the contract drift is misleading for future readers of the model.

[MEDIUM] apply_push_id soft FK is undocumented at SQL layer

  • Table/column: pending_db_changes.apply_push_id
  • File: alembic/versions/022_wp_console_state.py:65-68
  • The migration comments explain the rationale (pushes_v2.push_id is TEXT, not UUID, so no hard FK), but at the SQL level the column is a bare Text with no index. Apply-time logic in apply-all-to-prod will UPDATE pending_db_changes SET applied_at=now(), apply_push_id=$1 WHERE site_id=$2 AND applied_at IS NULL then later read back rows filtered by apply_push_id. There is no index for the reverse lookup, and no CHECK that the value exists in pushes_v2.push_id.
  • Recommendation: (a) add ix_pending_db_changes_apply_push_id partial index WHERE apply_push_id IS NOT NULL; (b) consider a deferred trigger that validates the soft FK against pushes_v2.push_id on commit.

[MEDIUM] site_login_failures lacks an index on last_seen for pruning

  • Table: site_login_failures
  • File: alembic/versions/023_security_toolkit.py:78-91
  • PK is (site_id, ip). The hourly cron decays / prunes rows older than 24 h: DELETE FROM site_login_failures WHERE last_seen < now() - interval '24 hours'. With no index on last_seen, that DELETE is a sequential scan. Volumes are likely low (one row per (site,ip) hit) but with a 1000-site fleet under brute-force probing the table grows fast.
  • Recommendation: add ix_site_login_failures_last_seen on last_seen.

[MEDIUM] Nullability mismatch — Site.bisect_state documentation drift

  • Column: sites.bisect_state (JSONB nullable)
  • File: model api/models/sites.py:108-111
  • Model declares Mapped[dict | None] (correct, matches alembic 017). However the docstring spec at lines 105-107 lists a fixed shape ({id, started_at, symptom, active_plugins_snapshot, candidates, currently_disabled, step_log}). Cross-checking against the actual writer in api/services/quarantine_bisect.py — the shape matches, no drift. No action needed; flagged here only to confirm the read for the audit.

[MEDIUM] site_metadata JSONB shape contract is unstable

  • Column: sites.metadata (alias site_metadata on the model)
  • The model docstring lists no documented shape. Code grep finds api/services/two_fa_audit.py:105 writing site_metadata["two_fa_audit"] and api/services/cache_plugins.py reading/writing other keys. There is no central inventory of what top-level keys the column carries.
  • Recommendation: extend the docstring on api/models/sites.py:46-50 with the current known key set (two_fa_audit, cache_plugins, …) and the per-key shape so future audits don't have to grep.

[LOW] Downgrade scripts — all 020–027 are reversible

Audited every downgrade() in 020–027. All drop the indexes + columns + tables they create, in correct reverse order. No pass / NotImplementedError / mismatched order. Concretely:

  • 020: drops ix_plugin_vulnerabilities_plugin_slug, table plugin_vulnerabilities, column sites.premium_licenses. Reverse order vs upgrade. OK.
  • 021: drops index + column on pushes_v2. OK.
  • 022: drops two tables in reverse order (pending_user_actions, pending_db_changes) with their indexes. OK.
  • 023: drops ix_backup_drills_site_drilled, table backup_drills, site_login_failures (no extra index), ix_site_integrity_checks_site_checked, table site_integrity_checks. Reverse order. OK.
  • 024: drops index + table. OK.
  • 025: drops sites.current_branch, sites.require_approval, table site_branches, table approvals (+ index), table scheduled_applies (+ both indexes). OK.
  • 026: drops two columns. OK.
  • 027: drops three tables (status_pages, feedback, reports) + sites.report_enabled. OK.

[LOW] Composite PKs and uniqueness coverage

All composite PKs declared in migrations have matching PrimaryKeyConstraint/primary_key=True in the model: - plugin_vulnerabilities (plugin_slug, cve) — OK. - site_login_failures (site_id, ip) — OK. - site_branches (site_id, name) — OK. - site_metrics_daily (site_uuid, day) — OK. - heartbeats (site_id, observed_at) — OK (Timescale hypertable; FK to sites intentionally omitted per model docstring). - site_status_pages (site_id) — single-col PK, also OK.

Unique constraints: - version_previews.hostname UNIQUE — declared and live. - site_status_pages.hostname UNIQUE — declared and live. - approvals.token UNIQUE — declared and live. - sites.slug, sites.pubkey_ed25519 — both unique, live. - pushes_v2.push_id UNIQUE — declared and live.

[LOW] Hypertable coverage

  • heartbeats is the only hypertable (Timescale, partition key observed_at, 3 chunks). Confirmed via timescaledb_information.hypertables.
  • site_metrics_daily is a regular table; row volume is bounded (≤365 × site_count) so this is appropriate.
  • site_lighthouse_runs, site_integrity_checks, backup_drills, site_login_failures, wpcli_audit are all candidate time-series tables but none are large enough today to justify hypertabling. The (site_id, ts DESC) btree indexes on each are adequate for the per-site list views.
  • No action required. Flag for future revisit if any of these grow past ~10M rows.

[LOW] Indexing — duplicate / missing composite indexes

Reviewed every site_id-keyed query column pair: - commands (site_id, status, created_at DESC) — composite present. - fleet_alerts (status, created_at) plus single-col (site_id) — adequate. - scheduled_applies (status, scheduled_for) and (site_id) — adequate. - approvals (site_id, status) — composite present. - version_previews (status, expires_at) and (site_uuid) — adequate. - site_versions (site_id) plus (created_at) — could add (site_id, created_at DESC) for the per-site history page, but the planner can index-merge today; volume is low. - pulls (site_id) and pushes (site_id) — single-col, low cardinality of dependent queries; acceptable.

No duplicate indexes detected (e.g. no (a) shadowed by (a, b) where neither is also a UNIQUE constraint).

[LOW] Nullability mismatches model ↔ migration

Spot-checked every column added in 020–027 against its model declaration. All match: - sites.premium_licenses — NOT NULL default '{}' in both. - sites.import_status — NOT NULL default 'enrolled' in both. - sites.import_metadata — NOT NULL default '{}' in both. - sites.require_approval — NOT NULL default false in both. - sites.current_branch — NOT NULL default 'main' in both. - sites.report_enabled — NOT NULL default false in both. - pushes_v2.pre_apply_version_id — nullable in both, ON DELETE SET NULL in both. - version_previews.progress_* — all three nullable in both.

Every NOT NULL add in 020–027 includes a server_default, so ALTER TABLE against the existing 8 production sites was safe. No drift.

[LOW] Schema-vs-ADR alignment

Sampled ADRs against migrations: - 020 vs ADR-0014 §A5 + §A8: spec calls for plugin_vulnerabilities (plugin_slug, cve) PK and sites.premium_licenses JSONB — both present, no extras. - 023 vs ADR-0017 §H: spec calls for three tables (site_integrity_checks, site_login_failures, backup_drills) — all present, columns line up with ADR data model. No extras. - 024 vs ADR-0017 §I: spec lists site_lighthouse_runs only — present. The status/error columns are an undocumented addition explained inline ("Not in the ADR data model but needed for the on-demand UX") — acceptable scope creep. - 025 vs ADR-0018 §J: three tables + two sites columns — all match. - 026 vs ADR-0018 §L: sites.import_status + sites.import_metadata — match. - 027 vs ADR-0018 §M: three tables + sites.report_enabled — match.

No schema-vs-spec drift detected for 020–027.

[LOW] JSONB shape contracts — minor gaps

  • pending_db_changes.before / pending_db_changes.after — shape varies by kind (option | user | post | db_cleanup | db_query). The model docstring (api/models/pending_db_change.py) explains the column purpose but does not enumerate the per-kind shape. Writers in api/routers/wp_options.py, users.py, posts.py, wp_cron.py, db_tools.py all funnel through api/utils/pending_db_log.py:log which is permissive (anything dict | None).
  • sites.import_metadata — model docstring lists examples but no formal schema.
  • sites.bisect_state — shape documented inline (see Medium-severity entry above). OK.

Recommendation: optional but useful — add Pydantic models for each documented JSONB shape under api/schemas/jsonb_shapes/ and reference them from model docstrings. Not gating.

Live schema vs model vs migration — sample diffs

Inspected \d sites, \d pushes_v2, \d plugin_vulnerabilities, \d version_previews, \d rehearsals, \d approvals, \d scheduled_applies, \d site_branches, \d site_reports, \d site_feedback, \d site_status_pages, \d pending_db_changes, \d pending_user_actions, \d site_integrity_checks, \d site_login_failures, \d backup_drills, \d site_lighthouse_runs, \d site_metrics_daily, \d version_reference, \d site_versions, \d heartbeats, \d commands, \d fleet_alerts, \d wpcli_audit.

Zero drift. Every column type, default and constraint visible in the live DB is faithfully reflected in both the alembic migration that created it and the corresponding model file.

Top-3 actionables

  1. Add ondelete="SET NULL" to rehearsals.source_version_id (HIGH) — only FK in the codebase without an explicit policy; blocks future site/version pruning.
  2. Convert pending_db_changes and pending_user_actions indexes to partial indexes filtered on applied_at IS NULL (HIGH) — matches the dominant access pattern and prevents bloat from replayed rows.
  3. Either implement the premium-license writer or drop sites.premium_licenses (MEDIUM) — column is declared in 020 + model but currently unreferenced anywhere in api/.