xiaoshu-app/scripts/sql/lesson-export.sql

BEGIN;
CREATE TABLE IF NOT EXISTS xs_history_export_job (
 id text PRIMARY KEY DEFAULT md5(random()::text||clock_timestamp()::text),
 company text NOT NULL,actor text NOT NULL,scope text NOT NULL,request_key text NOT NULL,filters jsonb NOT NULL,
 status text NOT NULL DEFAULT 'queued' CHECK(status IN('queued','running','ready','failed','cancelled')),
 total integer NOT NULL DEFAULT 0,processed integer NOT NULL DEFAULT 0,snapshot_ready boolean NOT NULL DEFAULT false,
 lease text,lease_until timestamptz,attempts integer NOT NULL DEFAULT 0,error text NOT NULL DEFAULT '',file_base64 text,
 created_at timestamptz NOT NULL DEFAULT now(),expires_at timestamptz NOT NULL DEFAULT now()+interval '24 hours',
 UNIQUE(company,actor,request_key)
);
CREATE UNIQUE INDEX IF NOT EXISTS xs_history_export_one_per_owner ON xs_history_export_job(company,actor) WHERE status IN('queued','running');
CREATE INDEX IF NOT EXISTS xs_history_export_queue ON xs_history_export_job(status,created_at);
CREATE TABLE IF NOT EXISTS xs_history_export_row (
 job_id text NOT NULL REFERENCES xs_history_export_job(id) ON DELETE CASCADE,position integer NOT NULL,value jsonb NOT NULL,
 PRIMARY KEY(job_id,position)
);
COMMIT;