feat: 新增 PG 初始化脚本 scripts/init_postgres.py

新机器一键拉起数据库, 替代手动跑 psql 建用户建库的 6 行命令.

脚本特性:
- 跨平台 Python (Windows/macOS/Linux 通吃, 不依赖 psql CLI 在 PATH 里)
- 直接用 psycopg 连 PG, 不 shell out
- 幂等: 用户已存在则重置密码, 库已存在则跳过, 重复跑不出错
- 业务密码自动 secrets.token_urlsafe(32) 强随机, 不让同事自己想
- 自动从 .env.example 复制出 .env 并写入 DATABASE_URL
- .env 里已有 DATABASE_URL 会询问是否覆盖 (不无脑覆盖)
- 跑完自动 alembic upgrade head, 一条命令到可用状态
- 全交互项支持环境变量提前指定 (PG_SUPER_PASS / APP_DB_PASS 等), CI 友好

同事新机器初始化流程从 6 步简化为 3 步:
  pip install -e .
  python scripts/init_postgres.py    # 唯一手动输入项: postgres 超级用户密码
  ./run.sh

CREATE/ALTER USER 注意点: PG 不支持参数化密码绑定, 用 sql.Literal 内联,
psycopg 自带的转义会处理引号和注入.

docs/数据库迁移.md 拆出 PG / SQLite 两套初始化流程, PG 走脚本,
SQLite 保留旧步骤 (本地临时回退用).

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>

alembic/versions/ef96beb47b1e_convert_savings_record_dishes_from_json_.py

@@ -0,0 +1,36 @@
+"""convert savings_record.dishes from json to jsonb
+
+PG only. SQLite 上 JSON/JSONB 都是 TEXT, 此迁移是 no-op。
+
+Revision ID: ef96beb47b1e
+Revises: c8d9e0f1a2b3
+Create Date: 2026-05-29 10:57:21.471774
+"""
+from typing import Sequence, Union
+
+from alembic import op
+from sqlalchemy.dialects.postgresql import JSONB
+
+
+revision: str = 'ef96beb47b1e'
+down_revision: Union[str, Sequence[str], None] = 'c8d9e0f1a2b3'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    if op.get_bind().dialect.name != "postgresql":
+        return
+    op.execute(
+        "ALTER TABLE savings_record "
+        "ALTER COLUMN dishes TYPE JSONB USING dishes::JSONB"
+    )
+
+
+def downgrade() -> None:
+    if op.get_bind().dialect.name != "postgresql":
+        return
+    op.execute(
+        "ALTER TABLE savings_record "
+        "ALTER COLUMN dishes TYPE JSON USING dishes::JSON"
+    )

app/api/v1/compare.py

@@ -1,20 +1,17 @@
-"""比价业务透传端点(外卖 + 电商两套)。
+"""外卖比价业务透传端点。
 
-把客户端的 POST /api/v1/{intent/recognize, price/step, ecom/intent/recognize, ecom/step}
+把客户端的 POST /api/v1/intent/recognize 和 /api/v1/price/step 请求原样转发给
-请求原样转发给 pricebot-backend 的对应路径。MVP 阶段**不鉴权**(同 coupon/step,
+pricebot-backend 的 /api/intent/recognize 和 /api/price/step。MVP 阶段**不鉴权**
-见 docs/待办与技术债.md P1:device_id 透传区分设备,待补 JWT + device_id↔user_id
+(同 coupon/step,见 docs/待办与技术债.md P1:device_id 透传区分设备,待补 JWT +
-绑定后才能做用户级画像)。
+device_id↔user_id 绑定后才能做用户级画像)。
 
-外卖(food):
 - Phase 1 /intent/recognize:从源平台(淘宝闪购/美团/京东外卖)购物车页识别店名+菜品+
   价格,一次性。
 - Phase 2 /price/step:多轮循环,在目标平台复现订单读到手价,直到 done。
 
-电商(ecom):
-- Phase 1 /ecom/intent/recognize:从源平台(淘宝/抖音/拼多多)SKU+详情页识别商品,一次性。
-- Phase 2 /ecom/step:多轮循环,在目标平台搜索商品并采价,直到 done。
-
 真正的目标驱动比价逻辑在 pricebot-backend(GoalEngine,另一个 repo),本接口只是透传壳。
