hankin
423baff73b
- Docker bridge 网络隔离（8000 端口封死） - Gunicorn 4 Worker 多进程 - Alembic 数据库迁移基线 - 日志轮转 20m×3 - JWT 密钥 + DB 密码 + CORS 收紧 - 3-2-1 备份链路（NAS + R740-B 冷备） - 连接池 pool_pre_ping + pool_recycle=3600
144 lines
4.6 KiB
Python
144 lines
4.6 KiB
Python
"""initial_full_schema
|
|
|
|
Renames legacy tables to *_legacy backup, then creates
|
|
all tables with correct UUID PKs, types, and constraints.
|
|
|
|
Revision ID: 0001
|
|
Revises:
|
|
Create Date: 2026-02-24
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision: str = "0001"
|
|
down_revision: Union[str, None] = None
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
# Legacy tables to rename (preserve data)
|
|
LEGACY_TABLES = ["users", "clients", "follow_ups", "expenses"]
|
|
|
|
|
|
def upgrade() -> None:
|
|
conn = op.get_bind()
|
|
|
|
# Step 1: Rename legacy tables to *_legacy
|
|
for table in LEGACY_TABLES:
|
|
exists = conn.execute(sa.text(
|
|
f"SELECT EXISTS (SELECT 1 FROM information_schema.tables "
|
|
f"WHERE table_schema='public' AND table_name='{table}')"
|
|
)).scalar()
|
|
if exists:
|
|
conn.execute(sa.text(
|
|
f'ALTER TABLE "{table}" RENAME TO "{table}_legacy"'
|
|
))
|
|
|
|
# Step 2: Create new tables with proper schema
|
|
|
|
# ---- users ----
|
|
conn.execute(sa.text("""
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(128) NOT NULL,
|
|
role VARCHAR(20) NOT NULL DEFAULT 'viewer',
|
|
permissions JSON DEFAULT '[]',
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP DEFAULT now(),
|
|
updated_at TIMESTAMP DEFAULT now()
|
|
);
|
|
"""))
|
|
|
|
# ---- clients ----
|
|
conn.execute(sa.text("""
|
|
CREATE TABLE clients (
|
|
id UUID PRIMARY KEY,
|
|
name VARCHAR(200) NOT NULL,
|
|
contact_person VARCHAR(100),
|
|
phone VARCHAR(30),
|
|
address VARCHAR(500),
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT now(),
|
|
updated_at TIMESTAMP DEFAULT now()
|
|
);
|
|
"""))
|
|
conn.execute(sa.text(
|
|
"CREATE INDEX ix_clients_name ON clients (name);"
|
|
))
|
|
|
|
# ---- customer_logs ----
|
|
conn.execute(sa.text("""
|
|
CREATE TABLE customer_logs (
|
|
id UUID PRIMARY KEY,
|
|
customer_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT now()
|
|
);
|
|
"""))
|
|
conn.execute(sa.text(
|
|
"CREATE INDEX ix_customer_logs_cid ON customer_logs (customer_id);"
|
|
))
|
|
|
|
# ---- customer_tags ----
|
|
conn.execute(sa.text("""
|
|
CREATE TABLE customer_tags (
|
|
id UUID PRIMARY KEY,
|
|
customer_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
tag_name VARCHAR(100) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT now()
|
|
);
|
|
"""))
|
|
conn.execute(sa.text(
|
|
"CREATE INDEX ix_customer_tags_cid ON customer_tags (customer_id);"
|
|
))
|
|
|
|
# ---- follow_up_todos ----
|
|
conn.execute(sa.text("""
|
|
CREATE TABLE follow_up_todos (
|
|
id UUID PRIMARY KEY,
|
|
customer_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
task_desc TEXT NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT now()
|
|
);
|
|
"""))
|
|
conn.execute(sa.text(
|
|
"CREATE INDEX ix_follow_up_todos_cid ON follow_up_todos (customer_id);"
|
|
))
|
|
|
|
# ---- sales_opportunities ----
|
|
conn.execute(sa.text("""
|
|
CREATE TABLE sales_opportunities (
|
|
id UUID PRIMARY KEY,
|
|
customer_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
|
|
amount NUMERIC(12, 2) NOT NULL DEFAULT 0,
|
|
stage VARCHAR(20) NOT NULL DEFAULT 'intent',
|
|
created_at TIMESTAMP DEFAULT now()
|
|
);
|
|
"""))
|
|
conn.execute(sa.text(
|
|
"CREATE INDEX ix_sales_opp_cid ON sales_opportunities (customer_id);"
|
|
))
|
|
|
|
|
|
def downgrade() -> None:
|
|
conn = op.get_bind()
|
|
|
|
# Drop new tables
|
|
for t in ["sales_opportunities", "follow_up_todos", "customer_tags",
|
|
"customer_logs", "clients", "users"]:
|
|
conn.execute(sa.text(f'DROP TABLE IF EXISTS "{t}" CASCADE'))
|
|
|
|
# Restore legacy tables
|
|
for table in LEGACY_TABLES:
|
|
exists = conn.execute(sa.text(
|
|
f"SELECT EXISTS (SELECT 1 FROM information_schema.tables "
|
|
f"WHERE table_schema='public' AND table_name='{table}_legacy')"
|
|
)).scalar()
|
|
if exists:
|
|
conn.execute(sa.text(
|
|
f'ALTER TABLE "{table}_legacy" RENAME TO "{table}"'
|
|
))
|