273 lines
11 KiB
SQL
273 lines
11 KiB
SQL
-- +goose Up
|
||
-- v4 中的 attempt、submission 或证据没有设备/session/租约归属，不能安全猜测成 claim。
|
||
-- 在同一迁移事务中拒绝这类数据库，避免补出虚假的所有权审计链。
|
||
CREATE TABLE task_claim_upgrade_guard (
|
||
valid INTEGER NOT NULL CHECK (valid = 1)
|
||
);
|
||
|
||
INSERT INTO task_claim_upgrade_guard (valid)
|
||
SELECT CASE WHEN
|
||
(SELECT COUNT(*) FROM purchase_attempts) = 0
|
||
AND (SELECT COUNT(*) FROM order_submissions) = 0
|
||
AND (SELECT COUNT(*) FROM evidence_assets) = 0
|
||
THEN 1 ELSE 0 END;
|
||
|
||
DROP TABLE task_claim_upgrade_guard;
|
||
|
||
-- 该唯一索引把“一条授权只能产生一个 attempt”下沉到数据库；应用层检查不能替代它。
|
||
CREATE UNIQUE INDEX purchase_attempts_one_per_authorization_idx
|
||
ON purchase_attempts (authorization_id);
|
||
|
||
-- claim_generation 是 attempt lineage 的组成部分，不能只在应用层比较。
|
||
CREATE UNIQUE INDEX purchase_attempts_claim_lineage_idx
|
||
ON purchase_attempts (task_id, authorization_id, id, claim_generation);
|
||
|
||
CREATE TABLE purchase_attempt_claims (
|
||
attempt_id TEXT PRIMARY KEY,
|
||
task_id TEXT NOT NULL,
|
||
authorization_id TEXT NOT NULL UNIQUE,
|
||
claimed_by_device_id TEXT NOT NULL,
|
||
session_id TEXT NOT NULL CHECK (
|
||
length(session_id) = 36
|
||
AND substr(session_id, 9, 1) = '-'
|
||
AND substr(session_id, 14, 1) = '-'
|
||
AND substr(session_id, 19, 1) = '-'
|
||
AND substr(session_id, 24, 1) = '-'
|
||
AND length(replace(session_id, '-', '')) = 32
|
||
AND replace(session_id, '-', '') NOT GLOB '*[^0-9a-f]*'
|
||
AND substr(session_id, 15, 1) = '4'
|
||
AND substr(session_id, 20, 1) IN ('8', '9', 'a', 'b')
|
||
),
|
||
claim_generation INTEGER NOT NULL CHECK (
|
||
typeof(claim_generation) = 'integer' AND claim_generation > 0
|
||
),
|
||
task_version INTEGER NOT NULL CHECK (
|
||
typeof(task_version) = 'integer' AND task_version > 0
|
||
),
|
||
task_title TEXT NOT NULL CHECK (trim(task_title) <> ''),
|
||
authorization_task_version INTEGER NOT NULL CHECK (
|
||
typeof(authorization_task_version) = 'integer' AND authorization_task_version > 0
|
||
),
|
||
goods_id TEXT NOT NULL CHECK (trim(goods_id) <> ''),
|
||
sku_color TEXT NOT NULL CHECK (trim(sku_color) <> ''),
|
||
sku_size TEXT NOT NULL CHECK (trim(sku_size) <> ''),
|
||
quantity INTEGER NOT NULL CHECK (typeof(quantity) = 'integer' AND quantity > 0),
|
||
total_price_cap TEXT NOT NULL CHECK (trim(total_price_cap) <> ''),
|
||
authorization_expires_at TEXT NOT NULL CHECK (
|
||
authorization_expires_at = trim(authorization_expires_at)
|
||
AND length(authorization_expires_at) >= 20
|
||
AND substr(authorization_expires_at, 11, 1) = 'T'
|
||
AND substr(authorization_expires_at, -1, 1) = 'Z'
|
||
AND julianday(authorization_expires_at) IS NOT NULL
|
||
),
|
||
claim_nonce BLOB NOT NULL CHECK (
|
||
typeof(claim_nonce) = 'blob' AND length(claim_nonce) = 32
|
||
),
|
||
claim_token_sha256 BLOB NOT NULL CHECK (
|
||
typeof(claim_token_sha256) = 'blob' AND length(claim_token_sha256) = 32
|
||
),
|
||
lease_expires_at TEXT NOT NULL CHECK (
|
||
lease_expires_at = trim(lease_expires_at)
|
||
AND length(lease_expires_at) >= 20
|
||
AND substr(lease_expires_at, 11, 1) = 'T'
|
||
AND substr(lease_expires_at, -1, 1) = 'Z'
|
||
AND julianday(lease_expires_at) IS NOT NULL
|
||
),
|
||
claimed_at TEXT NOT NULL CHECK (
|
||
claimed_at = trim(claimed_at)
|
||
AND length(claimed_at) >= 20
|
||
AND substr(claimed_at, 11, 1) = 'T'
|
||
AND substr(claimed_at, -1, 1) = 'Z'
|
||
AND julianday(claimed_at) IS NOT NULL
|
||
),
|
||
closed_at TEXT CHECK (
|
||
closed_at IS NULL OR (
|
||
closed_at = trim(closed_at)
|
||
AND length(closed_at) >= 20
|
||
AND substr(closed_at, 11, 1) = 'T'
|
||
AND substr(closed_at, -1, 1) = 'Z'
|
||
AND julianday(closed_at) IS NOT NULL
|
||
AND julianday(closed_at) >= julianday(claimed_at)
|
||
)
|
||
),
|
||
UNIQUE (task_id, attempt_id),
|
||
UNIQUE (attempt_id, claimed_by_device_id, session_id),
|
||
UNIQUE (
|
||
task_id, attempt_id, claimed_by_device_id, session_id,
|
||
claim_generation, claim_token_sha256
|
||
),
|
||
UNIQUE (task_id, authorization_id, attempt_id),
|
||
FOREIGN KEY (task_id, authorization_id, attempt_id, claim_generation)
|
||
REFERENCES purchase_attempts(task_id, authorization_id, id, claim_generation),
|
||
FOREIGN KEY (claimed_by_device_id) REFERENCES device_credentials(device_id)
|
||
);
|
||
|
||
-- 过期、撤销或停轮询都不会自动关闭 claim；partial unique 因而阻止另一条开放归属。
|
||
CREATE UNIQUE INDEX purchase_attempt_claims_one_open_per_device_idx
|
||
ON purchase_attempt_claims (claimed_by_device_id)
|
||
WHERE closed_at IS NULL;
|
||
|
||
CREATE TABLE task_claim_requests (
|
||
claim_request_id TEXT PRIMARY KEY CHECK (
|
||
length(claim_request_id) = 36
|
||
AND substr(claim_request_id, 9, 1) = '-'
|
||
AND substr(claim_request_id, 14, 1) = '-'
|
||
AND substr(claim_request_id, 19, 1) = '-'
|
||
AND substr(claim_request_id, 24, 1) = '-'
|
||
AND length(replace(claim_request_id, '-', '')) = 32
|
||
AND replace(claim_request_id, '-', '') NOT GLOB '*[^0-9a-f]*'
|
||
AND substr(claim_request_id, 15, 1) = '4'
|
||
AND substr(claim_request_id, 20, 1) IN ('8', '9', 'a', 'b')
|
||
),
|
||
device_id TEXT NOT NULL,
|
||
session_id TEXT NOT NULL CHECK (
|
||
length(session_id) = 36
|
||
AND substr(session_id, 9, 1) = '-'
|
||
AND substr(session_id, 14, 1) = '-'
|
||
AND substr(session_id, 19, 1) = '-'
|
||
AND substr(session_id, 24, 1) = '-'
|
||
AND length(replace(session_id, '-', '')) = 32
|
||
AND replace(session_id, '-', '') NOT GLOB '*[^0-9a-f]*'
|
||
AND substr(session_id, 15, 1) = '4'
|
||
AND substr(session_id, 20, 1) IN ('8', '9', 'a', 'b')
|
||
),
|
||
outcome TEXT NOT NULL CHECK (outcome IN ('CLAIMED', 'EMPTY', 'BLOCKED')),
|
||
attempt_id TEXT,
|
||
response_lease_expires_at TEXT CHECK (
|
||
response_lease_expires_at IS NULL OR (
|
||
response_lease_expires_at = trim(response_lease_expires_at)
|
||
AND length(response_lease_expires_at) >= 20
|
||
AND substr(response_lease_expires_at, 11, 1) = 'T'
|
||
AND substr(response_lease_expires_at, -1, 1) = 'Z'
|
||
AND julianday(response_lease_expires_at) IS NOT NULL
|
||
)
|
||
),
|
||
error_code TEXT CHECK (error_code IS NULL OR error_code = 'manual_recovery_required'),
|
||
created_at TEXT NOT NULL CHECK (
|
||
created_at = trim(created_at)
|
||
AND length(created_at) >= 20
|
||
AND substr(created_at, 11, 1) = 'T'
|
||
AND substr(created_at, -1, 1) = 'Z'
|
||
AND julianday(created_at) IS NOT NULL
|
||
),
|
||
CHECK (
|
||
(outcome = 'CLAIMED' AND attempt_id IS NOT NULL AND response_lease_expires_at IS NOT NULL AND error_code IS NULL)
|
||
OR (outcome = 'EMPTY' AND attempt_id IS NULL AND response_lease_expires_at IS NULL AND error_code IS NULL)
|
||
OR (outcome = 'BLOCKED' AND attempt_id IS NULL AND response_lease_expires_at IS NULL AND error_code = 'manual_recovery_required')
|
||
),
|
||
FOREIGN KEY (device_id) REFERENCES device_credentials(device_id),
|
||
-- EMPTY/BLOCKED 行的 attempt_id 为 NULL，SQLite 会跳过复合 FK；CLAIMED 行则必须
|
||
-- 同时匹配原 claim 的设备和 session，不能由应用 bug 写成跨设备重放。
|
||
FOREIGN KEY (attempt_id, device_id, session_id)
|
||
REFERENCES purchase_attempt_claims(attempt_id, claimed_by_device_id, session_id)
|
||
);
|
||
|
||
CREATE TABLE purchase_attempt_lease_renewals (
|
||
renew_request_id TEXT PRIMARY KEY CHECK (
|
||
length(renew_request_id) = 36
|
||
AND substr(renew_request_id, 9, 1) = '-'
|
||
AND substr(renew_request_id, 14, 1) = '-'
|
||
AND substr(renew_request_id, 19, 1) = '-'
|
||
AND substr(renew_request_id, 24, 1) = '-'
|
||
AND length(replace(renew_request_id, '-', '')) = 32
|
||
AND replace(renew_request_id, '-', '') NOT GLOB '*[^0-9a-f]*'
|
||
AND substr(renew_request_id, 15, 1) = '4'
|
||
AND substr(renew_request_id, 20, 1) IN ('8', '9', 'a', 'b')
|
||
),
|
||
task_id TEXT NOT NULL,
|
||
attempt_id TEXT NOT NULL,
|
||
device_id TEXT NOT NULL,
|
||
session_id TEXT NOT NULL CHECK (
|
||
length(session_id) = 36
|
||
AND substr(session_id, 9, 1) = '-'
|
||
AND substr(session_id, 14, 1) = '-'
|
||
AND substr(session_id, 19, 1) = '-'
|
||
AND substr(session_id, 24, 1) = '-'
|
||
AND length(replace(session_id, '-', '')) = 32
|
||
AND replace(session_id, '-', '') NOT GLOB '*[^0-9a-f]*'
|
||
AND substr(session_id, 15, 1) = '4'
|
||
AND substr(session_id, 20, 1) IN ('8', '9', 'a', 'b')
|
||
),
|
||
claim_generation INTEGER NOT NULL CHECK (
|
||
typeof(claim_generation) = 'integer' AND claim_generation > 0
|
||
),
|
||
claim_token_sha256 BLOB NOT NULL CHECK (
|
||
typeof(claim_token_sha256) = 'blob' AND length(claim_token_sha256) = 32
|
||
),
|
||
expected_lease_expires_at TEXT NOT NULL CHECK (
|
||
expected_lease_expires_at = trim(expected_lease_expires_at)
|
||
AND length(expected_lease_expires_at) >= 20
|
||
AND substr(expected_lease_expires_at, 11, 1) = 'T'
|
||
AND substr(expected_lease_expires_at, -1, 1) = 'Z'
|
||
AND julianday(expected_lease_expires_at) IS NOT NULL
|
||
),
|
||
lease_expires_at TEXT NOT NULL CHECK (
|
||
lease_expires_at = trim(lease_expires_at)
|
||
AND length(lease_expires_at) >= 20
|
||
AND substr(lease_expires_at, 11, 1) = 'T'
|
||
AND substr(lease_expires_at, -1, 1) = 'Z'
|
||
AND julianday(lease_expires_at) IS NOT NULL
|
||
),
|
||
created_at TEXT NOT NULL CHECK (
|
||
created_at = trim(created_at)
|
||
AND length(created_at) >= 20
|
||
AND substr(created_at, 11, 1) = 'T'
|
||
AND substr(created_at, -1, 1) = 'Z'
|
||
AND julianday(created_at) IS NOT NULL
|
||
),
|
||
FOREIGN KEY (
|
||
task_id, attempt_id, device_id, session_id,
|
||
claim_generation, claim_token_sha256
|
||
) REFERENCES purchase_attempt_claims(
|
||
task_id, attempt_id, claimed_by_device_id, session_id,
|
||
claim_generation, claim_token_sha256
|
||
)
|
||
);
|
||
|
||
CREATE INDEX order_authorizations_claim_candidate_idx
|
||
ON order_authorizations (status, created_at, id);
|
||
|
||
-- 首次证据写入必须属于认证设备当前未关闭的 claim。历史资产的幂等重放不触发 INSERT，
|
||
-- 因而未来人工关闭 claim 后仍可稳定返回原资产。
|
||
-- +goose StatementBegin
|
||
CREATE TRIGGER evidence_assets_claim_owner_insert
|
||
BEFORE INSERT ON evidence_assets
|
||
FOR EACH ROW
|
||
WHEN NOT EXISTS (
|
||
SELECT 1 FROM purchase_attempt_claims AS claims
|
||
WHERE claims.task_id = NEW.task_id
|
||
AND claims.attempt_id = NEW.attempt_id
|
||
AND claims.claimed_by_device_id = NEW.uploaded_by_device_id
|
||
AND claims.closed_at IS NULL
|
||
)
|
||
BEGIN
|
||
SELECT RAISE(ABORT, 'evidence claim ownership required');
|
||
END;
|
||
-- +goose StatementEnd
|
||
|
||
-- +goose Down
|
||
-- 请求、续租、attempt、submission 和证据都是领取或下游审计事实，回滚不得静默删除。
|
||
CREATE TABLE task_claim_downgrade_guard (
|
||
valid INTEGER NOT NULL CHECK (valid = 1)
|
||
);
|
||
|
||
INSERT INTO task_claim_downgrade_guard (valid)
|
||
SELECT CASE WHEN
|
||
(SELECT COUNT(*) FROM task_claim_requests) = 0
|
||
AND (SELECT COUNT(*) FROM purchase_attempt_lease_renewals) = 0
|
||
AND (SELECT COUNT(*) FROM purchase_attempt_claims) = 0
|
||
AND (SELECT COUNT(*) FROM purchase_attempts) = 0
|
||
AND (SELECT COUNT(*) FROM order_submissions) = 0
|
||
AND (SELECT COUNT(*) FROM evidence_assets) = 0
|
||
THEN 1 ELSE 0 END;
|
||
|
||
DROP TABLE task_claim_downgrade_guard;
|
||
DROP TRIGGER evidence_assets_claim_owner_insert;
|
||
DROP INDEX order_authorizations_claim_candidate_idx;
|
||
DROP TABLE purchase_attempt_lease_renewals;
|
||
DROP TABLE task_claim_requests;
|
||
DROP INDEX purchase_attempt_claims_one_open_per_device_idx;
|
||
DROP TABLE purchase_attempt_claims;
|
||
DROP INDEX purchase_attempts_claim_lineage_idx;
|
||
DROP INDEX purchase_attempts_one_per_authorization_idx;
|