49 lines
1.3 KiB
SQL
49 lines
1.3 KiB
SQL
|
-- Create documentFieldExtractions table for single-value fields
|
||
|
|
CREATE TABLE documentFieldExtractions (
|
||
|
|
id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
|
||
|
|
documentId uuid NOT NULL,
|
||
|
|
|
||
|
|
-- Single-value fields (19 fields total - 1:1 relationship)
|
||
|
|
-- 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 for efficient queries
|
||
|
|
CREATE INDEX idx_documentfieldextractions_documentid
|
||
|
|
ON documentFieldExtractions(documentId);
|
||
|
|
CREATE INDEX idx_documentfieldextractions_createdby
|
||
|
|
ON documentFieldExtractions(createdBy);
|
||
|
|
CREATE INDEX idx_documentfieldextractions_filename
|
||
|
|
ON documentFieldExtractions(fileName);
|