+电商(ecom)那两个端点 food MVP 暂不需要,以后放开 scene 时再加 /ecom/intent/recognize
+和 /ecom/step 两行即可。
 
 pricebot 协议文档:
   pricebot-backend/docs/main/02_api_protocol.md
@@ -89,13 +86,3 @@ async def intent_recognize(request: Request) -> dict[str, Any]:
 @router.post("/price/step", summary="外卖比价 Phase 2 步进 (透传到 pricebot)")
 async def price_step(request: Request) -> dict[str, Any]:
     return await _passthrough(request, "/api/price/step")
-
-
-@router.post("/ecom/intent/recognize", summary="电商比价 Phase 1 意图识别 (透传到 pricebot)")
-async def ecom_intent_recognize(request: Request) -> dict[str, Any]:
-    return await _passthrough(request, "/api/ecom/intent/recognize")
-
-
-@router.post("/ecom/step", summary="电商比价 Phase 2 步进 (透传到 pricebot)")
-async def ecom_step(request: Request) -> dict[str, Any]:
-    return await _passthrough(request, "/api/ecom/step")

app/api/v1/meituan.py

@@ -9,6 +9,7 @@ from concurrent.futures import ThreadPoolExecutor, as_completed
 
 from fastapi import APIRouter, HTTPException
 
