Lyon
106 lines
3.9 KiB
SQL
106 lines
3.9 KiB
SQL
CREATE TABLE IF NOT EXISTS hwlab_users (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
username TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
password_hash TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (status IN ('active', 'disabled', 'pending')),
|
|
CHECK (role IN ('user', 'admin'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS hwlab_user_sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
revoked_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_user_sessions_user_id_idx ON hwlab_user_sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS hwlab_user_sessions_token_hash_idx ON hwlab_user_sessions(token_hash);
|
|
|
|
CREATE TABLE IF NOT EXISTS hwlab_api_keys (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
key_prefix TEXT NOT NULL,
|
|
key_hash TEXT NOT NULL UNIQUE,
|
|
scopes_json JSONB NOT NULL DEFAULT '["api"]'::jsonb,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_used_at TIMESTAMPTZ,
|
|
revoked_at TIMESTAMPTZ,
|
|
CHECK (status IN ('active', 'revoked'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_api_keys_user_id_idx ON hwlab_api_keys(user_id);
|
|
CREATE INDEX IF NOT EXISTS hwlab_api_keys_key_hash_idx ON hwlab_api_keys(key_hash);
|
|
|
|
CREATE TABLE IF NOT EXISTS hwlab_credit_accounts (
|
|
user_id TEXT PRIMARY KEY REFERENCES hwlab_users(id) ON DELETE CASCADE,
|
|
balance_credits BIGINT NOT NULL DEFAULT 0,
|
|
reserved_credits BIGINT NOT NULL DEFAULT 0,
|
|
plan_id TEXT NOT NULL DEFAULT 'default',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (balance_credits >= 0),
|
|
CHECK (reserved_credits >= 0)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS hwlab_credit_ledger (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
|
|
delta_credits BIGINT NOT NULL,
|
|
balance_after BIGINT NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
idempotency_key TEXT UNIQUE,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_credit_ledger_user_id_idx ON hwlab_credit_ledger(user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS hwlab_billing_reservations (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
|
|
service_id TEXT NOT NULL,
|
|
estimated_credits BIGINT NOT NULL,
|
|
estimated_tokens BIGINT NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'reserved',
|
|
idempotency_key TEXT UNIQUE,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (estimated_credits >= 0),
|
|
CHECK (estimated_tokens >= 0),
|
|
CHECK (status IN ('reserved', 'completed', 'cancelled', 'expired'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_billing_reservations_user_id_idx ON hwlab_billing_reservations(user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS hwlab_usage_records (
|
|
id TEXT PRIMARY KEY,
|
|
reservation_id TEXT REFERENCES hwlab_billing_reservations(id) ON DELETE SET NULL,
|
|
user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
|
|
service_id TEXT NOT NULL,
|
|
unit TEXT NOT NULL DEFAULT 'token',
|
|
quantity BIGINT NOT NULL DEFAULT 0,
|
|
credits BIGINT NOT NULL DEFAULT 0,
|
|
idempotency_key TEXT UNIQUE,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (quantity >= 0),
|
|
CHECK (credits >= 0)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS hwlab_usage_records_user_id_idx ON hwlab_usage_records(user_id);
|
|
CREATE INDEX IF NOT EXISTS hwlab_usage_records_service_id_idx ON hwlab_usage_records(service_id);
|
|
|