Zoth schema tour — tables, cascades, and audit-grade thinking
Every table, every FK action, every index — with the reasoning behind each.
Know your own system
The full inventory (migrations/postgres/001–007, goose, embedded, auto-applied under pg_advisory_lock(72183921)):
| Table | Key columns | Notes |
|---|---|---|
tenants | idp_tenant_id UNIQUE | maps the IdP's org to your row |
users | idp_user_id UNIQUE, manager_id FK→users SET NULL, role TEXT DEFAULT 'MEMBER' | first synced user becomes ADMIN |
groups, user_groups | composite PK (user_id, group_id) | M:N, PK is the constraint |
integrations | platform_type enum, credentials_encrypted JSONB | AES-256-GCM envelope (Day 32) |
resources | external_id, sensitivity DEFAULT 'MEDIUM' | no tenant_id — via integration |
policies | priority, 3 JSONB columns, is_active | first-match ordered eval |
access_requests | status enum, policy_id FK SET NULL, rag_* advisory columns, expires_at | the state machine's home |
approvals | decision_source enum, rag_reasoning, decided_at | one routed row per request |
policy_documents | embedding VECTOR(768) | HNSW cosine index |
audit_logs | prev_hash, hash, append-only | tamper evidence (Day 34) |
api_keys | key_hash UNIQUE, key_prefix(8), scopes JSONB | machine credentials |
notifications | type, channel, read_at | in-app |
outbox_events | published_at NULL, partial index | relay backlog |
processed_events | event_id PK | consumer dedup |
FK actions as lifecycle documentation — this is the interview framing that elevates a table list into design thinking:
tenants → everything: ON DELETE CASCADE— deleting a tenant annihilates the org's data. One row, one blast radius. (You should have opinions: is that desirable? For a demo yes; for compliance you might want soft-offboarding + 30-day purge.)integrations → resources: CASCADE— disconnecting an integration removes its discovered resources; access_requests referencing those resources keep their resource_id dangling? No — requests reference resources with RESTRICT? Actually check: requests FK to resources would cascade or restrict... the design intent you can defend: request history must survive resource deletion, so the FK should be SET NULL or the join documented via audit metadata. Know your actual migration: read001_goose_init.sqlbefore any interview and be able to recite the FK actions — saying "I'd have to check" on your own schema is the one unacceptable answer.users.manager_id SET NULL— deleting a manager orphans the chain upward, not the users; routing falls back to UNROUTED (Day 38's semantics).access_requests.policy_id SET NULL— Day 25's point: frozen decisions.
Why role is TEXT not an enum (005): it was added post-hoc to an existing table; TEXT + app validation (RoleAdmin/RoleMember) kept the migration trivial. A CHECK constraint (role IN ('ADMIN','MEMBER')) would recover the safety — worth doing, worth mentioning as "the migration I'd write next."
Vector column details (007): vector(768) — the embedding model is text-embedding-004 (768 dims), changed from an earlier 1536-dim schema — the migration is literally ALTER COLUMN embedding TYPE vector(768) USING NULL::vector (a reset, not a conversion — embeddings must be re-ingested after dimension changes; a real lesson in model-dimension coupling) + CREATE INDEX ... USING hnsw (embedding vector_cosine_ops).
What's absent, by design: no deleted_at anywhere — audit-grade systems prefer hard deletes + immutable history over soft-delete ambiguity (Day 36 covers the tradeoff). No updated_at on most tables — rows are either immutable facts (audit, approvals, events) or transition via the status column.
Bulletproof your resume
Say these answers aloud before moving on
These are the follow-ups an interviewer will actually ask about your projects.
Why no soft deletes in zoth?
Soft deletes optimize for 'undo', at the cost of every query needing deleted_at filters and every unique constraint fighting ghost rows. Zoth's requirements are audit instead: what matters is proving what happened — the hash-chained append-only audit_logs is the record, and deletions are hard + cascaded. If a tenant requests deletion, that's a compliance workflow (GDPR erasure) implemented as a documented purge, not a flag flip. Traverse, by contrast, uses User.deletedAt with a 7-day recovery window — a product feature, where undo is the point.
What's the blast radius of deleting a tenant?
Cascades wipe users, groups, integrations, resources, requests, approvals, policies, keys, notifications, audit. That's the designed behavior: a tenant is the unit of data ownership. Two things I'd harden for production: a pre-delete export job (the audit log has evidentiary value the tenant may owe a regulator) and an admin-only double-confirmation flow, since CASCADE here is the biggest hammer in the schema.
Why store RAG outputs as columns on access_requests?
They're advisory snapshots tied to the moment of the request: risk score, approval likelihood, ETA, reasoning. Storing them freezes the LLM's view for later analysis (calibration, audit, 'why did auto-approve fire?') without giving them decision authority — the decision columns are status/policy_id. Denormalized snapshot on the row it describes, refreshed never: the request row is its own historical record.