Jay Brown
36 lines
1.1 KiB
SQL
36 lines
1.1 KiB
SQL
CREATE TYPE batch_outcome_status AS ENUM (
|
|
-- Extraction-time outcomes (set by batch worker)
|
|
'submitted',
|
|
'duplicate',
|
|
'failed_open',
|
|
'failed_read',
|
|
'failed_s3_upload',
|
|
'failed_upload',
|
|
'invalid_type',
|
|
-- Pipeline outcomes (set by runners, update the row in place)
|
|
'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_outcome_status 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)
|
|
);
|
|
|
|
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);
|
|
CREATE INDEX idx_documents_batch_id_filename ON documents(batch_id, filename);
|