Database | Gospel in Action

Canonical definition: services/api/app/models.py. Postgres in production, SQLite for local Phase 1. All timestamps are UTC; all primary keys are UUID strings except append-only sequence tables.

Traceability chain

content_projects
  └── agent_runs            (constitution_sha256, policy_pack_sha256, cost, idempotency)
  └── workflow_events       (append-only status history)
  └── theological_audits    (findings, status, publishable, constitution_sha256, content_sha256)
  └── assets                (provider, model, sha256, licence, cost)
  └── publications          (youtube_video_id, audit_id, idempotency_key)
        └── analytic_snapshots

A published video can therefore be reconstructed: which policy governed it, which agent produced each artifact, what it cost, what the audit said, and which human (if any) overrode it.

Tables

Table	Purpose	Key constraints
`constitution_versions`	immutable policy snapshots	unique `version`; `sha256` stored
`content_projects`	one video/short/artifact lineage	`status`, `constitution_version`
`workflow_events`	append-only transition log	unique `idempotency_key`
`theological_audits`	every audit run	`status`, `publishable`, `findings` JSON
`agent_runs`	every agent invocation	unique `idempotency_key` per attempt
`assets`	images, audio, renders, thumbnails	`sha256` index for dedupe
`publications`	YouTube records	unique `youtube_video_id`, one per project
`channel_snapshots`	channel-level metrics	append-only, `source` label
`analytic_snapshots`	per-video metrics	append-only, `window_days`
`provider_usage`	spend ledger	provider, operation, units, cost
`policy_changes`	authorised policy edits	version, summary, authoriser

Notes

theological_audits.findings keeps the full finding list, including non-blocking qualifications. Audits are never updated except for a recorded override, which sets reviewer and override_reason.
content_sha256 lets the system detect post-approval edits: an artifact whose hash changed after the audit is not publishable on the old audit.
assets.sha256 prevents paying twice for the same image or render.
No credentials are stored in the database. OAuth refresh tokens live in the secrets manager.
Migrations: Phase 1 uses create_all; introduce Alembic before the first production deployment and never mutate a shipped schema by hand.

Indexing for the dashboard

content_projects(status), theological_audits(status), theological_audits(publishable)
agent_runs(project_id), workflow_events(project_id)
assets(sha256), publications(youtube_video_id)

Retention

Audits, workflow events, policy changes and publications are permanent. Generated assets keep the hash record permanently; binaries may be archived after 12 months (the record remains).