Note

    ../../DOCS/database/operations-and-maintenance.md

    Source Summary title: Database Operations and Maintenance description: Operational procedures for Neon persistence, migrations, verification, and rollback st...

    Source Summary

    --- title: Database Operations and Maintenance description: Operational procedures for Neon persistence, migrations, verification, and rollback status: evolving lastUpdated: 2026-05-08 owner: Engineering --- # Database Operations and Maintenance ## Purpose This runbook defines how database-backed admin persistence and canonical knowledge-backbone storage are

    Imported Context

    title: Database Operations and Maintenance description: Operational procedures for Neon persistence, migrations, verification, and rollback status: evolving lastUpdated: 2026-05-08 owner: Engineering

    Database Operations and Maintenance

    Purpose

    This runbook defines how database-backed admin persistence and canonical knowledge-backbone storage are operated and maintained in this repository.

    Scope

    • Admin persistence backend: Neon/Postgres
    • Canonical public knowledge backbone (Neon/Postgres)
    • Tables:
      • admin_content_state
      • admin_history
      • admin_migrations
      • kb_entities
      • kb_entity_tags
      • kb_edges
      • kb_media_objects
      • kb_media_derivatives
      • kb_workflows
      • kb_workflow_steps
      • kb_provenance_runs
      • kb_entity_sources
    • One-off operational data migrations (for example Blob -> Neon)

    Source of Truth Policy

    • Production canonical backend is neon (ADMIN_STORAGE_BACKEND=neon).
    • file and blob are fallback or migration-source backends, not primary production write targets.
    • All migration execution metadata must be persisted in admin_migrations.
    • Production knowledge-backbone data is canonical for public content review unless an active project explicitly approves otherwise.
    • Local knowledge-backbone data is a review/development mirror by default. Production-to-local refreshes must export both environments before mutating local, verify expected production markers, and avoid production writes unless separately approved.
    • Intake-output repair work must use isolated fixtures or explicitly approved non-production targets until its data contract is revalidated.

    Ownership and Change Control

    • Owner: Engineering
    • Every database-affecting change must include:
      • migration script (or explicit statement that no migration is needed)
      • docs update in DOCS/database/ and/or DOCS/development/migrations.md
      • changelog entry
      • project checkpoint update in active project doc

    Migration Storage and Naming

    • Script folder: scripts/migrations/
    • Naming format:
      • YYYYMMDD_<project-number>_<source>_to_<target>_<short-purpose>.mjs
    • Current reference:
      • scripts/migrations/20260211_1002_blob_to_neon_admin_state.mjs

    Operations Scripts

    • Script details: scripts/ops/README.md
    • Post-deploy parity check:
      • npm run ops:parity:postdeploy
      • required env: ADMIN_PASSWORD
    • Neon backup/restore drill:
      • npm run ops:neon:backup-restore-drill
      • required env: DATABASE_URL (or POSTGRES_URL)
      • optional env: BACKUP_OUTPUT_PATH
    • Blob fallback verification:
      • npm run ops:blob:fallback-verify
      • required env: BLOB_READ_WRITE_TOKEN
      • optional env: ADMIN_BLOB_PREFIX, DATABASE_URL for Neon parity check
    • Knowledge drift verification:
      • npm run ops:knowledge:drift
      • required env: DATABASE_URL (or POSTGRES_URL)
      • compares legacy constellation payload versus DB-backed kb_* graph parity
    • Release-gated checks:
      • npm run ops:release-gate
      • default: parity check enabled, drill/fallback/knowledge drift opt-in
      • optional env:
        • RUN_OPS_PARITY=false
        • RUN_OPS_NEON_DRILL=true
        • RUN_OPS_BLOB_FALLBACK=true
        • RUN_OPS_KNOWLEDGE_DRIFT=true

    Required Migration Safeguards

    Each migration script must implement:

    1. Dry-run by default.
    2. Explicit apply flag (--apply) for writes.
    3. Idempotency guard (no silent re-runs).
    4. Precondition checks on target state.
    5. Transactional writes where possible.
    6. Migration ledger write to admin_migrations.
    7. Structured summary output (counts, hash, mode).

    Standard Procedure: Run a Migration

    1) Preflight

    1. Confirm project is linked to correct Vercel project:
      • cat .vercel/project.json
    2. Pull target environment variables:
      • npx vercel env pull /tmp/hyphenomenon.env --environment production
    3. Confirm required vars:
      • DATABASE_URL or POSTGRES_URL
      • source backend credentials (for Blob migrations: BLOB_READ_WRITE_TOKEN)

    2) Dry-run

    1. Export required env vars into shell.
    2. Run dry-run command:
      • npm run migrate:blob-to-neon:dry-run
    3. Verify:
      • source counts are expected
      • target pre-migration counts are expected
      • payload hash is present

    For knowledge backbone rollout:

    • npm run migrate:kb-schema:dry-run
    • npm run migrate:kb-backfill:dry-run

    3) Apply

    1. Execute:
      • npm run migrate:blob-to-neon:apply
    2. Confirm output:
      • post-migration counts
      • migration timestamp

    For knowledge backbone rollout:

    • npm run migrate:kb-schema:apply
    • npm run migrate:kb-backfill:apply

    4) Verify

    1. Verify Neon tables:
      • admin_content_state row count
      • admin_history row count
    2. Verify migration ledger:
      • migration name exists in admin_migrations
      • payload hash recorded
    3. Verify live API behavior:
    • GET /api/constellation response shape and expected metadata (for example domains)

    Drift Detection and Health Checks

    Per Deployment

    • Verify Vercel env values by target:
      • ADMIN_STORAGE_BACKEND=neon
      • DATABASE_URL present
    • Verify production deployment is ready (npx vercel ls / npx vercel inspect <deployment>).

    Weekly or After Material Admin Edits

    • Query Neon row counts for:
      • admin_content_state
      • admin_history
      • admin_migrations
    • Spot check /api/constellation and admin overview counts for expected parity.

    Rollback and Recovery Procedure

    If Neon behavior regresses or migration output is incorrect:

    1. Stabilize writes:
      • switch ADMIN_STORAGE_BACKEND to known-good fallback (blob) in target environments.
      • redeploy so environment snapshot is applied.
    2. Recover state:
      • restore from known source via migration script (force mode only with explicit intent).
    3. Audit:
      • record rollback details in project checkpoint and changelog.
    4. Return to Neon:
      • verify corrected state, then switch backend back to neon and redeploy.

    Documentation Maintenance

    • Keep this runbook aligned with actual scripts in scripts/migrations/.
    • Add each new migration to:
      • DOCS/database/index.md
      • changelog
      • project checkpoint log

    Latest Verified Drill Record

    • Verification window: 2026-02-11 14:38-14:40 ET (America/New_York)
    • Post-deploy parity check:
      • status: pass
      • key checks: nodes/edges counts aligned, domains aligned, build/notes node counts aligned, edge endpoint integrity valid
    • Neon backup/restore drill:
      • status: pass
      • source/restored rows: admin_content_state=1, admin_history=1
      • canonical hash match: 01bf99456bd9b848826c54a401015b8d6285277c45f4bbe89be89bad4017db6a
    • Blob fallback verification:
      • status: pass
      • canonical parity hash match:
        • blob: a4c7da177d89d5094e8f7c950ee5578f97a422a4645ce4ca591c87b015f28668
        • neon: a4c7da177d89d5094e8f7c950ee5578f97a422a4645ce4ca591c87b015f28668

    Provenance