143 lines
5.5 KiB
SQL
143 lines
5.5 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "OrderStatus" AS ENUM ('PENDING_PAYMENT', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "CouponKind" AS ENUM ('FIXED', 'PERCENT');
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "stock_reservations" ADD COLUMN "order_id" UUID;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "carts" (
|
|
"id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"version" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "carts_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "cart_lines" (
|
|
"id" UUID NOT NULL,
|
|
"cart_id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"variant_id" UUID NOT NULL,
|
|
"quantity" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "cart_lines_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "coupons" (
|
|
"id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"code" VARCHAR(40) NOT NULL,
|
|
"kind" "CouponKind" NOT NULL,
|
|
"currency" CHAR(3) NOT NULL,
|
|
"amount" DECIMAL(12,2),
|
|
"percent_bps" INTEGER,
|
|
"minimum_subtotal" DECIMAL(12,2) NOT NULL DEFAULT 0,
|
|
"max_uses" INTEGER NOT NULL,
|
|
"per_user_limit" INTEGER NOT NULL,
|
|
"starts_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"ends_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
CONSTRAINT "coupons_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "orders" (
|
|
"id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"status" "OrderStatus" NOT NULL DEFAULT 'PENDING_PAYMENT',
|
|
"currency" CHAR(3) NOT NULL,
|
|
"subtotal" DECIMAL(16,2) NOT NULL,
|
|
"discount" DECIMAL(16,2) NOT NULL,
|
|
"merchandise_total" DECIMAL(16,2) NOT NULL,
|
|
"address_snapshot" JSONB NOT NULL,
|
|
"coupon_snapshot" JSONB,
|
|
"coupon_id" UUID,
|
|
"idempotency_key" UUID NOT NULL,
|
|
"request_hash" CHAR(64) NOT NULL,
|
|
"expires_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "orders_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "order_lines" (
|
|
"id" UUID NOT NULL,
|
|
"order_id" UUID NOT NULL,
|
|
"organization_id" UUID NOT NULL,
|
|
"variant_id" UUID NOT NULL,
|
|
"sku" VARCHAR(64) NOT NULL,
|
|
"product_name" VARCHAR(160) NOT NULL,
|
|
"variant_name" VARCHAR(160) NOT NULL,
|
|
"quantity" INTEGER NOT NULL,
|
|
"unit_price" DECIMAL(12,2) NOT NULL,
|
|
"line_total" DECIMAL(16,2) NOT NULL,
|
|
|
|
CONSTRAINT "order_lines_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "carts_user_id_organization_id_key" ON "carts"("user_id", "organization_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "carts_id_organization_id_key" ON "carts"("id", "organization_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "cart_lines_cart_id_variant_id_key" ON "cart_lines"("cart_id", "variant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "coupons_organization_id_code_key" ON "coupons"("organization_id", "code");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "coupons_id_organization_id_key" ON "coupons"("id", "organization_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "orders_organization_id_user_id_created_at_id_idx" ON "orders"("organization_id", "user_id", "created_at", "id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "orders_coupon_id_status_expires_at_idx" ON "orders"("coupon_id", "status", "expires_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "orders_user_id_organization_id_idempotency_key_key" ON "orders"("user_id", "organization_id", "idempotency_key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "orders_id_organization_id_key" ON "orders"("id", "organization_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "order_lines_order_id_variant_id_key" ON "order_lines"("order_id", "variant_id");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "carts" ADD CONSTRAINT "carts_user_id_organization_id_fkey" FOREIGN KEY ("user_id", "organization_id") REFERENCES "users"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "cart_lines" ADD CONSTRAINT "cart_lines_cart_id_organization_id_fkey" FOREIGN KEY ("cart_id", "organization_id") REFERENCES "carts"("id", "organization_id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "cart_lines" ADD CONSTRAINT "cart_lines_variant_id_organization_id_fkey" FOREIGN KEY ("variant_id", "organization_id") REFERENCES "product_variants"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "coupons" ADD CONSTRAINT "coupons_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "orders" ADD CONSTRAINT "orders_user_id_organization_id_fkey" FOREIGN KEY ("user_id", "organization_id") REFERENCES "users"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "orders" ADD CONSTRAINT "orders_coupon_id_organization_id_fkey" FOREIGN KEY ("coupon_id", "organization_id") REFERENCES "coupons"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "order_lines" ADD CONSTRAINT "order_lines_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_lines" ADD CONSTRAINT "order_lines_variant_id_organization_id_fkey" FOREIGN KEY ("variant_id", "organization_id") REFERENCES "product_variants"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "stock_reservations" ADD CONSTRAINT "stock_reservations_order_id_organization_id_fkey" FOREIGN KEY ("order_id", "organization_id") REFERENCES "orders"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;
|