59 lines
2.4 KiB
SQL
59 lines
2.4 KiB
SQL
CREATE TABLE "wallets" (
|
|
"id" TEXT NOT NULL,
|
|
"owner_key" TEXT NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'IDR',
|
|
"balance_minor" INTEGER NOT NULL DEFAULT 0,
|
|
"held_minor" INTEGER NOT NULL DEFAULT 0,
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "wallets_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "wallet_transactions" (
|
|
"id" TEXT NOT NULL,
|
|
"wallet_id" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"direction" TEXT NOT NULL,
|
|
"amount_minor" INTEGER NOT NULL,
|
|
"balance_before_minor" INTEGER NOT NULL,
|
|
"balance_after_minor" INTEGER NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'posted',
|
|
"reference_type" TEXT,
|
|
"reference_id" TEXT,
|
|
"description" TEXT,
|
|
"metadata_json" JSONB,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "wallet_transactions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "payment_orders" (
|
|
"id" TEXT NOT NULL,
|
|
"wallet_id" TEXT NOT NULL,
|
|
"provider" TEXT NOT NULL DEFAULT 'midtrans',
|
|
"provider_order_id" TEXT NOT NULL,
|
|
"amount_minor" INTEGER NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'IDR',
|
|
"status" TEXT NOT NULL DEFAULT 'pending',
|
|
"snap_token" TEXT,
|
|
"redirect_url" TEXT,
|
|
"paid_at" TIMESTAMP(3),
|
|
"expired_at" TIMESTAMP(3),
|
|
"metadata_json" JSONB,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "payment_orders_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "wallets_owner_key_key" ON "wallets"("owner_key");
|
|
CREATE INDEX "wallet_transactions_wallet_id_created_at_idx" ON "wallet_transactions"("wallet_id", "created_at");
|
|
CREATE INDEX "wallet_transactions_reference_type_reference_id_idx" ON "wallet_transactions"("reference_type", "reference_id");
|
|
CREATE UNIQUE INDEX "payment_orders_provider_order_id_key" ON "payment_orders"("provider_order_id");
|
|
CREATE INDEX "payment_orders_wallet_id_status_idx" ON "payment_orders"("wallet_id", "status");
|
|
|
|
ALTER TABLE "wallet_transactions" ADD CONSTRAINT "wallet_transactions_wallet_id_fkey" FOREIGN KEY ("wallet_id") REFERENCES "wallets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "payment_orders" ADD CONSTRAINT "payment_orders_wallet_id_fkey" FOREIGN KEY ("wallet_id") REFERENCES "wallets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|