query-orchestration/internal/database/migrations_collapsed/00000000000008_field_extractions.up.sql

-- Migration 008: Field Extractions (Consolidated)
-- Document field extraction storage for GenAI-extracted data
-- Merges: 112 + 113 + 114 + 117

-- 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);

-- Create documentFieldExtractionVersions table for version tracking
-- Includes documentId column from migration 117 for unique constraint
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(),

    -- documentId for unique version constraint (from migration 117)
    documentId uuid NOT NULL,

    FOREIGN KEY (fieldExtractionId) REFERENCES documentFieldExtractions(id),
    FOREIGN KEY (documentId) REFERENCES documents(id)
);

-- Indexes for version queries
CREATE INDEX idx_documentfieldextractionversions_fieldextractionid
    ON documentFieldExtractionVersions(fieldExtractionId);
CREATE INDEX idx_documentfieldextractionversions_version
    ON documentFieldExtractionVersions(version);
CREATE INDEX idx_documentfieldextractionversions_documentid
    ON documentFieldExtractionVersions(documentId);

-- Unique constraint to prevent duplicate versions per document (from migration 117)
CREATE UNIQUE INDEX idx_unique_version_per_document
    ON documentFieldExtractionVersions(documentId, version);

-- Create documentFieldExtractionArrayFields table for 1:N array fields
CREATE TABLE documentFieldExtractionArrayFields (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    fieldExtractionId uuid NOT NULL,
    arrayIndex smallint NOT NULL,

    -- Array-value fields (112 fields total - 1:N relationship)
    -- All fields at same arrayIndex form one "row" of the array

    -- 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,

    -- Patient demographics
    patientAgeMin text,
    patientAgeMax text,

    -- Reimbursement terms
    reimbTerm text,
    lobProgramRelationship text,
    lobProductRelationship text,

    -- Carveout and payment logic
    carveoutInd boolean,
    carveoutCd text,
    lesserOfInd boolean,
    greaterOfInd boolean,
    aareteDerivedReimbMethod text,
    unitOfMeasure text,

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

    -- Default and addition
    defaultInd boolean,
    additionDesc text,
    additionMaxFeeRateInc numeric(12,2),
    additionMaxPctRateInc numeric(10,4),
    aareteDerivedAdditionRateChangeTimeline text,

    -- Fee schedule
    aareteDerivedFeeSchedule text,
    aareteDerivedFeeScheduleVersion text,

    -- Service codes
    serviceTerm text,
    cpt4ProcCd text,
    cpt4ProcCdDesc text,
    cpt4ProcMod text,
    cpt4ProcModDesc text,
    revenueCd text,
    revenueCdDesc text,
    diagCd text,
    diagCdDesc text,
    ndcCd text,
    ndcCdDesc text,

    -- Claim admit and status
    claimAdmitTypeCd text,
    authAdmitTypeDesc text,
    claimStatusCd text,
    claimStatusCdDesc text,

    -- Grouper information
    grouperType text,
    grouperCd text,
    grouperCdDesc text,
    grouperPctRate numeric(10,4),
    grouperBaseRate numeric(12,2),
    aareteDerivedGrouperVersion text,
    grouperAlternativeLevelOfCare text,
    grouperSeverityInd boolean,
    grouperSeverity text,
    grouperRiskOfMortalitySubclass text,
    grouperTransferInd boolean,
    grouperReadmissionsInd boolean,
    grouperHacInd boolean,

    -- Outlier terms
    outlierTerm text,
    outlierFirstDollarInd boolean,
    rangeNbrDays text,
    outlierFixedLossNbrDaysThreshold numeric(10,2),
    outlierFixedLossThreshold numeric(12,2),
    outlierMaximum numeric(12,2),
    outlierMaximumFrequency numeric(10,2),
    outlierPctRate numeric(10,4),
    outlierExclusionCd text,
    outlierExclusionCdDesc text,

    -- Facility adjustments
    facilityAdjustmentTerm text,
    dshInd boolean,
    dshPctRate numeric(10,4),
    dshFeeRate numeric(12,2),
    imeInd boolean,
    imePctRate numeric(10,4),
    imeFeeRate numeric(12,2),
    ntapInd boolean,
    ntapPctRate numeric(10,4),
    ntapFeeRate numeric(12,2),
    ucInd boolean,
    ucPctRate numeric(10,4),
    ucFeeRate numeric(12,2),
    gmeInd boolean,
    gmePctRate numeric(10,4),
    gmeFeeRate numeric(12,2),

    -- Rate escalator
    rateEscalatorInd boolean,
    rateEscalatorDesc text,
    rateEscalatorMaxRateIncPct numeric(10,4),
    rateEscalatorRateChangeTimeline numeric(10,2),

    -- Stop loss
    stopLossTerm text,
    stopLossFirstDollarInd boolean,
    stopLossRangeNbrDays numeric(10,2),
    stopLossFixedLossThreshold numeric(12,2),
    stopLossMaximum numeric(12,2),
    stopLossMaximumFrequency numeric(10,2),
    stopLossDailyMaxRate numeric(12,2),
    stopLossPctRateOnExcessCharges numeric(10,4),
    stopLossExclusionCd text,
    stopLossExclusionDesc text,

    FOREIGN KEY (fieldExtractionId) REFERENCES documentFieldExtractions(id),

    -- Ensure unique index per extraction
    UNIQUE (fieldExtractionId, arrayIndex),

    -- Ensure arrayIndex is non-negative
    CHECK (arrayIndex >= 0)
);

-- Indexes for efficient array field queries
CREATE INDEX idx_documentfieldextractionarrayfields_fieldextractionid
    ON documentFieldExtractionArrayFields(fieldExtractionId);

-- Composite index for efficient ordered retrieval
CREATE INDEX idx_documentfieldextractionarrayfields_id_index
    ON documentFieldExtractionArrayFields(fieldExtractionId, arrayIndex);