xsl
71204affab
Creates the vector extension plus id_user, id_user_identity, id_consent, bl_quota_rule, bl_quota, bl_ledger, tk_task and ob_ai_call using the domain prefixes of 架构文档 §6.1, and seeds the quota rules. Reversible: verified by an upgrade/downgrade/upgrade round-trip, which CI now runs on every push.
261 lines
12 KiB
Python
Executable File
261 lines
12 KiB
Python
Executable File
"""initial foundation: pgvector + identity / billing / task / cost tables
|
|
|
|
Revision ID: 0001
|
|
Revises:
|
|
Create Date: 2026-08-13
|
|
|
|
Reversible: yes
|
|
|
|
Covers 架构文档 §6.2 for the subset the engineering foundation needs:
|
|
identity id_user, id_user_identity, id_consent
|
|
billing bl_quota_rule, bl_quota, bl_ledger
|
|
task tk_task
|
|
观测与成本 ob_ai_call
|
|
|
|
The pgvector extension is created here (not with the kb_ tables) so that every
|
|
environment — including CI — proves it is available before any embedding work
|
|
starts. Missing extensions are the classic "works on my laptop" failure.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
from collections.abc import Sequence
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
from sqlalchemy.dialects import postgresql
|
|
|
|
revision: str = "0001"
|
|
down_revision: str | None = None
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.execute("CREATE EXTENSION IF NOT EXISTS vector")
|
|
|
|
# ---------------- identity ----------------
|
|
op.create_table(
|
|
"id_user",
|
|
sa.Column("user_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("status", sa.String(length=16), nullable=False, server_default="active"),
|
|
sa.Column("nickname", sa.String(length=64), nullable=True),
|
|
sa.Column("avatar_key", sa.String(length=255), nullable=True),
|
|
sa.Column(
|
|
"register_source", sa.String(length=32), nullable=False, server_default="unknown"
|
|
),
|
|
sa.Column(
|
|
"created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
|
|
),
|
|
sa.Column("last_active_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.PrimaryKeyConstraint("user_id"),
|
|
)
|
|
op.create_index("ix_id_user_status", "id_user", ["status"])
|
|
|
|
op.create_table(
|
|
"id_user_identity",
|
|
sa.Column("identity_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("user_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("provider", sa.String(length=32), nullable=False),
|
|
sa.Column("union_id", sa.String(length=128), nullable=False),
|
|
sa.Column("open_id", sa.String(length=128), nullable=True),
|
|
sa.Column(
|
|
"created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
|
|
),
|
|
sa.PrimaryKeyConstraint("identity_id"),
|
|
sa.UniqueConstraint("provider", "union_id", name="uq_id_user_identity_provider_union"),
|
|
)
|
|
op.create_index("ix_id_user_identity_user", "id_user_identity", ["user_id"])
|
|
|
|
op.create_table(
|
|
"id_consent",
|
|
sa.Column("consent_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("user_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("consent_type", sa.String(length=32), nullable=False),
|
|
sa.Column("doc_version", sa.String(length=32), nullable=False),
|
|
sa.Column(
|
|
"granted_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
|
|
),
|
|
sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("client_ip", sa.String(length=64), nullable=True),
|
|
sa.Column("ua", sa.String(length=512), nullable=True),
|
|
sa.PrimaryKeyConstraint("consent_id"),
|
|
)
|
|
op.create_index("ix_id_consent_user_type", "id_consent", ["user_id", "consent_type"])
|
|
|
|
# ---------------- billing ----------------
|
|
op.create_table(
|
|
"bl_quota_rule",
|
|
sa.Column("quota_key", sa.String(length=64), nullable=False),
|
|
sa.Column("period_type", sa.String(length=16), nullable=False),
|
|
sa.Column("base_limit", sa.Integer(), nullable=False),
|
|
sa.Column("task_bonus_max", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("hard_cap", sa.Integer(), nullable=False),
|
|
sa.Column("description", sa.String(length=255), nullable=True),
|
|
sa.Column(
|
|
"updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
|
|
),
|
|
sa.PrimaryKeyConstraint("quota_key"),
|
|
)
|
|
|
|
op.create_table(
|
|
"bl_quota",
|
|
sa.Column("user_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("quota_key", sa.String(length=64), nullable=False),
|
|
sa.Column("period_key", sa.String(length=32), nullable=False),
|
|
sa.Column("used", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("limit_value", sa.Integer(), nullable=False),
|
|
sa.Column("bonus_value", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column(
|
|
"updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
|
|
),
|
|
sa.PrimaryKeyConstraint("user_id", "quota_key", "period_key", name="pk_bl_quota"),
|
|
)
|
|
|
|
op.create_table(
|
|
"bl_ledger",
|
|
sa.Column("tx_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("user_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("delta", sa.Integer(), nullable=False),
|
|
sa.Column("tx_type", sa.String(length=24), nullable=False),
|
|
sa.Column("status", sa.String(length=16), nullable=False, server_default="committed"),
|
|
sa.Column("reason", sa.String(length=128), nullable=True),
|
|
sa.Column("ref_type", sa.String(length=32), nullable=True),
|
|
sa.Column("ref_id", sa.String(length=64), nullable=True),
|
|
sa.Column("quota_key", sa.String(length=64), nullable=True),
|
|
sa.Column("period_key", sa.String(length=32), nullable=True),
|
|
sa.Column("balance_after", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("idempotency_key", sa.String(length=128), nullable=False),
|
|
sa.Column(
|
|
"created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
|
|
),
|
|
sa.PrimaryKeyConstraint("tx_id"),
|
|
sa.UniqueConstraint("idempotency_key", name="uq_bl_ledger_idempotency_key"),
|
|
)
|
|
op.create_index("ix_bl_ledger_user_created", "bl_ledger", ["user_id", "created_at"])
|
|
|
|
# ---------------- task ----------------
|
|
op.create_table(
|
|
"tk_task",
|
|
sa.Column("task_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("user_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("task_type", sa.String(length=32), nullable=False),
|
|
sa.Column("status", sa.String(length=16), nullable=False),
|
|
sa.Column("progress", sa.SmallInteger(), nullable=False, server_default="0"),
|
|
sa.Column("stage_text", sa.String(length=128), nullable=True),
|
|
sa.Column("idempotency_key", sa.String(length=128), nullable=False),
|
|
sa.Column("payload", postgresql.JSONB(), nullable=False, server_default="{}"),
|
|
sa.Column("result", postgresql.JSONB(), nullable=True),
|
|
sa.Column("error_code", sa.Integer(), nullable=True),
|
|
sa.Column("error_message", sa.String(length=512), nullable=True),
|
|
sa.Column("retry_count", sa.SmallInteger(), nullable=False, server_default="0"),
|
|
sa.Column("points_reserved", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("reserve_tx_id", sa.BigInteger(), nullable=True),
|
|
sa.Column(
|
|
"submitted_at",
|
|
sa.DateTime(timezone=True),
|
|
nullable=False,
|
|
server_default=sa.func.now(),
|
|
),
|
|
sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("eta_seconds", sa.Integer(), nullable=True),
|
|
sa.PrimaryKeyConstraint("task_id"),
|
|
sa.UniqueConstraint("idempotency_key", name="uq_tk_task_idempotency_key"),
|
|
)
|
|
op.create_index("ix_tk_task_user_status", "tk_task", ["user_id", "status"])
|
|
|
|
# ---------------- 观测与成本 ----------------
|
|
op.create_table(
|
|
"ob_ai_call",
|
|
sa.Column("call_id", sa.BigInteger(), nullable=False),
|
|
sa.Column("user_id", sa.BigInteger(), nullable=True),
|
|
sa.Column("scene", sa.String(length=48), nullable=False),
|
|
sa.Column("provider", sa.String(length=32), nullable=False),
|
|
sa.Column("model", sa.String(length=64), nullable=False),
|
|
sa.Column("prompt_id", sa.String(length=64), nullable=True),
|
|
sa.Column("prompt_version", sa.String(length=16), nullable=True),
|
|
sa.Column("tokens_in", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("tokens_out", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("tokens_cached", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("latency_ms", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("cost_cents", sa.Numeric(14, 6), nullable=False, server_default="0"),
|
|
sa.Column("success", sa.Boolean(), nullable=False, server_default=sa.false()),
|
|
sa.Column("error_code", sa.String(length=64), nullable=True),
|
|
sa.Column("error_message", sa.Text(), nullable=True),
|
|
sa.Column("ref_type", sa.String(length=32), nullable=True),
|
|
sa.Column("ref_id", sa.String(length=64), nullable=True),
|
|
sa.Column(
|
|
"created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()
|
|
),
|
|
sa.PrimaryKeyConstraint("call_id"),
|
|
)
|
|
op.create_index("ix_ob_ai_call_created", "ob_ai_call", ["created_at"])
|
|
op.create_index("ix_ob_ai_call_scene_created", "ob_ai_call", ["scene", "created_at"])
|
|
op.create_index("ix_ob_ai_call_user_created", "ob_ai_call", ["user_id", "created_at"])
|
|
|
|
# FR-BILL-04: 配额规则是数据不是代码，改配置不发版。
|
|
op.bulk_insert(
|
|
sa.table(
|
|
"bl_quota_rule",
|
|
sa.column("quota_key", sa.String),
|
|
sa.column("period_type", sa.String),
|
|
sa.column("base_limit", sa.Integer),
|
|
sa.column("task_bonus_max", sa.Integer),
|
|
sa.column("hard_cap", sa.Integer),
|
|
sa.column("description", sa.String),
|
|
),
|
|
[
|
|
{
|
|
"quota_key": "agent_chat",
|
|
"period_type": "day",
|
|
"base_limit": 20,
|
|
"task_bonus_max": 10,
|
|
"hard_cap": 50,
|
|
"description": "穿搭 Agent 每日对话轮次",
|
|
},
|
|
{
|
|
"quota_key": "report_basic",
|
|
"period_type": "month",
|
|
"base_limit": 1,
|
|
"task_bonus_max": 1,
|
|
"hard_cap": 3,
|
|
"description": "初级美学报告生成次数",
|
|
},
|
|
{
|
|
"quota_key": "tryon",
|
|
"period_type": "day",
|
|
"base_limit": 3,
|
|
"task_bonus_max": 3,
|
|
"hard_cap": 10,
|
|
"description": "智能试穿出图次数",
|
|
},
|
|
],
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index("ix_ob_ai_call_user_created", table_name="ob_ai_call")
|
|
op.drop_index("ix_ob_ai_call_scene_created", table_name="ob_ai_call")
|
|
op.drop_index("ix_ob_ai_call_created", table_name="ob_ai_call")
|
|
op.drop_table("ob_ai_call")
|
|
|
|
op.drop_index("ix_tk_task_user_status", table_name="tk_task")
|
|
op.drop_table("tk_task")
|
|
|
|
op.drop_index("ix_bl_ledger_user_created", table_name="bl_ledger")
|
|
op.drop_table("bl_ledger")
|
|
op.drop_table("bl_quota")
|
|
op.drop_table("bl_quota_rule")
|
|
|
|
op.drop_index("ix_id_consent_user_type", table_name="id_consent")
|
|
op.drop_table("id_consent")
|
|
op.drop_index("ix_id_user_identity_user", table_name="id_user_identity")
|
|
op.drop_table("id_user_identity")
|
|
op.drop_index("ix_id_user_status", table_name="id_user")
|
|
op.drop_table("id_user")
|
|
|
|
# The extension is left installed on purpose: dropping it would break any
|
|
# other schema in the same database that depends on it.
|