# 27 — Database reference model

## Purpose

`database/reference-schema.sql` is the canonical relational reference for Milestone 0. The selected migration/ORM layer may express it differently, but table purpose, ownership, immutability, uniqueness and deletion behavior must remain equivalent.

## Conventions

- PostgreSQL.
- Opaque UUID identifiers.
- UTC `timestamptz` values.
- `jsonb` only for versioned canonical documents or evidence whose shape is governed by an external schema.
- Frequently queried ownership, status and timestamp values remain typed columns.
- Workspace-owned tables include `workspace_id` directly or inherit it through a mandatory parent with authorization-safe queries.
- Soft deletion is used only where history or external references require it; otherwise explicit deletion with audit is preferred.

## Identity records

The model defines users, revocable sessions, invitations, password-reset tokens, workspaces and memberships. Token values are stored as hashes. Personal workspaces are ordinary workspaces with one owner membership.

## Playbook records

`playbooks` contains stable identity. `playbook_versions` contains immutable published packages or mutable draft versions. Published rows have a content digest and cannot be updated in place. Favorites and collections reference stable playbook identity; generated runs reference exact versions.

Private publication evidence remains explicit: append-only `playbook_review_attestations` bind a human review, validation state, lint count, limitations acknowledgement and safety-regression state to one exact draft digest. Static evaluation cases and immutable results record target, fixture and environment digests separately; legacy rows without those bindings are retained but cannot satisfy lifecycle promotion.

## Repository records

A repository may be manual or associated with a forge integration. Profile revisions and completed snapshots are immutable. Findings belong to an exact snapshot and may be dismissed or resolved without changing the evidence.

## Composition and generated tasks

Composition drafts are mutable. Final generation stores all resolved input and policy snapshots plus the exact rendered bytes and digest. The database term remains `generated_run` for compatibility with the product specification; the UI should call it a **Generated task** until direct execution exists.

## Integrations and secrets

Integration metadata and encrypted secret envelopes are separate. An integration secret can be rotated without rewriting historical snapshots. The plaintext token never appears in the database.

## Jobs and operations

Jobs contain lease, attempt, progress and redacted error state. Audit events are append-only. Support bundles are artifacts and must never include secret envelopes.

## Immutability

The application layer is authoritative, but database protections should prevent accidental updates to:

- published playbook versions;
- repository profile revisions;
- completed repository snapshots;
- generated runs;
- completed evaluation results;
- audit events.

A trigger or restricted repository API may enforce this. Tests must prove direct application updates are rejected.

## Deletion

- Deleting a user disables login and applies configured personal-data deletion behavior.
- Deleting a repository never changes the frozen profile snapshot inside a generated run.
- Deleting generated artifacts may retain immutable prompt text and metadata.
- Deleting an integration deletes encrypted credentials and prevents refresh but may retain normalized historical evidence.
- Built-in published playbook versions are not deleted by ordinary users.

## Migration requirements

The first migration creates all MVP tables and indexes in dependency-safe order. Later destructive changes use expand/migrate/contract. Every migration test starts from an empty database and upgrades from the latest released fixture.