xiaoshu-app/scripts/sql/legacy-credit-balances.sql

-- Keep legacy source balances separate from live accounts and all local credit postings.
CREATE TABLE IF NOT EXISTS "LegacyCreditBalanceSnapshot" (
 company text NOT NULL,"studentId" numeric NOT NULL,"runId" text NOT NULL,
 balances jsonb NOT NULL,"capturedAt" timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY(company,"studentId","runId")
);
CREATE TABLE IF NOT EXISTS "LegacyCreditBalanceState" (
 company text NOT NULL,"studentId" numeric NOT NULL,account text NOT NULL,
 balance numeric NOT NULL,"sourceBalance" numeric NOT NULL,"localNet" numeric NOT NULL,
 "sourceIds" jsonb NOT NULL,"runId" text NOT NULL,"updatedAt" timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY(company,"studentId",account)
);
CREATE TABLE IF NOT EXISTS "LegacyCreditBalanceAudit" (
 company text NOT NULL,"studentId" numeric NOT NULL,account text NOT NULL,"runId" text NOT NULL,
 "before" numeric NOT NULL,"after" numeric NOT NULL,delta numeric NOT NULL,evidence jsonb NOT NULL,
 "createdAt" timestamptz NOT NULL DEFAULT now(),PRIMARY KEY(company,"studentId",account,"runId")
);
CREATE TABLE IF NOT EXISTS "LegacyCreditBalanceReceipt" (
 company text NOT NULL,"studentId" numeric NOT NULL,"runId" text NOT NULL,
 result jsonb NOT NULL,"updatedAt" timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY(company,"studentId","runId")
);
REVOKE ALL ON "LegacyCreditBalanceSnapshot","LegacyCreditBalanceState","LegacyCreditBalanceAudit","LegacyCreditBalanceReceipt" FROM PUBLIC;