95 lines
3.8 KiB
SQL
95 lines
3.8 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "TaxMode" AS ENUM ('INCLUSIVE', 'EXCLUSIVE');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "commerce_events" (
|
|
"id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"order_id" UUID NOT NULL,
|
|
"kind" VARCHAR(80) NOT NULL,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "commerce_events_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "event_deliveries" (
|
|
"event_id" UUID NOT NULL,
|
|
"attempts" INTEGER NOT NULL DEFAULT 0,
|
|
"available_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"lease_token" UUID,
|
|
"lease_expires_at" TIMESTAMPTZ(3),
|
|
"delivered_at" TIMESTAMPTZ(3),
|
|
"last_error_code" VARCHAR(80),
|
|
|
|
CONSTRAINT "event_deliveries_pkey" PRIMARY KEY ("event_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "pricing_policies" (
|
|
"id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"name" VARCHAR(100) NOT NULL,
|
|
"currency" CHAR(3) NOT NULL,
|
|
"country_code" CHAR(2) NOT NULL,
|
|
"region" VARCHAR(100) NOT NULL DEFAULT '',
|
|
"tax_mode" "TaxMode" NOT NULL,
|
|
"merchandise_tax_bps" INTEGER NOT NULL,
|
|
"shipping_fee" DECIMAL(12,2) NOT NULL,
|
|
"shipping_tax_bps" INTEGER NOT NULL,
|
|
"free_shipping_minimum" DECIMAL(12,2),
|
|
"active" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "pricing_policies_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "order_pricing" (
|
|
"order_id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"policy_id" UUID NOT NULL,
|
|
"policy_snapshot" JSONB NOT NULL,
|
|
"merchandise_tax" DECIMAL(16,2) NOT NULL,
|
|
"shipping_net" DECIMAL(16,2) NOT NULL,
|
|
"shipping_tax" DECIMAL(16,2) NOT NULL,
|
|
"tax_total" DECIMAL(16,2) NOT NULL,
|
|
"payable_total" DECIMAL(16,2) NOT NULL,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "order_pricing_pkey" PRIMARY KEY ("order_id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "commerce_events_organization_id_created_at_id_idx" ON "commerce_events"("organization_id", "created_at", "id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "commerce_events_order_id_kind_key" ON "commerce_events"("order_id", "kind");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "event_deliveries_delivered_at_available_at_lease_expires_at_idx" ON "event_deliveries"("delivered_at", "available_at", "lease_expires_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "pricing_policies_organization_id_currency_country_code_regi_idx" ON "pricing_policies"("organization_id", "currency", "country_code", "region", "active");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "pricing_policies_id_organization_id_key" ON "pricing_policies"("id", "organization_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "order_pricing_order_id_organization_id_key" ON "order_pricing"("order_id", "organization_id");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "commerce_events" ADD CONSTRAINT "commerce_events_order_id_organization_id_fkey" FOREIGN KEY ("order_id", "organization_id") REFERENCES "orders"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "event_deliveries" ADD CONSTRAINT "event_deliveries_event_id_fkey" FOREIGN KEY ("event_id") REFERENCES "commerce_events"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "pricing_policies" ADD CONSTRAINT "pricing_policies_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "order_pricing" ADD CONSTRAINT "order_pricing_order_id_organization_id_fkey" FOREIGN KEY ("order_id", "organization_id") REFERENCES "orders"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "order_pricing" ADD CONSTRAINT "order_pricing_policy_id_organization_id_fkey" FOREIGN KEY ("policy_id", "organization_id") REFERENCES "pricing_policies"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|