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>
39 lines
1.9 KiB
SQL
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");
|