settlekit/packages/database/drizzle/0010_onchain_billing.sql
caelum0x ec04f3ddb5 feat(persistence): Postgres store for onchain subscriptions, charges and escrow
Migration 0010 adds onchain_subscriptions, onchain_charges (UNIQUE per
subscription+period) and onchain_escrow_payments. PgOnchainBillingStore
claims a period under a row lock so API and worker replicas can never
charge the same period twice. Adds shared access hooks (extend/suspend
core subscription, record payments, expire entitlements) and links
escrow payments to their checkout session.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-09-29 20:19:14 +03:00

39 lines
1.9 KiB
SQL

CREATE TABLE IF NOT EXISTS "onchain_subscriptions" (
"id" text PRIMARY KEY NOT NULL,
"organization_id" text NOT NULL,
"customer_id" text NOT NULL,
"status" text NOT NULL,
"network" text NOT NULL,
"method" text NOT NULL,
"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "onchain_charges" (
"id" text PRIMARY KEY NOT NULL,
"onchain_subscription_id" text NOT NULL,
"period_index" integer NOT NULL,
"status" text NOT NULL,
"lease_until" timestamp with time zone,
"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "onchain_charges_subscription_period_unique" UNIQUE("onchain_subscription_id","period_index")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "onchain_escrow_payments" (
"id" text PRIMARY KEY NOT NULL,
"organization_id" text NOT NULL,
"status" text NOT NULL,
"metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "onchain_subscriptions_org_idx" ON "onchain_subscriptions" USING btree ("organization_id");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "onchain_subscriptions_customer_idx" ON "onchain_subscriptions" USING btree ("customer_id");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "onchain_subscriptions_status_idx" ON "onchain_subscriptions" USING btree ("status");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "onchain_charges_status_idx" ON "onchain_charges" USING btree ("status");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "onchain_escrow_payments_org_idx" ON "onchain_escrow_payments" USING btree ("organization_id");