+from app.core.config import settings
 from app.integrations.meituan import MeituanCpsError, get_referral_link, query_coupon
 from app.schemas.meituan import (
     CouponCard,
@@ -27,6 +28,8 @@ router = APIRouter(prefix="/api/v1/meituan", tags=["meituan-cps"])
 
 @router.post("/coupons", response_model=CouponListResponse, summary="券列表(为您推荐)")
 def list_coupons(req: CouponListRequest) -> CouponListResponse:
+    if not settings.mt_cps_configured:
+        return CouponListResponse(items=[], has_next=False, search_id=None)
     logger.info("[coupons] lon=%.6f lat=%.6f topic=%s", req.longitude, req.latitude, req.list_topic_id)
     try:
         raw = query_coupon(
@@ -78,6 +81,8 @@ def _interleave(waimai: list[dict], daodian: list[dict]) -> list[CouponCard]:
 
 @router.post("/feed", response_model=FeedResponse, summary="混合feed(外卖+到店交叉, 无限流)")
 def feed(req: FeedRequest) -> FeedResponse:
+    if not settings.mt_cps_configured:
+        return FeedResponse(items=[], has_next=False, page=req.page)
     page_idx = req.page - 1
     lon, lat = req.longitude, req.latitude
     logger.info("[feed] page=%s lon=%.6f lat=%.6f", req.page, lon, lat)
@@ -108,6 +113,8 @@ def feed(req: FeedRequest) -> FeedResponse:
 
 @router.post("/referral-link", response_model=ReferralLinkResponse, summary="换取推广链接(点抢时调)")
 def referral_link(req: ReferralLinkRequest) -> ReferralLinkResponse:
+    if not settings.mt_cps_configured:
+        return ReferralLinkResponse(link="", link_map={})
     try:
         raw = get_referral_link(
             product_view_sign=req.product_view_sign,

app/core/config.py

@@ -53,11 +53,18 @@ class Settings(BaseSettings):
     SMS_SEND_INTERVAL_SEC: int = 60
 
     # ===== 美团联盟 CPS =====
+    # 未配置时所有 /api/v1/meituan/* 接口 200 返空(优雅降级),不影响登录/领券等其他业务。
     MT_CPS_APP_KEY: str = ""
     MT_CPS_APP_SECRET: str = ""
     MT_CPS_HOST: str = "https://media.meituan.com"
     MT_CPS_TIMEOUT_SEC: int = 15
     MT_CPS_DEFAULT_SID: str = "sgbjia"
+
+    @property
+    def mt_cps_configured(self) -> bool:
+        """美团 CPS 凭证齐全(缺则接口返空,而非 502)。"""
+        return bool(self.MT_CPS_APP_KEY and self.MT_CPS_APP_SECRET)
+
     # ===== 微信支付(商家转账到零钱 / 提现)=====
     # 真实凭证放 .env(已 gitignore),证书 .pem 放 secrets/。WECHAT_APP_ID 同时用于
     # 微信登录(code 换 openid)与转账,必须与 App 端开放平台 appid 一致。

app/db/session.py

@@ -27,19 +27,25 @@ def _ensure_sqlite_dir(url: str) -> None:
 
 _ensure_sqlite_dir(settings.DATABASE_URL)
 
+_is_sqlite = settings.DATABASE_URL.startswith("sqlite")
+
 # SQLite 跨线程访问要 check_same_thread=False;PG/MySQL 不需要这个参数
 _connect_args: dict = {}
-if settings.DATABASE_URL.startswith("sqlite"):
+if _is_sqlite:
     _connect_args["check_same_thread"] = False
 
-engine = create_engine(
+_engine_kwargs: dict = {
-    settings.DATABASE_URL,
+    "connect_args": _connect_args,
-    connect_args=_connect_args,
     # echo 在 dev 下打 SQL,生产关掉
-    echo=settings.APP_DEBUG and not settings.is_prod,
+    "echo": settings.APP_DEBUG and not settings.is_prod,
-    future=True,
+    "future": True,
-    pool_pre_ping=True,
+    "pool_pre_ping": True,
-)
+}
+# SQLite 用单文件不需要池;PG/MySQL 必须显式池化 + recycle 防 idle 断连
+if not _is_sqlite:
+    _engine_kwargs.update(pool_size=10, max_overflow=20, pool_recycle=3600)
+
+engine = create_engine(settings.DATABASE_URL, **_engine_kwargs)
 
 SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False, expire_on_commit=False)
 

app/models/savings.py

@@ -8,7 +8,8 @@ from __future__ import annotations
 
 from datetime import datetime
 
-from sqlalchemy import JSON, DateTime, ForeignKey, Integer, String, func
+from sqlalchemy import DateTime, ForeignKey, Integer, String, func
+from sqlalchemy.dialects.postgresql import JSONB
 from sqlalchemy.orm import Mapped, mapped_column
 
 from app.db.base import Base
@@ -28,8 +29,8 @@ class SavingsRecord(Base):
     title: Mapped[str | None] = mapped_column(String(128), nullable=True)
     # 店铺名(订单明细卡标题,如「窑鸡王（王府井店）」)
     shop_name: Mapped[str | None] = mapped_column(String(128), nullable=True)
-    # 菜品名列表(JSON),前 2 道直接展示,其余收进「还有 N 道菜」展开。SQLite 存为 TEXT。
+    # 菜品名列表(JSONB),PG 上可建 GIN 索引,前 2 道直接展示,其余收进「还有 N 道菜」展开。
-    dishes: Mapped[list[str]] = mapped_column(JSON, nullable=False, default=list)
+    dishes: Mapped[list[str]] = mapped_column(JSONB, nullable=False, default=list)
     # 来源:demo(演示) / compare(真实比价上报)
     source: Mapped[str] = mapped_column(String(16), nullable=False, default="compare")
 

docs/数据库迁移.md

@@ -15,20 +15,37 @@ alembic upgrade head
 ---
 
 ## 1. 首次初始化（clone 后）
+
+### 1.1 用 PostgreSQL（推荐，与生产一致）
+前置:本机已装 PostgreSQL 16 + 知道 postgres 超级用户密码。
+
 ```bash
-# (1) 装依赖(在你的虚拟环境里)
+# (1) 装依赖(在你的虚拟环境里, 含 psycopg)
 pip install -e .
 
-# (2) 配 .env(从模板复制,至少填 JWT_SECRET_KEY)
+# (2) 一键建用户 + 建库 + 写 .env + 跑迁移
-cp .env.example .env
+python scripts/init_postgres.py
+# 按提示输入 PG 超级用户密码即可。脚本会:
+#   - 自动建业务用户 shaguabijia_app + 业务库 shaguabijia
+#   - 自动生成业务用户强密码,写入 .env 的 DATABASE_URL
+#   - 跑完 alembic upgrade head
 
-# (3) SQLite 默认库需要 data/ 目录存在
+# (3) 启动
+./run.sh
+```
+
+可用环境变量提前指定(免交互):
+```bash
+PG_SUPER_PASS=xxx APP_DB_PASS=yyy python scripts/init_postgres.py
+```
+脚本幂等,重复跑会重置业务用户密码、跳过已存在的库。
+
+### 1.2 用 SQLite(本地开发临时用,不接生产链路时)
+```bash
+pip install -e .
+cp .env.example .env       # 不动 DATABASE_URL, 默认 sqlite:///./data/app.db
 mkdir -p data
-
-# (4) 跑迁移建表 —— 关键
 alembic upgrade head
-
-# (5) 启动(run.sh 会自动重跑 3+4,所以平时直接 ./run.sh 也行)
 ./run.sh
 ```
 > 也可以直接 `bash scripts/migrate.sh` 只做迁移、不启服务（部署/CI 用）。

pyproject.toml

@@ -17,6 +17,9 @@ dependencies = [
     "sqlalchemy>=2.0.35",
     "alembic>=1.13.3",
 
+    # PostgreSQL 驱动 (psycopg3, SQLAlchemy 2.0 时代默认, 不要再装 psycopg2)
+    "psycopg[binary]>=3.1",
+
     # JWT 签名 / 校验
     "pyjwt[crypto]>=2.9.0",
 

scripts/init_postgres.py

@@ -0,0 +1,189 @@
+"""Bootstrap PostgreSQL: 建用户 + 建库 + 授权 + 写 .env + 跑迁移。
+
+新机器初始化用。前置:已装 PostgreSQL 16 + 知道 postgres 超级用户密码。
+
+用法:
+    python scripts/init_postgres.py
+
+环境变量(可选,不填会交互式问):
+    PG_HOST          PG 主机, 默认 localhost
+    PG_PORT          PG 端口, 默认 5432
+    PG_SUPER_USER    超级用户, 默认 postgres
+    PG_SUPER_PASS    超级用户密码 (不填会 getpass 交互输入)
+    APP_DB_NAME      业务库名, 默认 shaguabijia
+    APP_DB_USER      业务用户名, 默认 shaguabijia_app
+    APP_DB_PASS      业务用户密码 (不填会自动生成强密码)
+
+幂等:已存在的用户/库不会重复建,已配的 .env 会被覆盖前提示。
+"""
+from __future__ import annotations
+
+import getpass
+import os
+import secrets
+import subprocess
+import sys
+from pathlib import Path
+
+try:
+    import psycopg
+    from psycopg import sql
+except ImportError:
+    print("❌ 缺 psycopg。先跑: pip install -e .")
+    sys.exit(1)
+
+
+ROOT = Path(__file__).resolve().parent.parent
+ENV_FILE = ROOT / ".env"
+ENV_EXAMPLE = ROOT / ".env.example"
+
+
+def env_or_input(key: str, prompt: str, default: str = "", secret: bool = False) -> str:
+    val = os.environ.get(key, "").strip()
+    if val:
+        return val
+    if secret:
+        return getpass.getpass(f"{prompt}: ").strip()
+    suffix = f" [{default}]" if default else ""
+    raw = input(f"{prompt}{suffix}: ").strip()
+    return raw or default
+
+
+def ensure_role(conn: psycopg.Connection, user: str, password: str) -> None:
+    # 注意: CREATE/ALTER USER 不支持参数化密码, 必须用 sql.Literal 内联
+    with conn.cursor() as cur:
+        cur.execute("SELECT 1 FROM pg_roles WHERE rolname=%s", (user,))
+        if cur.fetchone():
+            print(f"  · 用户 {user} 已存在, 重置密码")
+            cur.execute(
+                sql.SQL("ALTER USER {} WITH PASSWORD {}").format(
+                    sql.Identifier(user), sql.Literal(password)
+                )
+            )
+        else:
+            print(f"  · 创建用户 {user}")
+            cur.execute(
+                sql.SQL("CREATE USER {} WITH PASSWORD {}").format(
+                    sql.Identifier(user), sql.Literal(password)
+                )
+            )
+
+
+def ensure_database(conn: psycopg.Connection, db: str, owner: str) -> None:
+    with conn.cursor() as cur:
+        cur.execute("SELECT 1 FROM pg_database WHERE datname=%s", (db,))
+        if cur.fetchone():
+            print(f"  · 数据库 {db} 已存在")
+            return
+        print(f"  · 创建数据库 {db} (owner={owner}, encoding=UTF8)")
+        cur.execute(
+            sql.SQL("CREATE DATABASE {} OWNER {} ENCODING 'UTF8'").format(
+                sql.Identifier(db), sql.Identifier(owner)
+            )
+        )
+
+
+def grant_all(conn: psycopg.Connection, db: str, user: str) -> None:
+    with conn.cursor() as cur:
+        cur.execute(
+            sql.SQL("GRANT ALL PRIVILEGES ON DATABASE {} TO {}").format(
+                sql.Identifier(db), sql.Identifier(user)
+            )
+        )
+
+
+def write_env(database_url: str) -> None:
+    if not ENV_FILE.exists():
+        if ENV_EXAMPLE.exists():
+            ENV_FILE.write_text(ENV_EXAMPLE.read_text(encoding="utf-8"), encoding="utf-8")
+            print(f"  · 从 .env.example 复制出 .env")
+        else:
+            ENV_FILE.write_text("", encoding="utf-8")
+
+    lines = ENV_FILE.read_text(encoding="utf-8").splitlines()
+    new_lines = []
+    replaced = False
+    for line in lines:
+        if line.startswith("DATABASE_URL="):
+            old_url = line.split("=", 1)[1]
+            if old_url.strip() and old_url.strip() != database_url:
+                ans = input(f"\n.env 里已有 DATABASE_URL=\n  {old_url}\n覆盖吗? [y/N]: ").strip().lower()
+                if ans != "y":
+                    print("  · 保留原 DATABASE_URL")
+                    new_lines.append(line)
+                    replaced = True
+                    continue
+            new_lines.append(f"DATABASE_URL={database_url}")
+            replaced = True
+        else:
+            new_lines.append(line)
+    if not replaced:
+        new_lines.append(f"DATABASE_URL={database_url}")
+
+    ENV_FILE.write_text("\n".join(new_lines) + "\n", encoding="utf-8")
+    print(f"  · 写入 .env -> DATABASE_URL")
+
+
+def run_alembic_upgrade() -> bool:
+    print("\n[3/3] 跑 alembic upgrade head")
+    try:
+        subprocess.run(
+            [sys.executable, "-m", "alembic", "upgrade", "head"],
+            cwd=ROOT,
+            check=True,
+        )
+        return True
+    except subprocess.CalledProcessError as e:
+        print(f"❌ alembic 失败: {e}")
+        return False
+
+
+def main() -> int:
+    print("=" * 60)
+    print("PostgreSQL 初始化脚本 — shaguabijia-app-server")
+    print("=" * 60)
+
+    host = env_or_input("PG_HOST", "PG host", "localhost")
+    port = env_or_input("PG_PORT", "PG port", "5432")
+    super_user = env_or_input("PG_SUPER_USER", "PG superuser", "postgres")
+    super_pass = env_or_input("PG_SUPER_PASS", "PG superuser password", secret=True)
+    db_name = env_or_input("APP_DB_NAME", "App database name", "shaguabijia")
+    db_user = env_or_input("APP_DB_USER", "App database user", "shaguabijia_app")
+    db_pass = os.environ.get("APP_DB_PASS", "").strip()
+    if not db_pass:
+        db_pass = secrets.token_urlsafe(32)
+        print(f"  · 自动生成业务用户密码: {db_pass}")
+
+    print(f"\n[1/3] 连接 PG (host={host}:{port}, user={super_user})")
+    try:
+        conn = psycopg.connect(
+            host=host, port=int(port), user=super_user, password=super_pass,
+            dbname="postgres", autocommit=True,
+        )
+    except psycopg.OperationalError as e:
+        print(f"❌ 连不上 PG: {e}")
+        print("   检查 1) PG 服务是否在跑 2) 超级用户密码是否正确 3) 端口防火墙是否放行")
+        return 1
+
+    print("\n[2/3] 建用户 + 建库 + 授权")
+    with conn:
+        ensure_role(conn, db_user, db_pass)
+        ensure_database(conn, db_name, db_user)
+        grant_all(conn, db_name, db_user)
+
+    database_url = f"postgresql+psycopg://{db_user}:{db_pass}@{host}:{port}/{db_name}"
+    write_env(database_url)
+
+    if not run_alembic_upgrade():
+        return 1
+
+    print("\n" + "=" * 60)
+    print("✅ 全部完成")
+    print(f"   DATABASE_URL = {database_url}")
+    print("   下一步: 启动服务 -> ./run.sh 或 uvicorn app.main:app --reload --port 8770")
+    print("=" * 60)
+    return 0
+
+
+if __name__ == "__main__":
+    sys.exit(main())