query-orchestration/docs/ai.generated/04-data-architecture.md

Data Architecture

Database Schema Overview

The system uses PostgreSQL 17.2 with a schema designed for multi-tenant document processing, field extraction, and versioning. The schema is managed through migrations and uses SQLC for type-safe Go integration.

Note: Query/result tables and text-extraction tables were removed in migrations 00000000000119 and 00000000000120. Any references to those tables in older sections should be treated as legacy context, not active runtime architecture.

Entity Relationship Diagram

erDiagram
    clients ||--o{ client_metadata_schemas : "owns"
    client_metadata_schemas ||--o{ documents : "bound to"
    documents ||--o{ document_custom_metadata : "has metadata"

    clients ||--o{ documents : "owns"
    clients ||--o{ batch_uploads : "owns"
    clients ||--|| clientCanSync : "has sync status"
    clients ||--o{ documentUploads : "uploads"
    clients ||--o{ folders : "organizes"
    clients ||--o{ collectorVersions : "has versions"
    clients ||--|| collectorActiveVersions : "has active version"
    clients ||--o{ collectorQueries : "configures"
    clients ||--o{ collectorMinCleanVersions : "sets min clean version"
    clients ||--o{ collectorMinTextVersions : "sets min text version"

    batch_uploads ||--o{ documents : "contains"
    batch_uploads ||--o{ documentUploads : "tracks uploads"
    batch_uploads ||--o{ batch_document_outcomes : "tracks outcomes"
    batch_uploads ||--o{ batch_folder_candidates : "tracks cleanup candidates"

    folders ||--o{ documents : "contains"
    folders ||--o{ folders : "has children"
    folders ||--o{ documentUploads : "targets"

    documents ||--o{ documentEntries : "has entries"
    documents ||--o{ documentCleans : "has clean versions"
    documents ||--o{ documentLabels : "has labels"
    documents ||--o{ documentFieldExtractions : "has field extractions"

    documentEntries }o--|| documents : "references"

    documentCleans }o--|| documents : "cleans"
    documentCleans ||--o{ documentCleanEntries : "has entries"
    documentCleans ||--o{ documentTextExtractions : "produces text"

    documentTextExtractions }o--|| documentCleans : "from"
    documentTextExtractions ||--o{ documentTextExtractionEntries : "has entries"
    documentTextExtractions ||--o{ results : "generates"

    labels ||--o{ documentLabels : "applied to documents"

    documentFieldExtractions ||--o{ documentFieldExtractionVersions : "has versions"
    documentFieldExtractions ||--o{ documentFieldExtractionArrayFields : "has array fields"

    queries ||--o{ queryVersions : "has versions"
    queries ||--|| queryActiveVersions : "has active version"
    queries ||--o{ requiredQueries : "requires other queries"
    queries ||--o{ queryConfigs : "has configs"
    queries ||--o{ results : "produces"
    queries ||--o{ collectorQueries : "used by collectors"

    queryVersions ||--|| queryActiveVersions : "tracks active"

    results }o--|| documentTextExtractions : "from text"
    results }o--|| queries : "from query"
    results ||--o{ resultDependencies : "depends on"
    results ||--o{ resultDependencies : "required by"

    collectorVersions ||--|| collectorActiveVersions : "tracks active"
    collectorQueries }o--|| queries : "references"

    eulaVersions ||--o{ eulaAgreements : "has agreements"

    uiSettings {
        uuid id PK
        varchar namespace
        varchar key
        jsonb value
        boolean isDeleted
        timestamptz createdAt
        timestamptz updatedAt
    }

    client_metadata_schemas {
        uuid id PK
        varchar client_id FK
        text name
        text description
        jsonb schema_def
        int version
        schema_status_type status
        timestamptz created_at
        varchar created_by
    }

    document_custom_metadata {
        uuid id PK
        uuid document_id FK
        jsonb metadata
        bigint version
        timestamptz created_at
        varchar created_by
    }

    clients {
        varchar clientId PK
        text name UK
    }

    clientCanSync {
        uuid syncId PK
        varchar clientId FK
        boolean canSync
    }

    folders {
        uuid id PK
        text path
        uuid parentId FK
        varchar clientId FK
        timestamp createdAt
        varchar createdBy
    }

    documents {
        uuid id PK
        varchar clientId FK
        text hash
        text filename
        uuid batch_id FK
        uuid folderId FK
        text originalPath
        uuid custom_schema_id FK
    }

    batch_uploads {
        uuid id PK
        varchar client_id FK
        text original_filename
        integer total_documents
        integer processed_documents
        integer failed_documents
        integer invalid_type_documents
        batch_status status
        integer progress_percent
        jsonb failed_filenames
        timestamp created_at
        timestamp completed_at
        text archive_bucket
        text archive_key
        bigint file_size_bytes
    }

    documentUploads {
        uuid id PK
        text bucket
        text key
        varchar clientId FK
        smallint part
        timestamp createdAt
        text filename
        uuid batch_id FK
        uuid folder_id FK
    }

    documentEntries {
        uuid id PK
        uuid documentId FK
        text bucket
        text key
    }

    documentCleans {
        uuid id PK
        uuid documentId FK
        text bucket
        text key
        text hash
        cleanMimeType mimetype
        cleanFailType fail
    }

    documentCleanEntries {
        uuid id PK
        uuid cleanId FK
        bigint version
    }

    documentTextExtractions {
        uuid id PK
        uuid cleanId FK
        text bucket
        text key
        text hash
        timestamp createdAt
        smallint part
    }

    documentTextExtractionEntries {
        uuid id PK
        uuid textId FK
        bigint version
    }

    labels {
        text label PK
        text description
    }

    documentLabels {
        uuid id PK
        uuid documentId FK
        text label FK
        timestamp appliedAt
        varchar appliedBy
    }

    documentFieldExtractions {
        uuid id PK
        uuid documentId FK
        text fileName
        text contractTitle
        int aareteDerivedAmendmentNum
        text clientName
        text payerName
        date aareteDerivedEffectiveDt
        date aareteDerivedTerminationDt
        boolean autoRenewalInd
        timestamp createdAt
        varchar createdBy
    }

    documentFieldExtractionVersions {
        uuid id PK
        uuid fieldExtractionId FK
        bigint version
        varchar createdBy
        timestamp createdAt
    }

    documentFieldExtractionArrayFields {
        uuid id PK
        uuid fieldExtractionId FK
        smallint arrayIndex
        text exhibitTitle
        text reimbProvTin
        date reimbEffectiveDt
        text aareteDerivedClaimTypeCd
        numeric reimbPctRate
        numeric reimbFeeRate
    }

    queries {
        uuid queryId PK
        queryType queryType
    }

    queryVersions {
        uuid queryId FK
        int versionId "PK composite"
        timestamp addedAt
    }

    queryActiveVersions {
        uuid activeVersionEntryId PK
        uuid queryId FK
        int versionId FK
    }

    requiredQueries {
        uuid requiredQueryEntryId PK
        uuid queryId FK
        uuid requiredQueryId FK
        int addedVersion FK
        int removedVersion FK
    }

    queryConfigs {
        uuid configId PK
        uuid queryId FK
        jsonb config
        int addedVersion FK
        int removedVersion FK
    }

    results {
        uuid id PK
        uuid textEntryId FK
        uuid queryId FK
        text value
        int queryVersion FK
    }

    resultDependencies {
        uuid resultId FK
        uuid requiredResultId FK
    }

    collectorVersions {
        varchar clientId FK
        int id "PK composite"
        timestamp addedAt
    }

    collectorActiveVersions {
        uuid id PK
        varchar clientId FK
        int versionId FK
    }

    collectorQueries {
        uuid id PK
        varchar clientId FK
        varchar name
        uuid queryId FK
        int addedVersion FK
        int removedVersion FK
    }

    collectorMinCleanVersions {
        uuid id PK
        varchar clientId FK
        bigint versionId
        int addedVersion FK
        int removedVersion FK
    }

    collectorMinTextVersions {
        uuid id PK
        varchar clientId FK
        bigint versionId
        int addedVersion FK
        int removedVersion FK
    }

    eulaVersions {
        uuid id PK
        varchar version UK
        text title
        text content
        timestamptz effectiveDate
        timestamptz createdAt
        varchar createdBy
        boolean isCurrent
        timestamptz activatedAt
        varchar activatedBy
    }

    eulaAgreements {
        uuid id PK
        varchar cognitoSubjectId
        varchar userEmail
        uuid eulaVersionId FK
        timestamptz agreedAt
        varchar agreedFromIp
    }

    uiSettings {
        uuid id PK
        varchar namespace
        varchar key
        jsonb value
        boolean isDeleted
        timestamptz createdAt
        timestamptz updatedAt
    }

Core Entities

Clients (clients)

Central entity representing organizations or tenants in the system.

CREATE TABLE clients (
    clientId varchar(255) PRIMARY KEY,
    name TEXT NOT NULL,
    UNIQUE (name)
);

Key Characteristics:

• String-based client identifiers for human readability
• Multi-tenant isolation through client-scoped data
• Central reference point for all client-owned entities
• Unique constraint on name prevents duplicate client names

Client Sync Status (clientCanSync)

Tracks whether a client is allowed to synchronize documents.

CREATE TABLE clientCanSync (
    syncId uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    clientId varchar(255) NOT NULL,
    canSync boolean NOT NULL,
    FOREIGN KEY (clientId) REFERENCES clients(clientId)
);

Key Features:

• Historical tracking of sync permission changes
• Multiple entries per client for audit trail
• Most recent entry determines current sync status

Documents (documents)

Represents uploaded files requiring processing.

CREATE TABLE documents (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    clientId varchar(255) NOT NULL,
    hash TEXT NOT NULL,
    filename TEXT,
    batch_id uuid,
    folderId uuid,
    originalPath TEXT,
    custom_schema_id uuid NULL,
    UNIQUE(clientId, hash),
    FOREIGN KEY (clientId) REFERENCES clients(clientId),
    FOREIGN KEY (batch_id) REFERENCES batch_uploads(id),
    FOREIGN KEY (folderId) REFERENCES folders(id),
    CONSTRAINT fk_documents_custom_schema_same_client
        FOREIGN KEY (custom_schema_id, clientId)
        REFERENCES client_metadata_schemas(id, client_id)
);

-- Indexes
CREATE INDEX idx_documents_filename ON documents(filename);
CREATE INDEX idx_documents_batch_id ON documents(batch_id);
CREATE INDEX idx_documents_folderid ON documents(folderId);
CREATE INDEX idx_documents_custom_schema_id ON documents(custom_schema_id);

Key Features:

• UUID v7 primary keys for time-ordered insertion
• Client isolation with foreign key constraints
• Hash-based deduplication within client scope (per client)
• Filename preservation for folder path support (added in migration 105)
• Optional batch association for grouped uploads (added in migration 103)
• Virtual folder organization via folderId (added in migration 111)
• Immutable originalPath preserving exact upload path (added in migration 111)
• Indexed on filename, batch_id, and folderId for efficient queries
• custom_schema_id (nullable UUID, added in migration 128): when non-null, opts the document into the custom metadata system; protected by a composite FK ensuring the schema belongs to the same client

Batch Uploads (batch_uploads)

Tracks batch document upload operations with ZIP archive processing.

CREATE TYPE batch_status AS ENUM ('processing', 'completed', 'failed', 'cancelled');

CREATE TABLE batch_uploads (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    client_id varchar(255) NOT NULL,
    original_filename TEXT NOT NULL,
    total_documents integer NOT NULL,
    processed_documents integer NOT NULL DEFAULT 0,
    failed_documents integer NOT NULL DEFAULT 0,
    invalid_type_documents integer NOT NULL DEFAULT 0,
    status batch_status NOT NULL DEFAULT 'processing',
    progress_percent integer NOT NULL DEFAULT 0,
    failed_filenames jsonb DEFAULT '[]'::jsonb,
    created_at timestamp NOT NULL DEFAULT NOW(),
    completed_at timestamp,
    archive_bucket text,
    archive_key text,
    file_size_bytes bigint,
    FOREIGN KEY (client_id) REFERENCES clients(clientId),
    CONSTRAINT batch_storage_consistent CHECK (
        (archive_bucket IS NULL AND archive_key IS NULL AND file_size_bytes IS NULL) OR
        (archive_bucket IS NOT NULL AND archive_key IS NOT NULL)
    )
);

-- Indexes
CREATE INDEX idx_batch_uploads_client_id ON batch_uploads(client_id);
CREATE INDEX idx_batch_uploads_status ON batch_uploads(status);

Batch Processing Features:

• UUID v7 for time-ordered batch tracking
• Comprehensive status tracking with ENUM types (batch_status)
• Progress counters for processed/failed/invalid documents
• Progress percentage for UI updates
• Failed filename tracking as JSONB array for flexible querying
• Original filename preservation for display
• Temporal tracking with creation and completion timestamps
• Storage metadata tracking (archive bucket/key and file size)
• Storage consistency constraint ensures bucket and key are both set or both null
• folder_cleanup_completed_at records when backend folder cleanup has finalized for the batch
• Indexed by client_id and status for efficient queries
• Note: Uses snake_case naming convention (unlike other tables)

Batch Document Outcomes (batch_document_outcomes)

Tracks per-file extraction and pipeline processing outcomes for batch uploads. One row per file in the ZIP, updated in place as the document progresses through the processing pipeline (init, sync, clean runners).

CREATE TYPE batch_document_outcome AS ENUM (
    'submitted', 'duplicate', 'failed_open', 'failed_read',
    'failed_s3_upload', 'failed_upload', 'invalid_type',
    'init_complete', 'init_duplicate', 'sync_complete',
    'sync_skipped', 'clean_passed', 'clean_failed'
);

CREATE TABLE batch_document_outcomes (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    batch_id uuid NOT NULL,
    filename text NOT NULL,
    outcome batch_document_outcome NOT NULL,
    error_detail text,
    document_id uuid,
    updated_at timestamp NOT NULL DEFAULT NOW(),
    created_at timestamp NOT NULL DEFAULT NOW(),
    FOREIGN KEY (batch_id) REFERENCES batch_uploads(id),
    FOREIGN KEY (document_id) REFERENCES documents(id),
    UNIQUE (batch_id, filename)
);

-- Indexes
CREATE INDEX idx_batch_doc_outcomes_batch_id ON batch_document_outcomes(batch_id);
CREATE INDEX idx_batch_doc_outcomes_document_id ON batch_document_outcomes(document_id);

Batch Document Outcome Features:

• Single source of truth for per-file batch status (extraction outcomes and pipeline progress)
• ENUM type covers both extraction-time outcomes (submitted, duplicate, failed_, invalid_type) and pipeline outcomes (init_, sync_, clean_)
• Unique constraint on (batch_id, filename) enables idempotent upserts for batch retries
• Foreign key to documents table links outcome rows to their document records once created
• Rows for files that never became documents (extraction failures) have null document_id
• LEFT JOIN to currentCleanEntries provides specific clean failure reasons when queried
• Indexed by batch_id for efficient batch detail lookups and by document_id for pipeline updates
• Added in migration 124

Batch Folder Candidates (batch_folder_candidates)

Tracks folders touched by legacy ZIP batch extraction so the backend can remove empty auto-created folders after all file outcomes are terminal.

CREATE TABLE batch_folder_candidates (
    batch_id uuid NOT NULL,
    folder_id uuid NOT NULL,
    path text NOT NULL,
    existed_before boolean NOT NULL,
    created_at timestamp NOT NULL DEFAULT NOW(),
    PRIMARY KEY (batch_id, folder_id),
    FOREIGN KEY (batch_id) REFERENCES batch_uploads(id) ON DELETE CASCADE,
    FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
);

Batch Folder Cleanup Features:

• Records each non-root folder segment touched by a batch upload
• Preserves existed_before=false once any batch creates the folder, enabling idempotent retries
• Cleanup finalizes only after terminal batch outcome evidence exists; zero-outcome failed batches stay cleanup-pending
• Safe deletion never removes /, pre-existing folders, renamed folders, folders with documents/uploads/children/chatbot scopes, or folders still needed by another pending batch
• Added in migration 132

Folders (folders)

Virtual folder hierarchy for document organization. Folders are database-only entities with no direct relationship to S3 storage locations.

CREATE TABLE folders (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    path text NOT NULL,
    parentId uuid,
    clientId varchar(255) NOT NULL,
    createdAt timestamp NOT NULL DEFAULT NOW(),
    createdBy varchar(255) NOT NULL,
    FOREIGN KEY (parentId) REFERENCES folders(id),
    FOREIGN KEY (clientId) REFERENCES clients(clientId),
    UNIQUE(clientId, path),
    CHECK (id != parentId)
);

-- Indexes
CREATE INDEX idx_folders_parentid ON folders(parentId);
CREATE INDEX idx_folders_clientid ON folders(clientId);
CREATE INDEX idx_folders_path ON folders(path);

Key Features:

• Self-referential hierarchy via parentId for nested folders
• Client isolation with unique constraint on (clientId, path)
• Self-reference prevention via check constraint (id != parentId)
• Path column stores virtual folder path (e.g., "/contracts/2025")
• Folders can be renamed without affecting S3 storage locations
• Audit tracking via createdAt and createdBy
• Note: Added in migration 109

Labels System

Labels (labels)

Lookup table for document processing status labels.

CREATE TABLE labels (
    label text PRIMARY KEY,
    description text NOT NULL
);

-- Seeded initial values
INSERT INTO labels (label, description) VALUES
    ('Ingested', 'Document has been ingested into the system'),
    ('OCR_Processed', 'OCR text extraction completed'),
    ('GenAI_Processed', 'GenAI processing completed'),
    ('Doczy_AI_Completed', 'Doczy.AI processing completed'),
    ('Dashboard_Ready', 'Document ready for dashboard display');

Key Features:

• Predefined processing status labels
• Primary key on label text for direct referencing
• Description provides human-readable explanation

Document Labels (documentLabels)

Junction table tracking label application to documents.

CREATE TABLE documentLabels (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    documentId uuid NOT NULL,
    label text NOT NULL,
    appliedAt timestamp NOT NULL DEFAULT NOW(),
    appliedBy varchar(255) NOT NULL,
    FOREIGN KEY (documentId) REFERENCES documents(id) ON DELETE CASCADE,
    FOREIGN KEY (label) REFERENCES labels(label)
);

-- Indexes
CREATE INDEX idx_documentlabels_documentid ON documentLabels(documentId);
CREATE INDEX idx_documentlabels_label ON documentLabels(label);
CREATE INDEX idx_documentlabels_appliedat ON documentLabels(appliedAt);
CREATE INDEX idx_documentlabels_document_label_time ON documentLabels(documentId, label, appliedAt DESC);

Key Features:

• Tracks which labels are applied to which documents
• Audit tracking via appliedAt and appliedBy
• Cascade delete removes labels when document is deleted
• Composite index optimizes queries for most recent label application
• Multiple applications of same label are allowed (historical tracking)
• Note: Added in migration 110

EULA System

The EULA (End User License Agreement) system tracks EULA versions and user consent. Added in migration 121.

EULA Versions (eulaVersions)

Stores EULA version records with content and activation tracking.

CREATE TABLE eulaVersions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    version VARCHAR(50) NOT NULL UNIQUE,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    effectiveDate TIMESTAMPTZ NOT NULL,
    createdAt TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    createdBy VARCHAR(320) NOT NULL,
    isCurrent BOOLEAN NOT NULL DEFAULT FALSE,
    activatedAt TIMESTAMPTZ,
    activatedBy VARCHAR(320)
);

-- Partial unique index enforces only ONE row can have isCurrent=true at any time
CREATE UNIQUE INDEX idx_eulaversions_one_current ON eulaVersions(isCurrent) WHERE isCurrent = TRUE;

-- Indexes for efficient queries
CREATE INDEX idx_eulaversions_effectivedate ON eulaVersions(effectiveDate DESC);
CREATE INDEX idx_eulaversions_iscurrent ON eulaVersions(isCurrent);

Key Features:

• UUID v7 primary keys for time-ordered insertion
• Unique version string constraint prevents duplicate versions
• Content stored in Markdown format for flexible rendering
• Partial unique index ensures only one version can be isCurrent=true at any time
• Activation tracking with timestamp and admin email
• Effective date supports scheduling future EULA versions

EULA Agreements (eulaAgreements)

Tracks user consent with audit metadata.

CREATE TABLE eulaAgreements (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    cognitoSubjectId VARCHAR(256) NOT NULL,
    userEmail VARCHAR(320) NOT NULL,
    eulaVersionId uuid NOT NULL REFERENCES eulaVersions(id) ON DELETE RESTRICT,
    agreedAt TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    agreedFromIp VARCHAR(45) NOT NULL,
    UNIQUE(cognitoSubjectId, eulaVersionId)
);

-- Indexes for efficient queries
CREATE INDEX idx_eulaagreements_subjectid ON eulaAgreements(cognitoSubjectId);
CREATE INDEX idx_eulaagreements_versionid ON eulaAgreements(eulaVersionId);
CREATE INDEX idx_eulaagreements_version_time ON eulaAgreements(eulaVersionId, agreedAt DESC);
CREATE INDEX idx_eulaagreements_user_time ON eulaAgreements(cognitoSubjectId, agreedAt DESC);

Key Features:

• Links users (by Cognito subject ID) to EULA versions they've agreed to
• Unique constraint on (cognitoSubjectId, eulaVersionId) prevents duplicate agreements
• Stores user email at time of agreement for audit purposes
• IP address tracking for legal compliance
• ON DELETE RESTRICT prevents deleting EULA versions that have agreements
• Composite indexes optimize common query patterns (agreements by version, user history)

UI Settings (uiSettings)

Generic key-value store scoped by namespace for UI state (preferences, feature flags, saved filters). Added in migration 123.

CREATE TABLE "uiSettings" (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    namespace VARCHAR(255) NOT NULL,
    key VARCHAR(255) NOT NULL,
    value JSONB NOT NULL,
    "isDeleted" BOOLEAN NOT NULL DEFAULT FALSE,
    "createdAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updatedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(namespace, key)
);

-- Index for list/filter by namespace; UNIQUE(namespace, key) already creates an index on (namespace, key)
CREATE INDEX idx_uisettings_namespace ON "uiSettings"(namespace);

Key Features:

• Namespace scopes data (e.g. demo-dashboard:{userId}, feature-flags:global, saved-filters:{clientId})
• One setting per (namespace, key); value is JSONB for flexible structure
• Soft delete via isDeleted; list/get exclude soft-deleted rows
• UNIQUE(namespace, key) enforces one record per scope+key and provides an implicit index for lookups by (namespace, key)
• Single explicit index on namespace for listing/filtering by namespace

Custom Schema System

The custom schema system stores client-scoped JSON Schema definitions and per-document validated metadata blobs. Added in migrations 127–130.

Client Metadata Schemas (client_metadata_schemas)

Stores versioned JSON Schema definitions scoped to a client. Each unique (client_id, name) pair forms a schema lineage; versions within a lineage are numbered sequentially starting at 1.

CREATE TYPE schema_status_type AS ENUM ('active', 'superseded', 'retired');

CREATE TABLE client_metadata_schemas (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    client_id varchar(255) NOT NULL,
    name text NOT NULL,
    description text,
    schema_def jsonb NOT NULL,
    version integer NOT NULL DEFAULT 1,
    status schema_status_type NOT NULL DEFAULT 'active',
    created_at timestamptz NOT NULL DEFAULT NOW(),
    created_by varchar(255) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(clientId) ON DELETE CASCADE,
    CONSTRAINT uq_client_schema_name_version UNIQUE (client_id, name, version),
    CONSTRAINT uq_cms_id_client UNIQUE (id, client_id),
    CONSTRAINT chk_schema_def_size CHECK (pg_column_size(schema_def) <= 70000)
);

-- Indexes
CREATE INDEX idx_cms_client_id ON client_metadata_schemas(client_id);
CREATE INDEX idx_cms_client_name ON client_metadata_schemas(client_id, name);
CREATE INDEX idx_cms_client_status ON client_metadata_schemas(client_id, status);

Key Features:

• UUID v7 primary keys for time-ordered insertion
• FOREIGN KEY (client_id) ... ON DELETE CASCADE — deleting a client removes all its schemas
• CONSTRAINT uq_client_schema_name_version — no two rows can share the same (client_id, name, version) triple
• CONSTRAINT uq_cms_id_client — required as the target of the composite FK on documents (see composite FK section below)
• CONSTRAINT chk_schema_def_size — enforces the 64 KB maximum at the database layer (app layer enforces 65,536 bytes via MaxSchemaDefinitionBytes)
• created_by stores the JWT sub claim (Cognito subject UUID), not an email address
• Status lifecycle: active → superseded (when a new version is created) or active → retired (via DELETE endpoint)

Status Semantics:

Status	Meaning
active	Current version; documents can be assigned to this schema; only one active row per (client_id, name) is allowed by application invariants
superseded	An older version of the same name; documents bound to it retain the binding; no new documents can be assigned
retired	Explicitly removed; no documents are bound (enforced by DELETE endpoint returning 409 if any are)

Document Custom Metadata (document_custom_metadata)

Stores versioned JSONB metadata blobs for documents that have a custom schema assigned. Each write appends a new version row; the service always reads version rows ordered descending to get the current value.

CREATE TABLE document_custom_metadata (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    document_id uuid NOT NULL,
    metadata jsonb NOT NULL,
    version bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT NOW(),
    created_by varchar(255) NOT NULL,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    CONSTRAINT uq_doc_custom_metadata_version UNIQUE (document_id, version)
);

-- Index
CREATE INDEX idx_dcm_doc_version_desc ON document_custom_metadata(document_id, version DESC);

Key Features:

• UUID v7 primary keys
• FOREIGN KEY (document_id) ... ON DELETE CASCADE — deleting a document removes all its metadata rows automatically; no application-level cleanup required
• CONSTRAINT uq_doc_custom_metadata_version — prevents duplicate version numbers per document
• idx_dcm_doc_version_desc — optimizes the common ORDER BY version DESC LIMIT 1 query pattern for fetching the latest version
• No schema_id column — documents.custom_schema_id is the single source of truth for schema binding; metadata rows carry no independent schema reference
• created_by stores the JWT sub claim (Cognito subject UUID)
• Maximum payload enforced at application layer: 1 MB (MaxMetadataPayloadBytes = 1048576)

documents.custom_schema_id Column

Migration 128 adds a nullable UUID column custom_schema_id to the documents table:

ALTER TABLE documents ADD COLUMN custom_schema_id uuid NULL;
CREATE INDEX idx_documents_custom_schema_id ON documents(custom_schema_id);

When custom_schema_id is NULL, the document uses the legacy field extraction system (or has no structured metadata at all). When non-null, the document is opted into the custom metadata system and mutual exclusivity is enforced by triggers and service-layer locks.

Composite Foreign Key — Same-Client Schema Binding

A composite foreign key on documents prevents a document from being bound to a schema owned by a different client. This replaces the trigger-based approach used in earlier versions.

ALTER TABLE documents
    ADD CONSTRAINT fk_documents_custom_schema_same_client
    FOREIGN KEY (custom_schema_id, clientId)
    REFERENCES client_metadata_schemas(id, client_id);

How it works:

• client_metadata_schemas has CONSTRAINT uq_cms_id_client UNIQUE (id, client_id), which is required for the composite FK target.
• Any attempt to set documents.custom_schema_id to a schema row whose client_id does not match the document's clientId fails at the database layer with a FK violation, before any application code runs.
• This enforcement fires on direct SQL updates as well, providing defense-in-depth beyond the application layer.

Schema Invariant Triggers

Two triggers enforce the mutual exclusivity invariant between the custom metadata system and the legacy field extraction system. Added in migration 130.

trg_prevent_schema_reassignment (Trigger 1)

BEFORE UPDATE OF custom_schema_id ON documents
FOR EACH ROW EXECUTE FUNCTION trg_prevent_schema_reassignment()

Fires whenever custom_schema_id is changed on an existing document row. The trigger reads the document's current state and raises an exception if:

• The document already has custom metadata rows in document_custom_metadata, or
• The document already has legacy field extraction rows in documentFieldExtractions.

A schema reassignment is only allowed when neither system has recorded any data for the document. This ensures that once a document has committed to either extraction system, its schema binding cannot change without an explicit reset (see POST /super-admin/documents/{id}/reset-metadata).

trg_prevent_legacy_extraction_on_custom_document (Trigger 2)

BEFORE INSERT ON documentFieldExtractions
FOR EACH ROW EXECUTE FUNCTION trg_prevent_legacy_extraction_on_custom_document()

Fires on every INSERT into documentFieldExtractions. The function reads documents.custom_schema_id using SELECT ... FOR SHARE on the parent row, and raises a check_violation (SQLSTATE 23514) if the column is non-null. This prevents legacy field extractions from being added to documents already opted into the custom schema system.

The FOR SHARE lock in the trigger function is the symmetric counterpart to the FOR UPDATE lock taken by AssignSchema in the service layer. This ensures that a concurrent race between AssignSchema and CreateFieldExtraction cannot leave the document in an inconsistent state where both systems have data.

Defense-in-depth summary:

• Service layer: FOR UPDATE parent-row lock acquired before any writes in both AssignSchema and CreateFieldExtraction
• Trigger 1: blocks schema reassignment when data exists
• Trigger 2: blocks legacy extraction inserts on schema-bound documents
• Composite FK: blocks cross-client schema bindings structurally

Queries (queries)

Defines data extraction logic with type-specific implementations.

CREATE TABLE queries (
    queryId uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    queryType querytype NOT NULL
);

CREATE TYPE querytype AS ENUM ('context_full', 'json_extractor');

Query Types:

• context_full: Returns complete document context
• json_extractor: Extracts specific JSON paths from structured data

Results (results)

Stores extracted data from document processing.

CREATE TABLE results (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    textEntryId uuid NOT NULL REFERENCES documentTextExtractions(id),
    queryId uuid NOT NULL REFERENCES queries(queryId),
    value text NOT NULL,
    queryVersion int4 NOT NULL
);

Chatbot System (bot_* tables, migration 131)

Migration 131 adds four tables under the bot_* namespace for the Aaria FastAPI agent service integration. New bot_* tables use snake_case columns (matching migrations 127-130); existing tables retain their camelCase columns (documents.clientId, folders.parentId, etc.) and the chatbot queries reference them unchanged.

See Chatbot Guide for the operator runbook, configuration reference, and API surface; this section covers the schema only.

bot_sessions

Owner-scoped chatbot conversations. Soft-deletable via is_deleted.

CREATE TABLE bot_sessions (
    id          uuid         NOT NULL DEFAULT uuid_generate_v7(),
    client_id   varchar(255) NOT NULL REFERENCES clients(clientId) ON DELETE CASCADE,
    created_by  varchar(255) NOT NULL,
    created_at  timestamptz  NOT NULL DEFAULT NOW(),
    updated_at  timestamptz  NOT NULL DEFAULT NOW(),
    title       text         NOT NULL DEFAULT '',
    state       jsonb        NOT NULL DEFAULT '{}'::jsonb,
    is_deleted  boolean      NOT NULL DEFAULT false,
    CONSTRAINT pk_bot_sessions PRIMARY KEY (id)
);

CREATE INDEX idx_bot_sessions_client_user_active
    ON bot_sessions (client_id, created_by, updated_at DESC)
    WHERE is_deleted = false;

CREATE INDEX idx_bot_sessions_client_active
    ON bot_sessions (client_id, updated_at DESC)
    WHERE is_deleted = false;

The two partial indexes back the M2 owner-list and super-admin-list queries respectively. The WHERE is_deleted = false clause keeps soft-deleted rows out of the index so the planner satisfies the list query without a sort.

title is auto-derived from the first turn's prompt (first 120 runes, plan §6). The empty-title guard WHERE title = '' in SetBotSessionTitle prevents clobbering on retry.

state jsonb mirrors the Aaria agent's session state. Plan §7 strip rules apply on every successful turn: cached_results is removed before persist; oversized payloads (>64 KiB) preserve the prior state.

There is no last_turn column. Queries derive:

• last_seen_ordinal = COALESCE(MAX(ordinal), 0).
• last_terminal_ordinal = COALESCE(MAX(ordinal) FILTER (WHERE status <> 'in_flight'), 0).
• API lastTurn returns last_terminal_ordinal.

bot_session_documents and bot_session_folders

Per-session document/folder scope.

CREATE TABLE bot_session_documents (
    session_id  uuid NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
    document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    CONSTRAINT pk_bot_session_documents PRIMARY KEY (session_id, document_id)
);

CREATE INDEX idx_bsd_document ON bot_session_documents(document_id);

CREATE TABLE bot_session_folders (
    session_id uuid NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
    folder_id  uuid NOT NULL REFERENCES folders(id) ON DELETE CASCADE,
    CONSTRAINT pk_bot_session_folders PRIMARY KEY (session_id, folder_id)
);

CREATE INDEX idx_bsf_folder ON bot_session_folders(folder_id);

The PATCH-scope handler validates that every scoped document and folder belongs to the session client (M2). Empty scope tables mean "all documents for the client" (plan §4 fallback).

bot_turns

Append-only turn log. Status is one of five values; the partial unique index serializes concurrent submissions.

CREATE TABLE bot_turns (
    session_id          uuid        NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
    ordinal             int         NOT NULL CHECK (ordinal > 0),
    prompt              text        NOT NULL,
    completion          text,
    status              text        NOT NULL DEFAULT 'in_flight',
    attempt_id          uuid        NOT NULL,
    attempt_started_at  timestamptz NOT NULL DEFAULT NOW(),
    created_at          timestamptz NOT NULL DEFAULT NOW(),
    latency_ms          int,
    tokens_in           int,
    tokens_out          int,
    error               jsonb,
    CONSTRAINT pk_bot_turns PRIMARY KEY (session_id, ordinal),
    CONSTRAINT bot_turns_status_values CHECK (
        status IN ('in_flight', 'completed', 'errored', 'abandoned', 'session_deleted')
    ),
    CONSTRAINT bot_turns_status_fields_consistent CHECK (
        (status = 'in_flight' AND completion IS NULL AND error IS NULL)
        OR (status = 'completed' AND completion IS NOT NULL AND error IS NULL)
        OR (status IN ('errored', 'abandoned', 'session_deleted') AND completion IS NULL AND error IS NOT NULL)
        OR status NOT IN ('in_flight', 'completed', 'errored', 'abandoned', 'session_deleted')
    )
);

CREATE UNIQUE INDEX bot_turns_one_inflight_per_session
    ON bot_turns (session_id)
    WHERE status = 'in_flight';

CREATE INDEX idx_bot_turns_session_completed
    ON bot_turns (session_id, ordinal)
    WHERE status = 'completed';

CHECK guard clause. The bot_turns_status_fields_consistent CHECK has a fourth OR clause OR status NOT IN (...) that is NOT in the plan §4 literal. This is an evaluation-order guard: Postgres evaluates table-level CHECK constraints in alphabetical order by constraint name, so bot_turns_status_fields_consistent runs before bot_turns_status_values for any INSERT/UPDATE. Without the guard, an INSERT with an unknown status (e.g., 'frobnicated') would trip _status_fields_consistent first (every documented branch evaluates false), masking the cleaner _status_values rejection. With the guard, unknown statuses pass _status_fields_consistent and _status_values owns the rejection. For every documented status value the plan §4 three-clause matrix still holds verbatim.

Partial unique index bot_turns_one_inflight_per_session enforces "at most one in-flight turn per session" at the database layer. This is the primary serialization mechanism for concurrent AddTurn submits; the AddTurn algorithm catches the unique-violation pgconn.PgError (SQLSTATE 23505) and maps to ErrPriorTurnInFlight (HTTP 409). Plan §6.

attempt_id is the compare-and-set token for tx2. The visible retry count returned to clients comes from the FastAPI client's AttemptCount field, not from attempt_id.

Field Extractions System

The field extractions system stores structured data extracted from documents, supporting both single-value fields (1:1 relationship) and array fields (1:N relationship). This system was added in migrations 112-115.

Field Extractions Architecture

graph TD
    Document[documents] --> FE[documentFieldExtractions]
    FE --> FEV[documentFieldExtractionVersions]
    FE --> FEAF[documentFieldExtractionArrayFields]
    FEV --> CFE[currentFieldExtractions View]
    CFE --> CFEWAC[currentFieldExtractionsWithArrayCount View]
    FEAF --> CFEWAC

Document Field Extractions (documentFieldExtractions)

Stores single-value extracted fields from documents (19 fields total, 1:1 relationship with document).

CREATE TABLE documentFieldExtractions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    documentId uuid NOT NULL,

    -- Document identification
    fileName text,
    contractTitle text,
    aareteDerivedAmendmentNum int,

    -- Party information
    clientName text,
    payerName text,
    payerState text,
    providerState text,

    -- Tax identification numbers (stored as text to preserve leading zeros)
    filenameTin text,
    provGroupTin text,
    provGroupNpi text,
    provGroupNameFull text,
    provOtherTin text,
    provOtherNpi text,
    provOtherNameFull text,

    -- Contract dates
    aareteDerivedEffectiveDt date,
    aareteDerivedTerminationDt date,

    -- Renewal information
    autoRenewalInd boolean,
    autoRenewalTerm text,

    -- Metadata
    createdAt timestamp NOT NULL DEFAULT NOW(),
    createdBy varchar(255) NOT NULL,

    FOREIGN KEY (documentId) REFERENCES documents(id)
);

-- Indexes
CREATE INDEX idx_documentfieldextractions_documentid
    ON documentFieldExtractions(documentId);
CREATE INDEX idx_documentfieldextractions_createdby
    ON documentFieldExtractions(createdBy);
CREATE INDEX idx_documentfieldextractions_filename
    ON documentFieldExtractions(fileName);

Single-Value Fields (19 fields):

• Document identification: fileName, contractTitle, aareteDerivedAmendmentNum
• Party information: clientName, payerName, payerState, providerState
• Tax IDs (as text for leading zeros): filenameTin, provGroupTin, provGroupNpi, provGroupNameFull, provOtherTin, provOtherNpi, provOtherNameFull
• Contract dates: aareteDerivedEffectiveDt, aareteDerivedTerminationDt
• Renewal info: autoRenewalInd, autoRenewalTerm

Document Field Extraction Versions (documentFieldExtractionVersions)

Version tracking for field extractions, enabling historical queries.

CREATE TABLE documentFieldExtractionVersions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    fieldExtractionId uuid NOT NULL,
    version bigint NOT NULL,
    createdBy varchar(255) NOT NULL,
    createdAt timestamp NOT NULL DEFAULT NOW(),

    FOREIGN KEY (fieldExtractionId) REFERENCES documentFieldExtractions(id)
);

-- Indexes
CREATE INDEX idx_documentfieldextractionversions_fieldextractionid
    ON documentFieldExtractionVersions(fieldExtractionId);
CREATE INDEX idx_documentfieldextractionversions_version
    ON documentFieldExtractionVersions(version);

Key Features:

• Links field extractions to version numbers
• Audit tracking via createdBy and createdAt
• Enables temporal queries and rollback capability

Document Field Extraction Array Fields (documentFieldExtractionArrayFields)

Stores array-value extracted fields (112 fields total, 1:N relationship with field extraction).

CREATE TABLE documentFieldExtractionArrayFields (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    fieldExtractionId uuid NOT NULL,
    arrayIndex smallint NOT NULL,

    -- Array-value fields (112 fields, grouped by category)
    -- Exhibit information
    exhibitTitle text,
    exhibitPage text,

    -- Reimbursement provider information
    reimbProvTin text,
    reimbProvNpi text,
    reimbProvName text,
    reimbEffectiveDt date,
    reimbTerminationDt date,

    -- Claim and product codes
    aareteDerivedClaimTypeCd text,
    aareteDerivedProduct text,
    aareteDerivedLob text,
    aareteDerivedProgram text,
    aareteDerivedNetwork text,
    aareteDerivedProvType text,

    -- Provider taxonomy and specialty
    provTaxonomyCd text,
    provTaxonomyCdDesc text,
    provSpecialtyCd text,
    provSpecialtyCdDesc text,

    -- Service location
    placeOfServiceCd text,
    placeOfServiceCdDesc text,
    billTypeCd text,
    billTypeCdDesc text,

    -- Reimbursement rates
    reimbPctRate numeric(10,4),
    reimbFeeRate numeric(12,2),
    reimbConversionFactor numeric(12,4),

    -- Grouper information
    grouperType text,
    grouperCd text,
    grouperCdDesc text,
    grouperPctRate numeric(10,4),
    grouperBaseRate numeric(12,2),

    -- ... (96 additional fields for outliers, facility adjustments, rate escalators, stop loss)

    FOREIGN KEY (fieldExtractionId) REFERENCES documentFieldExtractions(id),
    UNIQUE (fieldExtractionId, arrayIndex),
    CHECK (arrayIndex >= 0)
);

-- Indexes
CREATE INDEX idx_documentfieldextractionarrayfields_fieldextractionid
    ON documentFieldExtractionArrayFields(fieldExtractionId);
CREATE INDEX idx_documentfieldextractionarrayfields_id_index
    ON documentFieldExtractionArrayFields(fieldExtractionId, arrayIndex);

Array Field Categories (112 fields total):

• Exhibit info: exhibitTitle, exhibitPage
• Reimbursement provider: reimbProvTin, reimbProvNpi, reimbProvName, dates
• Claim/product codes: claimTypeCd, product, LOB, program, network, provType
• Provider taxonomy: taxonomyCd, specialtyCd with descriptions
• Service location: placeOfServiceCd, billTypeCd with descriptions
• Patient demographics: patientAgeMin, patientAgeMax
• Reimbursement terms: reimbTerm, carveout info, payment logic
• Rates: reimbPctRate, reimbFeeRate, conversionFactor, thresholds
• Fee schedules: feeSchedule, feeScheduleVersion
• Service codes: CPT4, revenue, diagnosis, NDC codes with descriptions
• Grouper info: grouperType, grouperCd, rates, severity, transfer flags
• Outlier terms: thresholds, rates, exclusions
• Facility adjustments: DSH, IME, NTAP, UC, GME indicators and rates
• Rate escalator: escalator settings, max rate increases
• Stop loss: thresholds, maximums, daily rates

Key Constraints:

• Unique constraint on (fieldExtractionId, arrayIndex) prevents duplicate rows
• Check constraint ensures arrayIndex >= 0

Field Extraction Views

Current Field Extractions (currentFieldExtractions)

Returns the newest field extraction per document.

CREATE VIEW currentFieldExtractions AS
SELECT DISTINCT ON (dfe.documentId)
    dfe.id,
    dfe.documentId,
    dfe.fileName,
    dfe.contractTitle,
    -- ... all single-value fields ...
    dfev.version,
    dfev.createdBy,
    dfev.createdAt
FROM documentFieldExtractions dfe
JOIN documentFieldExtractionVersions dfev
    ON dfev.fieldExtractionId = dfe.id
ORDER BY dfe.documentId, dfev.id DESC;

Key Features:

• Uses DISTINCT ON for efficient latest-per-document queries
• Joins versions to include version metadata
• Orders by dfev.id DESC to get most recent version

Current Field Extractions With Array Count (currentFieldExtractionsWithArrayCount)

Extends currentFieldExtractions with count of array field rows.

CREATE VIEW currentFieldExtractionsWithArrayCount AS
SELECT
    cfe.*,
    COALESCE(array_counts.arraySize, 0) as arraySize
FROM currentFieldExtractions cfe
LEFT JOIN (
    SELECT fieldExtractionId, COUNT(*) as arraySize
    FROM documentFieldExtractionArrayFields
    GROUP BY fieldExtractionId
) array_counts ON array_counts.fieldExtractionId = cfe.id;

Key Features:

• Includes all fields from currentFieldExtractions
• Adds arraySize column showing count of related array field rows
• Uses COALESCE for documents with no array fields (returns 0)

Versioning System

The schema implements a comprehensive versioning system enabling temporal queries and configuration evolution.

Version Management Pattern

-- Generic versioning pattern
CREATE TABLE entity_versions (
    entityId uuid NOT NULL,
    versionId int4 NOT NULL,
    addedVersion int4 NOT NULL,
    removedVersion int4,
    -- entity-specific fields
    PRIMARY KEY (entityId, versionId)
);

CREATE TABLE entity_active_versions (
    entityId uuid PRIMARY KEY,
    versionId int4 NOT NULL
);

Temporal Validity Functions

-- Check if version is active at specific point
-- Note: Parameter order is _version, _addedVersion, _removedVersion
CREATE OR REPLACE FUNCTION isInVersion(
    _version int,
    _addedVersion int,
    _removedVersion int
)
RETURNS boolean AS $$
BEGIN
    RETURN _version IS NULL OR (
        _version >= _addedVersion AND
        (_removedVersion IS NULL OR _version < _removedVersion)
    );
END;
$$ LANGUAGE plpgsql;

Key Points:

• Returns true if _version is NULL (allowing queries without version constraints)
• Returns true if version is within the valid range [_addedVersion, _removedVersion)
• Used throughout views for temporal queries

Version-Enabled Entities

• Query Versions: queryVersions, queryActiveVersions
• Collector Versions: collectorVersions, collectorActiveVersions
• Processing Versions: Track minimum required versions for document stages

Document Processing Pipeline

Document Lifecycle Tables

Document Uploads (documentUploads)

Initial upload tracking for audit and debugging.

CREATE TABLE documentUploads (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    bucket TEXT NOT NULL,
    key TEXT NOT NULL,
    clientId varchar(255) NOT NULL,
    part unsignedsmallint NOT NULL,
    createdAt timestamp NOT NULL,
    filename TEXT,
    batch_id uuid,
    folder_id uuid,
    FOREIGN KEY (clientId) REFERENCES clients(clientId),
    FOREIGN KEY (folder_id) REFERENCES folders(id)
);

-- Indexes
CREATE INDEX idx_documentuploads_filename ON documentUploads(filename);
CREATE INDEX idx_documentuploads_batch_id ON documentUploads(batch_id);
CREATE INDEX idx_documentuploads_clientid ON documentUploads(clientId);
CREATE INDEX idx_documentuploads_folder_id ON documentUploads(folder_id);

Key Features:

• Tracks initial S3 upload location (bucket/key)
• Part number for storage distribution
• Optional batch association (added in migration 107, note: no foreign key constraint on batch_id)
• Filename tracking (added in migration 106)
• Folder association for batch uploads (added in migration 116, has foreign key constraint)
• Indexed for efficient lookups by client, filename, batch, and folder

Document Entries (documentEntries)

Core document references after deduplication and validation.

CREATE TABLE documentEntries (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    documentId uuid NOT NULL,
    bucket TEXT NOT NULL,
    key TEXT NOT NULL,
    FOREIGN KEY (documentId) REFERENCES documents(id)
);

Purpose:

• Links documents to their S3 storage locations after initial processing
• Multiple entries possible per document for version tracking

Document Cleans (documentCleans)

Document validation and format processing records.

CREATE TYPE cleanFailType AS ENUM (
    'invalid_mimetype',
    'invalid_read',
    'invalid_read_pages',
    'zero_page_count',
    'large_file',
    'small_dimensions',
    'large_dimensions',
    'small_dpi',
    'large_dpi',
    'password_protected',
    'contains_macros',
    'empty_content',
    'binary_content',
    'unsupported_encoding',
    'contains_attachments'
);

CREATE TYPE cleanMimeType AS ENUM (
    'application/pdf',
    'image/tiff',
    'image/jpeg',
    'image/png',
    'image/bmp',
    'application/docx',
    'application/xlsx',
    'application/pptx',
    'text/plain',
    'message/rfc822'
);

CREATE TABLE documentCleans (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    documentId uuid NOT NULL,
    bucket TEXT,
    key TEXT,
    hash TEXT,
    mimetype cleanMimeType,
    fail cleanFailType,
    FOREIGN KEY (documentId) REFERENCES documents(id),
    CONSTRAINT bucket_and_key_together CHECK (
        (bucket IS NULL) = (key IS NULL) AND
        (bucket IS NULL) = (mimetype IS NULL) AND
        (bucket IS NULL) = (hash IS NULL)
    ),
    CONSTRAINT location_xor_fail CHECK (
        (bucket IS NOT NULL) != (fail IS NOT NULL)
    )
);

Validation Logic:

• Either succeeds (has bucket/key/mimetype/hash) OR fails (has fail type)
• Cannot have both success and failure indicators
• Tracks specific failure reasons for troubleshooting

Supported MIME Types:

• application/pdf -- PDF documents
• image/tiff -- TIFF images (multi-frame supported)
• image/jpeg -- JPEG images
• image/png -- PNG images
• image/bmp -- BMP images
• application/docx -- Word documents
• application/xlsx -- Excel spreadsheets
• application/pptx -- PowerPoint presentations
• text/plain -- Plain text files
• message/rfc822 -- Email messages (EML)

Failure Reasons:

• invalid_mimetype -- Unrecognized or unsupported content type
• invalid_read -- Could not read document content
• invalid_read_pages -- Could not read individual pages
• zero_page_count -- Document has no pages/sheets/slides
• large_file -- File exceeds size limit (500MB for images/Office, 100MB for text/email)
• small_dimensions / large_dimensions -- Image dimensions outside acceptable range
• small_dpi / large_dpi -- Image resolution outside acceptable range (min 72 DPI)
• password_protected -- Document is encrypted or password-protected (DOCX, XLSX, PPTX, OLE2)
• contains_macros -- Document contains VBA macros (DOCX, XLSX, PPTX)
• empty_content -- Document has no meaningful content (blank images, empty text)
• binary_content -- Text file contains binary data (null bytes)
• unsupported_encoding -- Text file uses an unsupported character encoding
• contains_attachments -- Email contains MIME attachments (only plain text emails accepted)

Document Clean Entries (documentCleanEntries)

Version tracking for clean operations.

CREATE TABLE documentCleanEntries (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    cleanId uuid NOT NULL,
    version bigint NOT NULL,
    FOREIGN KEY (cleanId) REFERENCES documentCleans(id)
);

Purpose:

• Enables version-based filtering of clean results
• Used by currentCleanEntries view to get latest valid cleans

Document Text Extractions (documentTextExtractions)

OCR and text extraction results from AWS Textract.

CREATE TABLE documentTextExtractions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    cleanId uuid NOT NULL,
    bucket TEXT NOT NULL,
    key TEXT NOT NULL,
    hash TEXT NOT NULL,
    createdAt timestamp NOT NULL,
    part unsignedsmallint NOT NULL,
    FOREIGN KEY (cleanId) REFERENCES documentCleans(id)
);

Key Features:

• Links to successful clean operation
• Stores S3 location of extracted text
• Hash for content verification
• Part number for storage distribution

Document Text Extraction Entries (documentTextExtractionEntries)

Version tracking for text extraction operations.

CREATE TABLE documentTextExtractionEntries (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    textId uuid NOT NULL,
    version bigint NOT NULL,
    FOREIGN KEY (textId) REFERENCES documentTextExtractions(id)
);

Purpose:

• Enables version-based filtering of text extraction results
• Used by currentTextEntries view to get latest valid extractions

Processing Dependencies (Current)

graph LR
    Upload[documentUploads] --> Entry[documentEntries]
    Entry --> Clean[documentCleans]
    Clean --> FieldExtraction[documentFieldExtractions]

Legacy Sections Removed

The previous query subsystem and text extraction subsystem were removed from active runtime architecture. Legacy query/text schema details have been intentionally omitted from this generated doc to avoid drift.

For removal details, see:

• internal/database/migrations/00000000000119_remove_queries.up.sql
• internal/database/migrations/00000000000120_remove_text_extraction.up.sql