145 lines
4.4 KiB
SQL
145 lines
4.4 KiB
SQL
-- +goose Up
|
|
CREATE TABLE assets (
|
|
id TEXT PRIMARY KEY NOT NULL
|
|
CHECK (length(id) = 36),
|
|
creator_subject TEXT NOT NULL
|
|
CHECK (length(trim(creator_subject)) > 0),
|
|
purpose TEXT NOT NULL
|
|
CHECK (purpose IN ('TASK_REFERENCE')),
|
|
media_type TEXT NOT NULL
|
|
CHECK (media_type = 'image/jpeg'),
|
|
size_bytes INTEGER NOT NULL
|
|
CHECK (size_bytes > 0),
|
|
sha256 TEXT NOT NULL
|
|
CHECK (
|
|
length(sha256) = 64
|
|
AND sha256 NOT GLOB '*[^0-9a-f]*'
|
|
),
|
|
storage_key TEXT NOT NULL UNIQUE
|
|
CHECK (
|
|
length(storage_key) > 0
|
|
AND substr(storage_key, 1, 1) <> '/'
|
|
AND instr(storage_key, '\') = 0
|
|
AND instr(storage_key, '..') = 0
|
|
),
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX assets_creator_created_idx
|
|
ON assets (creator_subject, created_at DESC, id DESC);
|
|
|
|
CREATE TABLE purchase_tasks (
|
|
id TEXT PRIMARY KEY NOT NULL
|
|
CHECK (length(id) = 36),
|
|
creator_subject TEXT NOT NULL
|
|
CHECK (length(trim(creator_subject)) > 0),
|
|
source_ref TEXT
|
|
CHECK (
|
|
source_ref IS NULL
|
|
OR (
|
|
length(trim(source_ref)) > 0
|
|
AND length(CAST(source_ref AS BLOB)) <= 256
|
|
)
|
|
),
|
|
title TEXT NOT NULL
|
|
CHECK (
|
|
length(trim(title)) > 0
|
|
AND length(title) <= 120
|
|
AND length(CAST(title AS BLOB)) <= 2048
|
|
),
|
|
description TEXT NOT NULL
|
|
CHECK (length(CAST(description AS BLOB)) <= 8192),
|
|
sku TEXT NOT NULL
|
|
CHECK (
|
|
length(trim(sku)) > 0
|
|
AND length(CAST(sku AS BLOB)) <= 512
|
|
),
|
|
image_asset_id TEXT NOT NULL UNIQUE
|
|
REFERENCES assets(id) ON UPDATE RESTRICT ON DELETE RESTRICT,
|
|
quantity INTEGER NOT NULL
|
|
CHECK (quantity > 0),
|
|
max_budget_cents INTEGER
|
|
CHECK (max_budget_cents IS NULL OR max_budget_cents > 0),
|
|
currency TEXT NOT NULL DEFAULT 'CNY'
|
|
CHECK (currency = 'CNY'),
|
|
status TEXT NOT NULL
|
|
CHECK (
|
|
status IN (
|
|
'PENDING',
|
|
'CLAIMED',
|
|
'RUNNING',
|
|
'WAITING_CONFIRMATION',
|
|
'SUCCEEDED',
|
|
'FAILED',
|
|
'CANCELED'
|
|
)
|
|
),
|
|
version INTEGER NOT NULL
|
|
CHECK (version > 0),
|
|
cancel_reason TEXT
|
|
CHECK (
|
|
cancel_reason IS NULL
|
|
OR length(CAST(cancel_reason AS BLOB)) <= 500
|
|
),
|
|
canceled_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX purchase_tasks_creator_source_ref_idx
|
|
ON purchase_tasks (creator_subject, source_ref)
|
|
WHERE source_ref IS NOT NULL;
|
|
|
|
CREATE INDEX purchase_tasks_creator_created_idx
|
|
ON purchase_tasks (creator_subject, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX purchase_tasks_creator_status_created_idx
|
|
ON purchase_tasks (
|
|
creator_subject,
|
|
status,
|
|
created_at DESC,
|
|
id DESC
|
|
);
|
|
|
|
CREATE TABLE task_events (
|
|
id TEXT PRIMARY KEY NOT NULL
|
|
CHECK (length(id) = 36),
|
|
task_id TEXT NOT NULL
|
|
REFERENCES purchase_tasks(id) ON UPDATE RESTRICT ON DELETE CASCADE,
|
|
event_type TEXT NOT NULL
|
|
CHECK (event_type IN ('TASK_CREATED', 'TASK_CANCELED')),
|
|
message TEXT NOT NULL,
|
|
occurred_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX task_events_task_occurred_idx
|
|
ON task_events (task_id, occurred_at ASC, id ASC);
|
|
|
|
CREATE TABLE idempotency_records (
|
|
creator_subject TEXT NOT NULL,
|
|
operation TEXT NOT NULL,
|
|
idempotency_key TEXT NOT NULL,
|
|
request_sha256 TEXT NOT NULL
|
|
CHECK (
|
|
length(request_sha256) = 64
|
|
AND request_sha256 NOT GLOB '*[^0-9a-f]*'
|
|
),
|
|
resource_type TEXT NOT NULL
|
|
CHECK (resource_type IN ('ASSET', 'PURCHASE_TASK')),
|
|
resource_id TEXT NOT NULL
|
|
CHECK (length(resource_id) = 36),
|
|
created_at TEXT NOT NULL,
|
|
PRIMARY KEY (creator_subject, operation, idempotency_key)
|
|
);
|
|
|
|
-- +goose Down
|
|
DROP TABLE IF EXISTS idempotency_records;
|
|
DROP INDEX IF EXISTS task_events_task_occurred_idx;
|
|
DROP TABLE IF EXISTS task_events;
|
|
DROP INDEX IF EXISTS purchase_tasks_creator_status_created_idx;
|
|
DROP INDEX IF EXISTS purchase_tasks_creator_created_idx;
|
|
DROP INDEX IF EXISTS purchase_tasks_creator_source_ref_idx;
|
|
DROP TABLE IF EXISTS purchase_tasks;
|
|
DROP INDEX IF EXISTS assets_creator_created_idx;
|
|
DROP TABLE IF EXISTS assets;
|