Data Model

Data Model

prisma/schema.prisma: Postgres via Prisma 7 (@prisma/adapter-pg or @prisma/adapter-neon), thirteen models, plus the workflow world's own tables when self-hosted.

Full reference with every column: docs/data-model.md.

---

Four properties to hold in mind

Almost everything in the database is metadata about bytes that live elsewhere. The source, the normalized model, every export, report and batch vault are sealed in object storage and addressed by key. The columns point at them, checksum them, and record what was decided.

1. Extraction text never sits in the database in the clear. The normalized model is sealed outside the row. Redaction.text is the one exception, because a suggestion not yet decided still has to be shown, and it dies with its document. A pattern that outlives documents (OwnerRule) is sealed with the master key.
2. The row is the record. A durable run's progress lives on its row, not in the response that started it, so a window that closes and reopens reads the same truth.
3. Charges are idempotent, because the fact of having charged is written onto the row that produced the charge.
4. A batch owns decisions, not processing. Each document keeps its own run, quota and failure; the batch only makes a decision in one apply to the others.

---

Entity-relationship overview

erDiagram
    Batch ||--o{ Document : groups
    Batch ||--o{ BatchRule : "carries decisions across files"
    Batch ||--o{ BatchExport : "one archive run"
    Document ||--o{ Document : "Attachments: parentDocumentId"
    Document ||--o{ Redaction : "suggested / accepted / rejected"
    Document ||--o{ GlobalRule : "rules applied here"
    BatchRule ||--o{ GlobalRule : "copies (batchRuleId)"
    OwnerRule ||--o{ GlobalRule : "copies (ownerRuleId, unenforced)"
    Document ||--o{ ProcessingEvent : "run history"
    Document ||--o{ ExportArtifact : "generated outputs"
    Document ||--o{ AiUsage : "tokens (unenforced documentId)"

    Document {
        string id PK
        string status "queued … ready | expanded | failed | expired"
        string encryptionFormat "v0 | v1"
        json normalizedIndex "page byte offsets"
        string batchId FK
        string parentDocumentId FK
    }
    Redaction {
        string id PK
        string status "the exporter reads accepted only"
        string method "mask | pseudonymize | tokenize | encrypt"
        string ruleId "set when a rule made it"
    }
    OwnerRule {
        string id PK
        string sealedPattern "master-key sealed"
        datetime expiresAt "30 days after last use"
    }
    Setting {
        string key PK
        json value "rate-limit CLI; sealed ChatGPT sign-in"
    }
    RateLimit {
        string key PK
        float tokens "token bucket"
    }
    UsageRecord {
        string fingerprint
        date date "one row per identity per UTC day"
    }

Loading

Document is the hub. Most tables hang off documentId and cascade on delete, so a purged document takes its redactions, rules, events and exports with it. Batch sits to the side. OwnerRule, Setting, RateLimit, UsageRecord and AiUsage do not cascade from a document.

---

The models

Model	What it holds	Notable since v1.1
Document	The hub: status, kind, MIME, size, page count, preset, error / errorCode, the blob keys (uploadBlobKey, sourceBlobKey, normalizedBlobKey, processedBlobKey), checksums, the wrapped data key, workflowRunId, owner and quota keys, batch and parent links, metadata	encryptionFormat (1.6.0), normalizedIndex (1.7.0), status expanded
Batch	a group of documents sharing a review pass and a download
BatchRule	a decision carried across a batch, including documents still processing	kind, matchCase, wholeWord, enabled (1.9.0)
GlobalRule	a rule applied to one document, created directly or copied from a BatchRule / OwnerRule. Every redaction it makes carries its id.	kind, matchCase, wholeWord, ownerRuleId (1.9.0)
OwnerRule	a global rule: "redact this in everything I upload". Pattern sealed, expires 30 days after last use, at most 200 per owner	new in 1.9.0
BatchExport	one durable batch-export run: status, totals, per-document documents JSON, options (incl. output), cancelRequested
Redaction	one suggestion or decision: source (ai / user / rule), type, category, confidence, status, method, page / offsets / box / cell, text, reason, ruleId
ProcessingEvent	append-only run history: ids, stages, counts, durations, never content
ExportArtifact	a generated output: blob key, checksum, MIME, size, applied/verified counts, metadata-sanitised and label flags, variant, reportBlobKey / reportChecksum, vaultBlobKey / vaultChecksum (batch exports only)
UsageRecord	daily quota per identity per UTC day: pdfPages, docxPages, xlsxCells, textPages, pptxSlides, emailKilobytes, images, uploads	assistantTokens (1.9.0)
AiUsage	per model call: task, model, input/output tokens, duration, chunks. What the spend cap and the usage panel are computed from. task: "assistant" for Hush.
Setting	runtime key/value: rate-limit overrides written by the CLI (effective without restart); the sealed ChatGPT sign-in (ai.login.openai)	sign-in row (1.8.0)
RateLimit	token buckets (tokens is a float because refill is continuous), pruned by the sweep

Document.status

queued → extracting → normalizing → analyzing → ready, or failed from any stage. expanded is the terminal status of a container (a mailbox that became a batch: neither ready nor failed). expired is set by the sweep before purging. A document queued with no workflowRunId is waiting for admission.

Document.metadata (known keys)

Key	Meaning
quotaCharged	the idempotent charge mark, set in the same transaction as the UsageRecord increment
ocrPages	PDF pages read by OCR
imageClass	document / photograph / mixed
preset	mirrored from the column
width, height, format	image dimensions and format

---

The Document → Document self-relation ("Attachments")

An attachment is a document in its own right (its own run, extraction, review and export) while still being part of its message. So it is a child Document:

• parentDocumentId: the message, onDelete: Cascade (purging the message takes its attachments);
• sourcePartPath: the MIME part path (0.3), not the filename. Filenames collide and get redacted; only the path is unique and stable.
• @@unique([parentDocumentId, sourcePartPath]): one child per part, enforced by the database. Expansion is retried like any step and re-parses from scratch, and this constraint is what makes a second pass converge instead of duplicating.

A mailbox is the same shape one level up: its messages are children keyed by message index, and the mailbox ends expanded. The batch download's folder tree is a pure function of these two columns. See Batches and Mailboxes.

---

How rules are stored

Scope	Decision row	Per-document copy	Redactions
Document	none	GlobalRule	Redaction.ruleId → the GlobalRule
Batch	BatchRule	GlobalRule with batchRuleId	same
Global	OwnerRule (sealed)	GlobalRule with ownerRuleId (plaintext, dies with the document)	same

normalizedPattern folds a literal to lower case and single spaces, while a RegEx is kept exactly (folding \D to \d would change it). A carry skips a decision whose copy already exists, so a retried processing step cannot apply it twice. Every write that materialises a decision is planned first and then written in one transaction.

---

Migrations

Migration	Adds
20260904081104_db_init	the base schema
20260904084611_add_settings	Setting
20260905000000_add_document_error_code	Document.errorCode
20260905090000_add_export_report	export reports
20260905100000_add_batches	Batch, BatchRule
20260905110000_add_document_preset	Document.preset
20260905120000_add_format_quotas	per-format UsageRecord columns
20260905130000_add_batch_exports	BatchExport
20260905140000_add_attachment_provenance	parentDocumentId, sourcePartPath
20260906100000_add_redaction_method	Redaction.method
20260906110000_add_export_variant	ExportArtifact.variant
20260906120000_add_export_vault	batch vault keys and checksums
20260923120000_add_encryption_format	Document.encryptionFormat (1.6.0)
20260927120000_add_normalized_index	Document.normalizedIndex (1.7.0)
20260927140000_add_pattern_rules	rule kinds/options, enabled, ownerRuleId, OwnerRule (1.9.0)
20260927160000_add_assistant_quota	UsageRecord.assistantTokens (1.9.0)

pnpm db:migrate         # create and apply (development)
pnpm db:migrate:deploy  # apply existing (CI, production, upgrades)
pnpm db:push            # scratch work only

CI applies every migration to an empty database and fails if the schema and the migrations disagree.

---

Testing against a real database

TEST_DATABASE_URL points pnpm test:db at a throwaway database (never DATABASE_URL: the suites create and delete rows). Properties of the row are tested there rather than with fakes, which would agree with whatever the code did: expiry ordering (storage first, row last, only if storage went), quota atomicity and charge-once under retries, attachment and mailbox expansion idempotence, and the batch download end to end.

---

See also

• API Reference · Processing Workflow · Storage and Encryption · Search and Rules