Embase_Conference_Workflow_Scheduler/Database/create_tracking_table.sql

-- =============================================================================
-- Embase Conference Abstract Scheduler — Tracking Table
-- =============================================================================
-- Run this script once on the target PostgreSQL database before the first
-- scheduler execution.
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1. Tracking table
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS tblEmbaseConferenceDispatch
(
    Id           BIGSERIAL    PRIMARY KEY,
    SourceId     BIGINT       NOT NULL,
    LotId        BIGINT       NOT NULL,
    FileName     VARCHAR(200) NOT NULL,
    DispatchDate TIMESTAMP    NOT NULL,
    CreatedOn    TIMESTAMP    NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE  tblEmbaseConferenceDispatch              IS 'Tracks conference abstract lots that have already been packaged and uploaded to SFTP.';
COMMENT ON COLUMN tblEmbaseConferenceDispatch.SourceId     IS 'Journal / source identifier (maps to tblsrmaster.sourceid).';
COMMENT ON COLUMN tblEmbaseConferenceDispatch.LotId        IS 'Lot that was included in the dispatched ZIP.';
COMMENT ON COLUMN tblEmbaseConferenceDispatch.FileName     IS 'Name of the generated ZIP file (e.g. emconflum0000007.zip).';
COMMENT ON COLUMN tblEmbaseConferenceDispatch.DispatchDate IS 'UTC timestamp when the ZIP was successfully uploaded.';
COMMENT ON COLUMN tblEmbaseConferenceDispatch.CreatedOn    IS 'Row insertion timestamp.';

-- ---------------------------------------------------------------------------
-- 2. Indexes for common access patterns
-- ---------------------------------------------------------------------------

-- Used by the "NOT IN dispatch" exclusion sub-query
CREATE INDEX IF NOT EXISTS idx_embase_conf_dispatch_lotid
    ON tblEmbaseConferenceDispatch (LotId);

-- Used when auditing dispatches for a given source
CREATE INDEX IF NOT EXISTS idx_embase_conf_dispatch_sourceid
    ON tblEmbaseConferenceDispatch (SourceId);

-- Unique constraint: a lot should only be dispatched once
ALTER TABLE tblEmbaseConferenceDispatch
    ADD CONSTRAINT uq_embase_conf_dispatch_lot UNIQUE (LotId);

-- ---------------------------------------------------------------------------
-- 3. Verification query — run after creation to confirm structure
-- ---------------------------------------------------------------------------
-- SELECT column_name, data_type, column_default
-- FROM   information_schema.columns
-- WHERE  table_name = 'tblembaseconferencedispatch'
-- ORDER  BY ordinal_position;