Jay Brown
c10fa98d0a
Restore original migrations (001-120) to fix schema_migrations version mismatch * Restore original migrations (001-120) to fix schema_migrations version mismatch The previous migration collapse broke deployed databases because: - Dev database was at version 120 (the real remove_text_extraction migration) - Main had collapsed migrations (001-012) + stub files (119_stub, 120_stub) - The migrate tool couldn't find version 119/120 with matching content This commit restores the original 27 migrations (versions 001-120) from before the collapse, ensuring backward compatibility with existing databases.
32 lines
1.3 KiB
SQL
32 lines
1.3 KiB
SQL
-- Create labels lookup table
|
|
CREATE TABLE labels (
|
|
label text PRIMARY KEY,
|
|
description text NOT NULL
|
|
);
|
|
|
|
-- Seed initial label 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');
|
|
|
|
-- Create documentLabels junction table
|
|
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 for efficient label queries
|
|
CREATE INDEX idx_documentlabels_documentid ON documentLabels(documentId);
|
|
CREATE INDEX idx_documentlabels_label ON documentLabels(label);
|
|
CREATE INDEX idx_documentlabels_appliedat ON documentLabels(appliedAt);
|
|
-- Composite index for finding most recent label application
|
|
CREATE INDEX idx_documentlabels_document_label_time ON documentLabels(documentId, label, appliedAt DESC);
|