运营后台后端:RBAC 权限管理 + 系统配置下发修复 + 比价记录店/商品搜索

一、RBAC 权限管理(角色 → 可见页面)
- 新增 admin_role 表 + 权限目录 permissions.py:角色持有一组页面 key,登录后左侧只展示这些页
- 内建角色 管理员(super_admin 全权锁定)/运营/财务/技术,页集对齐原型;key 承重(require_role 用),另加中文 label 展示
- 角色 CRUD 端点(仅 super_admin)+ 目录端点;登录/me 下发当前角色有效可见页
- admins 加删除、角色存在性校验;可复看已确定登录密码:UI 建的账号留存明文 plain_password,脚本建的超管不留存

二、系统配置下发修复
- 首页数据「保存即生效」:显式保存(apply_now)对 real/manual 直接落配置目标值、绕过只增不减护栏(修「改了 app 端不变」),护栏仍管自动 tick
- 首页轮播新增数据源三选一:mixed(真实优先+种子)/real(只真实)/seed(只种子),get_feed 分支 + /marquee-seeds/mode 端点
- 福利页 Tab 隐藏 任务/里程碑,及看广告的单次金币/每轮次数/信息流开关:CONFIG_DEFS 加 hidden + list_config 过滤,业务读取默认值不受影响

三、比价记录店/商品搜索
- comparison_record 加 product_names 派生列(从下单 items 拼商品名),迁移建列并回填历史行
- admin 比价记录列表店/商品分列可搜:走 product_names 普通列 LIKE,规避 SQLite JSON 中文 ensure_ascii 转义搜不到的坑

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

.env.example

@@ -27,6 +27,11 @@ JG_PRIVATE_KEY_PATH=./secrets/jverify_rsa_private.pem
 JG_VERIFY_ENDPOINT=https://api.verification.jpush.cn/v1/web/loginTokenVerify
 JG_REQUEST_TIMEOUT_SEC=15
 
+# ===== 无障碍保护存活监控(pull 后置检测;本期不接推送)=====
+HEARTBEAT_MONITOR_ENABLED=true
+HEARTBEAT_TIMEOUT_MINUTES=60
+HEARTBEAT_SCAN_INTERVAL_SEC=60
+
 # ===== 短信 (mock 模式) =====
 # mock = true 时,任意 6 位数字均通过,且 /sms/send 不真发短信(只 log)。
 # 后续接阿里云/腾讯云短信时,改成 false 并填供应商相关 key。
@@ -34,19 +39,51 @@ SMS_MOCK=true
 SMS_CODE_TTL_SEC=300
 SMS_SEND_INTERVAL_SEC=60
 
+# ===== 测试账号(release 包全流程联调用)=====
+# 配一个固定测试手机号,专供无 SIM 卡 / 不走一键登录时打通全流程:该号登录【免短信验证码】
+# (real 模式下也跳过校验)、每次登录【都重走新手引导】,并有【每日登录上限】防被人猜到号后脚本刷。
+# 逻辑见 app/core/test_account.py,与其他业务解耦。
+# ⚠️ 留空 = 关闭整功能(生产默认);要启用才填号(如 11111111111)。改完重启生效,随时可清空停用。
+TEST_ACCOUNT_PHONE=
+# 该测试号每日最多登录次数,当日超过即拒绝(429),次日归零。
+TEST_ACCOUNT_DAILY_LIMIT=500
+
 # ===== 美团联盟 CPS =====
 # 美团联盟后台 → 媒体管理 拿到的 appkey 和密钥
 MT_CPS_APP_KEY=
 MT_CPS_APP_SECRET=
 # 默认渠道追踪标识(sid),用于区分不同 app 的 CPS 数据
 MT_CPS_DEFAULT_SID=sgbjia
+# 美团调用走的代理。⚠️ 本机/内网开发直连美团会 SSL EOF,必须填本地代理(如 http://127.0.0.1:7897);
+# 线上国内服务器留空(=直连)。留空且本机直连失败时 /feed、/coupons、/top-sales 会返回空。
+MT_CPS_PROXY=
 
-# ===== Pricebot 上游 (领券业务透传目标) =====
-# 客户端调本服务的 /api/v1/coupon/step,我们透传到 pricebot-backend 的 /api/coupon/step。
+# ===== 京东联盟 CPS =====
+# 京东联盟/京东宙斯开放平台创建应用后填写。AUTH_KEY 是工具商授权 key,自有应用可留空。
+JD_UNION_APP_KEY=
+JD_UNION_APP_SECRET=
+JD_UNION_SITE_ID=
+JD_UNION_AUTH_KEY=
+
+# ===== Pricebot 上游 (领券/比价业务透传目标) =====
+# 客户端调本服务的 /api/v1/coupon/step 等,我们透传到 pricebot-backend。
 # 本地开发用 localhost:8000。生产部署改成内网地址(如 http://pricebot.internal:8000)。
 PRICEBOT_BASE_URL=http://localhost:8000
+# 【多实例】单机多进程部署时,填逗号分隔的实例列表(端口与 pricebot 集群对齐),透传层按
+# trace_id 一致性 hash 选实例 → 同一比价所有帧落同一进程(进程内维护 state,无需 Redis)。
+# 留空 = 单实例(用上面的 PRICEBOT_BASE_URL)。详见 pricebot-backend/docs/并发部署设计.md
+# PRICEBOT_INSTANCES=http://127.0.0.1:8001,http://127.0.0.1:8002,http://127.0.0.1:8003,http://127.0.0.1:8004,http://127.0.0.1:8005,http://127.0.0.1:8006
 # 领券单帧最多 wait 6s,加网络往返,30s 兜底
 PRICEBOT_REQUEST_TIMEOUT_SEC=30
+# 比价(intent/recognize + price/step)透传超时:大上下文 LLM + 逐帧 LLM,给 60s
+PRICEBOT_COMPARE_TIMEOUT_SEC=60
+
+# ===== 内部端点 (server→server, pricebot 上报价格观测) =====
+# pricebot 比价 done 后把各平台到手价 POST 到本服务 /internal/price-observation 落库
+# (price_observation 表,比价资产沉淀层),靠共享密钥头 X-Internal-Secret 校验。
+# 必须与 pricebot 侧的 INTERNAL_API_SECRET **同值**;留空 = 内部写端点关闭(返 503)。
+# 启用前两边都填同一高熵串:python -c "import secrets; print(secrets.token_urlsafe(48))"
+INTERNAL_API_SECRET=
 
 # ===== CORS =====
 # 逗号分隔,生产留空(只让 app 调,不开放 web)。本地开发可加 http://localhost:5173 之类
@@ -63,13 +100,40 @@ WXPAY_MCH_PRIVATE_KEY_PATH=./secrets/apiclient_key.pem
 WXPAY_PUBLIC_KEY_ID=PUB_KEY_ID_xxxxxxxx
 WXPAY_PUBLIC_KEY_PATH=./secrets/pub_key.pem
 WXPAY_TRANSFER_SCENE_ID=1000
+WXPAY_AUTH_NOTIFY_URL=
+WITHDRAW_AUTO_RECONCILE_ENABLED=false
+WITHDRAW_AUTO_RECONCILE_INTERVAL_SEC=300
+WITHDRAW_AUTO_RECONCILE_OLDER_THAN_MINUTES=15
+# 0 点自动兑金币开关(deploy/daily-exchange.timer 触发的脚本读它;false=脚本 no-op)。默认 true。
+AUTO_EXCHANGE_ENABLED=true
 
 # ===== 穿山甲激励视频(服务端发奖回调)=====
 # 看完激励视频后穿山甲服务器 S2S 回调本服务发金币(客户端不参与发奖)。
-# PANGLE_REWARD_SECRET 是穿山甲后台配置的"奖励校验密钥",用于验签,从后台取到后填这里;
-# 配齐并把 ENABLED=true 后,/api/v1/ad/pangle-callback 才受理回调(否则 503)。
+# 穿山甲"奖励校验密钥"(m-key),验签用,从 GroMore 后台各广告位取到后填这里。
+# 配齐(任一非空)并把 ENABLED=true 后,/api/v1/ad/pangle-callback 才受理回调(否则 503)。
+# 每个激励位 m-key 不同但共用同一回调 URL → 各位分开一行配(留空的忽略);验签逐个试、任一通过即接受。
 PANGLE_CALLBACK_ENABLED=false
+# 测试应用 激励位 104099649
+PANGLE_REWARD_SECRET_TEST=
+# 测试应用 专属激励位 104127529
+PANGLE_REWARD_SECRET_TEST_DEDICATED=
+# 正式应用 激励位 104099389
+PANGLE_REWARD_SECRET_PROD=
+# (旧用法,仍兼容:单个或逗号分隔的多个 m-key,会与上面三个合并去重)
 PANGLE_REWARD_SECRET=
 # ⚠️ 仅本地联调:true 时开放 POST /api/v1/ad/test-grant,让 debug 客户端看完广告直接发奖,
 # 验证"看广告→金币到账"全链路(未部署公网、穿山甲 S2S 打不到本地时用)。生产必须 false(绕过反作弊)。
 AD_REWARD_TEST_GRANT_ENABLED=false
+
+# ===== 穿山甲 GroMore 数据 API(按天拉收益报表,供后台广告收益报表的「穿山甲后台收益」)=====
+# ⚠️ 与上面发奖回调的 m-key 是【两套不同凭证】:这三样在穿山甲后台「接入中心 → GroMore-API →
+# 聚合数据报告 API」文档页领取。只读拉取 GroMore 天级 revenue(预估)/ api_revenue(收益Api),
+# 不参与发奖。三样齐全才生效;留空 = scripts/sync_pangle_revenue 直接 no-op。
+# 子账号(role_id≠user_id)需主账号在「角色管理」授予「查看全部数据」权限,否则查不到收益(接口 118);
+# role_id 填成 = user_id 即查主账号数据。同步:线上每天 ~10:30 由 timer 跑 python -m scripts.sync_pangle_revenue。
+PANGLE_REPORT_USER_ID=0
+PANGLE_REPORT_ROLE_ID=0
+PANGLE_REPORT_SECURITY_KEY=
+# GroMore AppId(报表 site_id 维度)→ 应用环境;默认取现网两个应用,按需覆盖。
+PANGLE_REPORT_SITE_ID_PROD=5830519
+PANGLE_REPORT_SITE_ID_TEST=5832303

.gitignore

@@ -23,7 +23,17 @@ dist/
 *.db-journal
 *.db-shm
 *.db-wal
-data/
+# data/ 整体不入库(运行时数据/上传文件/大二进制)。例外:邀请落地页 dl.html 及其
+# 引用的静态插画(coupon-page-bg.png 底图 + sb-brand.png logo)——既是生产落地页资产
+# 又是本地测试资产,纳入 git 便于同事一致测试、且随部署进生产 /media(否则线上 404→落地页毛坯)。
+# (见 docs/邀请功能-实现原理与本地测试.md)。其余(avatars/ / *.apk / app.db 等)仍忽略。
+data/*
+!data/media/
+data/media/*
+!data/media/dl.html
+!data/media/taobao_landing.jpg
+!data/media/coupon-page-bg.png
+!data/media/sb-brand.png
 
 secrets/*
 !secrets/.gitkeep
@@ -37,3 +47,4 @@ secrets/*
 
 # 运行日志(run.sh 输出, 不入库)
 *.log
+logs/

CLAUDE.md

@@ -0,0 +1,116 @@
+# CLAUDE.md
+
+This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.
+
+## Project overview
+
+Shaguabijia (傻瓜比价) App backend — FastAPI + SQLAlchemy 2.0 + JWT. Covers user auth (Jiguang one-click / SMS), welfare wallet (coins/cash/signin/tasks/savings), WeChat Pay withdrawals, ad-reward callbacks (Pangle/GroMore S2S), Meituan CPS (coupon forwarding / price comparison), and an admin backend.
+
+## Commands
+
+```bash
+# Install
+pip install -e ".[dev]"
+
+# Run app server (port 8770, auto-migrates, auto-reload)
+./run.sh                    # or: uvicorn app.main:app --reload --port 8770
+
+# Run admin server (port 8771, separate process)
+uvicorn app.admin.main:admin_app --reload --port 8771
+
+# Database
+alembic upgrade head        # apply all migrations (idempotent)
+alembic revision --autogenerate -m "description"  # generate new migration
+
+# Tests
+pytest -q                   # all tests
+pytest tests/test_auth.py -q        # single file
+pytest -k "test_sms_login" -q       # single test by name
+
+# Lint
+ruff check .
+ruff check --fix .
+```
+
+## Architecture: two FastAPI apps
+
+This repo runs **two separate FastAPI processes** sharing the same `app/` codebase (models, repositories, integrations, config):
+
+| | App server | Admin server |
+|---|---|---|
+| Entry | `app/main.py` → `app:app` | `app/admin/main.py` → `admin_app` |
+| Port | 8770 | 8771 |
+| Auth | User JWT (`JWT_SECRET_KEY`) | Admin JWT (`ADMIN_JWT_SECRET`, independent) |
+| Audience | Mobile app clients | Internal admin dashboard |
+| Docs | `/docs` (non-prod only) | `/admin/docs` (non-prod only) |
+
+The two apps are intentionally decoupled — `app.main` never imports `app.admin`. Admin has its own auth chain (`app/admin/deps.py`, `app/admin/security.py`), role-based guards (`require_role`), and routers under `app/admin/routers/`.
+
+## Layered request flow
+
+```
+api/v1/  (thin: parse → delegate → respond + HTTP errors)
+  ├── integrations/   (external SDKs: signature, encryption, HTTP calls)
+  └── repositories/   (data access + transactions)
+        └── models/   (SQLAlchemy ORM, DeclarativeBase)
+```
+
+- **`api/v1/`**: Route handlers. Keep these thin — parse request, call repository or integration, return response. Never put business logic or external HTTP here.
+- **`api/deps.py`**: Shared FastAPI dependencies — `get_current_user` (Bearer JWT → User ORM object), `get_db` (request-scoped session).
+- **`integrations/`**: All external service logic — Jiguang REST + RSA decryption, WeChat Pay V3 signing/encryption, Meituan CPS gateway signing, Pangle callback signature verification, SMS sending. This is the layer you change when swapping vendors.
+- **`repositories/`**: Data access. Each file wraps SQLAlchemy queries + transactions for one domain (user, wallet, signin, savings, ad_reward, etc.). Some repositories also call integrations (e.g., `wallet.py` calls `integrations/wxpay.py` for withdrawals).
+- **`models/`**: ORM table definitions (SQLAlchemy 2.0 `Mapped` style, `DeclarativeBase`). Every new model must be imported in `app/models/__init__.py` so Alembic can discover it.
+- **`schemas/`**: Pydantic request/response contracts.
+- **`core/`**: Infrastructure — config (`pydantic-settings`), JWT (`security.py`), in-memory rate limiter (`ratelimit.py`), reward constants (`rewards.py`), logging setup, pricebot router (consistent-hash load balancing), withdraw reconcile worker.
+
+## Internal (server-to-server) endpoints
+
+Endpoints under `app/api/internal/` are for server-to-server communication (pricebot → app-server), NOT for clients. They use a shared secret header `X-Internal-Secret` (compared via `hmac.compare_digest`) instead of user JWT. If `INTERNAL_API_SECRET` is empty, these endpoints return 503.
+
+## Auth system
+
+- **User login**: Jiguang one-click (`integrations/jiguang.py` — REST token verification + RSA decryption with multi-padding retry) or SMS code (mock by default; `SMS_MOCK=true`).
+- **Tokens**: JWT access (2h) + refresh (30d). Both are JWT with `typ` claim (`"access"` vs `"refresh"`) to prevent refresh-as-access. See `core/security.py`.
+- **Admin auth**: Separate JWT secret (`ADMIN_JWT_SECRET`), 12h expiry, no refresh. Username + bcrypt password login. Role-based access via `require_role()` guard in `app/admin/deps.py` (`super_admin` bypasses all role checks).
+- **Rate limiting**: In-memory fixed-window by client IP (`core/ratelimit.py`). Single-worker only; disabled in tests via `RATE_LIMIT_ENABLED=false`.
+
+## Database
+
+- **Dev**: SQLite (`sqlite:///./data/app.db`), `check_same_thread=False`, no connection pool.
+- **Prod**: PostgreSQL — just change `DATABASE_URL` in `.env`. Pool size 10 + max overflow 20, pool_recycle 3600.
+- **Migrations**: Alembic with `render_as_batch` for SQLite compatibility. ~60+ migration files in `alembic/versions/` (filenames are descriptive, not hex prefixes). Migration chain uses `down_revision` within each file.
+- **New models**: Define in `app/models/`, import in `app/models/__init__.py`, then run `alembic revision --autogenerate`.
+
+## Config
+
+All config via `pydantic-settings` in `app/core/config.py`. Single `Settings` class with env vars / `.env` file. Access anywhere via `from app.core.config import settings`. Key patterns:
+- `*_configured` properties gate features gracefully (e.g., `mt_cps_configured`, `wxpay_configured`, `pangle_callback_configured`) — missing credentials → endpoints return empty/503 rather than crashing at startup.
+- Prod validation: `_enforce_prod_secrets` model validator blocks startup if `APP_ENV=prod` with weak JWT secrets.
+
+## Testing
+
+- `tests/conftest.py`: Sets env vars BEFORE imports, creates temp SQLite file, builds all tables with `Base.metadata.create_all()`, tears down with `drop_all()` + unlink.
+- External integrations are monkeypatched in tests (e.g., WeChat Pay, Jiguang, Pangle callbacks) — tests never make real HTTP calls.
+- `TestClient` from FastAPI is used for all tests. Rate limiting is disabled globally in tests.
+
+## Key integration details
+
+- **Jiguang one-click login**: REST call to verify `loginToken`, then RSA decrypt the returned phone number. Multiple padding schemes tried in order (PKCS1v15, OAEP with SHA1/SHA256) because Jiguang's encryption padding varies.
+- **WeChat Pay withdrawals**: V3 API merchant transfer to user WeChat balance. Lazy-loads merchant certificates from `secrets/`. Withdrawal flow: bind WeChat → create withdraw order → auto-reconcile worker polls pending orders.
+- **Pangle ad rewards**: S2S callback verification via SHA256 signature. Multiple `m-key` secrets supported (one per ad placement). Callback is idempotent by `trans_id`. Test grant endpoint (`AD_REWARD_TEST_GRANT_ENABLED`) for local debugging — must be false in prod.
+- **Meituan CPS**: Gateway signature-based API calls. Proxy support (`MT_CPS_PROXY`) for local dev (direct connection causes SSL EOF). Coupon endpoints gracefully return empty when credentials are missing.
+- **Pricebot forwarding**: `/api/v1/coupon/step` and `/api/v1/compare/*` proxy to pricebot-backend. Multi-instance support with consistent-hash routing by `trace_id` (see `core/pricebot_router.py`).
+- **CPS redirect**: `/c/{code}` is a public (no auth) short-link redirect — records a click then 302s to Meituan. Click recording failure never blocks the redirect.
+
+## Money and units
+
+All monetary amounts are in **cents** (`*_cents` fields). Coins/gold have their own unit. Conversion constants are in `core/rewards.py`.
+
+## Scripts
+
+Key operational scripts in `scripts/`:
+- `migrate.sh` — run migrations standalone
+- `create_admin.py` — create admin user
+- `daily_auto_exchange.py` — auto-convert coins to cash (triggered by systemd timer)
+- `reconcile_withdraws.py` — reconcile withdrawal orders with WeChat Pay
+- `sim_pangle_callback.py` — simulate Pangle S2S callback for testing

alembic/versions/0cf18d590b1d_merge_coupon_state_and_invite_heads.py

@@ -0,0 +1,26 @@
+"""merge coupon_state and invite heads
+
+Revision ID: 0cf18d590b1d
+Revises: coupon_state_tables, invite_code_and_relation
+Create Date: 2026-06-08 05:49:29.604571
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '0cf18d590b1d'
+down_revision: Union[str, Sequence[str], None] = ('coupon_state_tables', 'invite_code_and_relation')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/11a1d08c6f55_merge_meituan_coupon_与_ops_price_.py

@@ -0,0 +1,26 @@
+"""merge meituan_coupon 与 ops/price_observation 迁移头
+
+Revision ID: 11a1d08c6f55
+Revises: meituan_coupon_table, opsrename01
+Create Date: 2026-06-08 00:06:01.633796
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '11a1d08c6f55'
+down_revision: Union[str, Sequence[str], None] = ('meituan_coupon_table', 'opsrename01')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/1699fc2c069f_add_analytics_event_table.py

@@ -0,0 +1,65 @@
+"""add analytics_event table
+
+Revision ID: 1699fc2c069f
+Revises: bcfcaf07152b
+Create Date: 2026-06-26 16:35:16.133975
+
+"""
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = '1699fc2c069f'
+down_revision: str | Sequence[str] | None = 'bcfcaf07152b'
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table('analytics_event',
+    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+    sa.Column('event', sa.String(length=64), nullable=False),
+    sa.Column('props', sa.JSON(), nullable=True),
+    sa.Column('device_id', sa.String(length=64), nullable=False),
+    sa.Column('user_id', sa.Integer(), nullable=True),
+    sa.Column('session_id', sa.String(length=64), nullable=True),
+    sa.Column('client_ts', sa.BigInteger(), nullable=False),
+    sa.Column('sent_at', sa.BigInteger(), nullable=True),
+    sa.Column('page', sa.String(length=64), nullable=True),
+    sa.Column('client_ip', sa.String(length=64), nullable=True),
+    sa.Column('oem', sa.String(length=32), nullable=True),
+    sa.Column('os', sa.String(length=32), nullable=True),
+    sa.Column('model', sa.String(length=64), nullable=True),
+    sa.Column('app_ver', sa.String(length=32), nullable=True),
+    sa.Column('network', sa.String(length=16), nullable=True),
+    sa.Column('channel', sa.String(length=32), nullable=True),
+    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+    sa.PrimaryKeyConstraint('id')
+    )
+    with op.batch_alter_table('analytics_event', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_analytics_event_created_at'), ['created_at'], unique=False)
+        batch_op.create_index(batch_op.f('ix_analytics_event_device_id'), ['device_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_analytics_event_event'), ['event'], unique=False)
+        batch_op.create_index(batch_op.f('ix_analytics_event_session_id'), ['session_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_analytics_event_user_id'), ['user_id'], unique=False)
+
+    # 注:autogenerate 顺带检出 ad_ecpm/cps_*/invite_fingerprint 的历史索引漂移,
+    # 与本次「新增埋点表」无关,已手动移除,避免本迁移误改他人表。
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    with op.batch_alter_table('analytics_event', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_analytics_event_user_id'))
+        batch_op.drop_index(batch_op.f('ix_analytics_event_session_id'))
+        batch_op.drop_index(batch_op.f('ix_analytics_event_event'))
+        batch_op.drop_index(batch_op.f('ix_analytics_event_device_id'))
+        batch_op.drop_index(batch_op.f('ix_analytics_event_created_at'))
+
+    op.drop_table('analytics_event')
+    # ### end Alembic commands ###

alembic/versions/45047b5a884c_merge_store_mapping_jd_dl_invalid_and_.py

@@ -0,0 +1,26 @@
+"""merge store_mapping_jd_dl_invalid and ad_revenue heads (0.1.2)
+
+Revision ID: 45047b5a884c
+Revises: d4e68464761d, store_mapping_jd_dl_invalid
+Create Date: 2026-06-16 01:38:26.273226
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '45047b5a884c'
+down_revision: Union[str, Sequence[str], None] = ('d4e68464761d', 'store_mapping_jd_dl_invalid')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/4dc2af7ebe74_merge_device_65_and_comparison_token_.py

@@ -0,0 +1,26 @@
+"""merge device#65 and comparison_token_cols heads
+
+Revision ID: 4dc2af7ebe74
+Revises: comparison_token_cols, ad_feed_reward_trace_id
+Create Date: 2026-06-23 17:57:54.083531
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '4dc2af7ebe74'
+down_revision: Union[str, Sequence[str], None] = ('comparison_token_cols', 'ad_feed_reward_trace_id')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/761ef181ce7c_merge_jd_cps_order_fields_and_coupon_.py

@@ -0,0 +1,26 @@
+"""merge jd_cps_order_fields and coupon_session_origin_package heads
+
+Revision ID: 761ef181ce7c
+Revises: coupon_session_origin_package, jd_cps_order_fields
+Create Date: 2026-07-01 13:52:16.068808
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '761ef181ce7c'
+down_revision: Union[str, Sequence[str], None] = ('coupon_session_origin_package', 'jd_cps_order_fields')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/7db22acee504_merge_invite_cash_analytics_device_heads.py

@@ -0,0 +1,26 @@
+"""merge invite_cash + analytics + device heads
+
+Revision ID: 7db22acee504
+Revises: c2874d2bf705, device_first_protected_at, invite_cash_compare_reward
+Create Date: 2026-06-27 03:06:52.594401
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '7db22acee504'
+down_revision: Union[str, Sequence[str], None] = ('c2874d2bf705', 'device_first_protected_at', 'invite_cash_compare_reward')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/8ac524a8ea02_merge_comparison_and_savings_heads.py

@@ -0,0 +1,26 @@
+"""merge comparison and savings heads
+
+Revision ID: 8ac524a8ea02
+Revises: d4e5f6a7b8c9, savings_report_fields
+Create Date: 2026-06-02 09:53:06.924912
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '8ac524a8ea02'
+down_revision: Union[str, Sequence[str], None] = ('d4e5f6a7b8c9', 'savings_report_fields')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/9258bddde4ea_新增_price_report_上报更低价表.py

@@ -0,0 +1,65 @@
+"""新增 price_report 上报更低价表
+
+Revision ID: 9258bddde4ea
+Revises: ad60a1b2c3d4
+Create Date: 2026-06-05 10:15:51.508598
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '9258bddde4ea'
+down_revision: Union[str, Sequence[str], None] = 'ad60a1b2c3d4'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    op.create_table('price_report',
+    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+    sa.Column('user_id', sa.Integer(), nullable=False),
+    sa.Column('comparison_record_id', sa.Integer(), nullable=True),
+    sa.Column('store_name', sa.String(length=128), nullable=True),
+    sa.Column('dish_summary', sa.String(length=256), nullable=True),
+    sa.Column('original_platform_id', sa.String(length=32), nullable=True),
+    sa.Column('original_platform_name', sa.String(length=32), nullable=True),
+    sa.Column('original_price_cents', sa.Integer(), nullable=True),
+    sa.Column('reported_platform_id', sa.String(length=32), nullable=False),
+    sa.Column('reported_platform_name', sa.String(length=32), nullable=False),
+    sa.Column('reported_price_cents', sa.Integer(), nullable=False),
+    sa.Column('images', sa.JSON(), nullable=False),
+    sa.Column('status', sa.String(length=16), nullable=False),
+    sa.Column('reject_reason', sa.String(length=256), nullable=True),
+    sa.Column('reward_coins', sa.Integer(), nullable=True),
+    sa.Column('reviewed_at', sa.DateTime(timezone=True), nullable=True),
+    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+    sa.ForeignKeyConstraint(['comparison_record_id'], ['comparison_record.id'], ),
+    sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+    sa.PrimaryKeyConstraint('id')
+    )
+    with op.batch_alter_table('price_report', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_price_report_comparison_record_id'), ['comparison_record_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_report_created_at'), ['created_at'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_report_status'), ['status'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_report_user_id'), ['user_id'], unique=False)
+    # 注:autogenerate 另检测到「删除 order_record 表」——那是已降级为审计的历史表
+    #     (model 已移除、库表保留),与本次无关,手动剔除,避免误删他人审计数据。
+    # ### end Alembic commands ###
+
+
+def downgrade() -> None:
+    # ### commands auto generated by Alembic - please adjust! ###
+    # (对称:upgrade 未删 order_record,downgrade 也不重建它)
+    with op.batch_alter_table('price_report', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_price_report_user_id'))
+        batch_op.drop_index(batch_op.f('ix_price_report_status'))
+        batch_op.drop_index(batch_op.f('ix_price_report_created_at'))
+        batch_op.drop_index(batch_op.f('ix_price_report_comparison_record_id'))
+
+    op.drop_table('price_report')
+    # ### end Alembic commands ###

alembic/versions/9b894f5fff05_merge_user_force_onboarding_and_.py

@@ -0,0 +1,26 @@
+"""merge user_force_onboarding and comparison_idx_coupon_daily heads
+
+Revision ID: 9b894f5fff05
+Revises: d4b87c5847de, user_force_onboarding
+Create Date: 2026-06-10 22:49:02.248506
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '9b894f5fff05'
+down_revision: Union[str, Sequence[str], None] = ('d4b87c5847de', 'user_force_onboarding')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/a8c47fc4dc39_merge_invite_fingerprint_and_ad_reward_.py

@@ -0,0 +1,26 @@
+"""merge invite_fingerprint and ad_reward heads
+
+Revision ID: a8c47fc4dc39
+Revises: invite_fingerprint_table, 044dce6e9b1f
+Create Date: 2026-06-10 01:03:03.699443
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'a8c47fc4dc39'
+down_revision: Union[str, Sequence[str], None] = ('invite_fingerprint_table', '044dce6e9b1f')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/ad_ecpm_record.py

@@ -0,0 +1,47 @@
+"""ad_ecpm_record table (广告展示 eCPM 上报记录,内部收益统计/对账)
+
+Revision ID: a1b2c3d4e5f6
+Revises: f01db5d77dac
+Create Date: 2026-05-31 11:30:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'a1b2c3d4e5f6'
+down_revision: Union[str, Sequence[str], None] = 'f01db5d77dac'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'ad_ecpm_record',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('ad_type', sa.String(length=32), nullable=False),
+        sa.Column('adn', sa.String(length=32), nullable=True),
+        sa.Column('slot_id', sa.String(length=64), nullable=True),
+        sa.Column('ecpm_raw', sa.String(length=32), nullable=False),
+        sa.Column('report_date', sa.String(length=10), nullable=False),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+    )
+    with op.batch_alter_table('ad_ecpm_record', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_ad_ecpm_record_user_id'), ['user_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_ad_ecpm_record_report_date'), ['report_date'], unique=False)
+        batch_op.create_index(batch_op.f('ix_ad_ecpm_record_created_at'), ['created_at'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('ad_ecpm_record', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_ad_ecpm_record_created_at'))
+        batch_op.drop_index(batch_op.f('ix_ad_ecpm_record_report_date'))
+        batch_op.drop_index(batch_op.f('ix_ad_ecpm_record_user_id'))
+
+    op.drop_table('ad_ecpm_record')

alembic/versions/ad_feed_reward_session_id.py

@@ -0,0 +1,38 @@
+"""add ad_session_id to ad feed reward record
+
+Revision ID: ad_feed_reward_session
+Revises: coin_reward_phase2
+Create Date: 2026-06-09
+"""
+
+from __future__ import annotations
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision = "ad_feed_reward_session"
+down_revision = "coin_reward_phase2"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    op.add_column(
+        "ad_feed_reward_record",
+        sa.Column("ad_session_id", sa.String(length=64), nullable=True),
+    )
+    op.create_index(
+        op.f("ix_ad_feed_reward_record_ad_session_id"),
+        "ad_feed_reward_record",
+        ["ad_session_id"],
+        unique=False,
+    )
+
+
+def downgrade() -> None:
+    op.drop_index(
+        op.f("ix_ad_feed_reward_record_ad_session_id"),
+        table_name="ad_feed_reward_record",
+    )
+    op.drop_column("ad_feed_reward_record", "ad_session_id")

alembic/versions/ad_feed_reward_trace_id.py

@@ -0,0 +1,42 @@
+"""ad_feed_reward_record.trace_id (比价看广告金币归属到比价记录)
+
+Revision ID: ad_feed_reward_trace_id
+Revises: device_kill_alert_pending
+Create Date: 2026-06-21
+
+比价等候期信息流广告(feed_ad_reward)结算时,客户端带上本场比价 trace_id 落此列;
+比价记录页按 trace_id 聚合本场实发金币,显示「比价赚 N 金币」。领券/福利/旧客户端 = NULL。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'ad_feed_reward_trace_id'
+down_revision: Union[str, Sequence[str], None] = 'device_kill_alert_pending'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # SQLite 下 ADD COLUMN(可空) 与 CREATE INDEX 均原生支持,无需 batch_alter_table。
+    op.add_column(
+        'ad_feed_reward_record',
+        sa.Column('trace_id', sa.String(length=64), nullable=True),
+    )
+    op.create_index(
+        op.f('ix_ad_feed_reward_record_trace_id'),
+        'ad_feed_reward_record',
+        ['trace_id'],
+        unique=False,
+    )
+
+
+def downgrade() -> None:
+    op.drop_index(
+        op.f('ix_ad_feed_reward_record_trace_id'),
+        table_name='ad_feed_reward_record',
+    )
+    op.drop_column('ad_feed_reward_record', 'trace_id')

alembic/versions/ad_pangle_daily_revenue.py

@@ -0,0 +1,63 @@
+"""ad_pangle_daily_revenue: 穿山甲 GroMore 天级收益报表(后台结算口径)
+
+新建表存放从 GroMore 数据 API 按天拉取的收益(revenue 预估 + api_revenue 收益Api),
+粒度 = 日期 × 应用(app_env) × 代码位(our_code_id) × 广告源(adn)。供广告收益报表的
+汇总/趋势级展示「穿山甲后台收益」,与客户端自报 eCPM 折算的预估互为对照。
+
+Revision ID: ad_pangle_daily_revenue
+Revises: 7db22acee504
+Create Date: 2026-06-28
+"""
+
+from __future__ import annotations
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision = "ad_pangle_daily_revenue"
+down_revision = "7db22acee504"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        "ad_pangle_daily_revenue",
+        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
+        sa.Column("report_date", sa.String(length=10), nullable=False),
+        sa.Column("app_env", sa.String(length=16), nullable=False),
+        sa.Column("site_id", sa.String(length=32), nullable=True),
+        sa.Column("our_code_id", sa.String(length=64), nullable=False),
+        sa.Column("adn", sa.String(length=16), nullable=False, server_default=""),
+        sa.Column("revenue_yuan", sa.Float(), nullable=False, server_default="0"),
+        sa.Column("api_revenue_yuan", sa.Float(), nullable=True),
+        sa.Column("ecpm", sa.String(length=32), nullable=True),
+        sa.Column("impressions", sa.Integer(), nullable=False, server_default="0"),
+        sa.Column("currency", sa.String(length=8), nullable=False, server_default="cny"),
+        sa.Column(
+            "synced_at",
+            sa.DateTime(timezone=True),
+            server_default=sa.func.now(),
+            nullable=False,
+        ),
+        sa.UniqueConstraint(
+            "report_date", "app_env", "our_code_id", "adn", name="uq_ad_pangle_daily"
+        ),
+    )
+    op.create_index(
+        "ix_ad_pangle_daily_revenue_report_date",
+        "ad_pangle_daily_revenue",
+        ["report_date"],
+    )
+    op.create_index(
+        "ix_ad_pangle_daily_revenue_our_code_id",
+        "ad_pangle_daily_revenue",
+        ["our_code_id"],
+    )
+
+
+def downgrade() -> None:
+    op.drop_index("ix_ad_pangle_daily_revenue_our_code_id", table_name="ad_pangle_daily_revenue")
+    op.drop_index("ix_ad_pangle_daily_revenue_report_date", table_name="ad_pangle_daily_revenue")
+    op.drop_table("ad_pangle_daily_revenue")

alembic/versions/ad_revenue_report_cols.py

@@ -0,0 +1,39 @@
+"""ad revenue report columns: app_env + our_code_id
+
+给 ad_ecpm_record / ad_reward_record / ad_feed_reward_record 各加两列:
+- app_env:我们的穿山甲应用环境(prod=傻瓜比价正式 / test=测试应用)
+- our_code_id:我们在穿山甲后台配置的代码位 ID(104xxx,非底层 mediation rit)
+
+供「广告收益报表」按 用户/日期/广告类型/应用/代码位 聚合 展示条数/收益/金币。
+旧数据这两列为 NULL(报表里来源列留空),新数据由客户端上报/发奖时回填。
+
+Revision ID: ad_revenue_report_cols
+Revises: coupon_engage_per_package
+Create Date: 2026-06-15
+"""
+
+from __future__ import annotations
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision = "ad_revenue_report_cols"
+down_revision = "coupon_engage_per_package"
+branch_labels = None
+depends_on = None
+
+
+_TABLES = ("ad_ecpm_record", "ad_reward_record", "ad_feed_reward_record")
+
+
+def upgrade() -> None:
+    for table in _TABLES:
+        op.add_column(table, sa.Column("app_env", sa.String(length=16), nullable=True))
+        op.add_column(table, sa.Column("our_code_id", sa.String(length=64), nullable=True))
+
+
+def downgrade() -> None:
+    for table in _TABLES:
+        op.drop_column(table, "our_code_id")
+        op.drop_column(table, "app_env")

alembic/versions/add_feed_scene.py

@@ -0,0 +1,34 @@
+"""add feed_scene to ad_feed_reward_record
+
+信息流广告点位场景:comparison(比价等待) / coupon(领券) / welfare(福利页)。
+比价与领券共用同一个信息流代码位(AdConfig.feedCodeId),slot_id / our_code_id 都分不出来,
+只能由客户端在各调用点(PriceBotService / CouponForegroundService / WelfareScreen)显式打标。
+可空:历史记录与未升级客户端为 NULL = 未分类。
+
+Revision ID: add_feed_scene
+Revises: drop_force_onboarding
+Create Date: 2026-06-20 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "add_feed_scene"
+down_revision: Union[str, Sequence[str], None] = "drop_force_onboarding"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.add_column(
+        "ad_feed_reward_record",
+        sa.Column("feed_scene", sa.String(length=16), nullable=True),
+    )
+
+
+def downgrade() -> None:
+    op.drop_column("ad_feed_reward_record", "feed_scene")

alembic/versions/admin_role_table.py

@@ -0,0 +1,69 @@
+"""admin_role 表(RBAC 角色 → 可见页面)+ 种子内建角色
+
+新增后台 RBAC:角色持有一组页面 key,登录后左侧只展示这些页。super_admin 内建全权(pages 存空、
+effective_pages 特判为全部)、不可编辑删除;operator/finance 播默认页集,可由 super_admin 增删改。
+admin_user.role 弱引用本表 name。全新环境顺序应用即得三条内建角色。
+
+Revision ID: admin_role_table
+Revises: comparison_product_names
+Create Date: 2026-07-04 00:00:00.000000
+"""
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+from alembic import op
+
+revision: str = "admin_role_table"
+down_revision: str | Sequence[str] | None = "comparison_product_names"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+_JSON = sa.JSON().with_variant(postgresql.JSONB(), "postgresql")
+
+# 与 app/admin/permissions.py 的 BUILTIN_ROLES 同口径(迁移内联一份,不耦合 app 常量)。
+# 页集对齐 Prototypes/dashboard/permissions.md 的 ROLES。key 承重(require_role),label 为展示名。
+_BUILTIN = [
+    {"name": "super_admin", "label": "管理员", "pages": [], "is_builtin": True},
+    {"name": "operator", "label": "运营", "pages": [
+        "dashboard", "coupon-data", "ad-revenue-report", "comparison-records",
+        "cps", "device-liveness", "price-reports", "feedbacks",
+    ], "is_builtin": False},
+    {"name": "finance", "label": "财务", "pages": [
+        "dashboard", "ad-revenue-report", "cps", "withdraws",
+    ], "is_builtin": False},
+    {"name": "tech", "label": "技术", "pages": [
+        "dashboard", "device-liveness", "config", "ad-revenue", "event-logs", "audit-logs",
+    ], "is_builtin": False},
+]
+
+
+def upgrade() -> None:
+    op.create_table(
+        "admin_role",
+        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
+        sa.Column("name", sa.String(length=32), nullable=False),
+        sa.Column("label", sa.String(length=32), nullable=False, server_default=""),
+        sa.Column("pages", _JSON, nullable=False),
+        sa.Column("is_builtin", sa.Boolean(), nullable=False, server_default=sa.false()),
+        sa.Column(
+            "created_at", sa.DateTime(timezone=True),
+            server_default=sa.func.now(), nullable=False,
+        ),
+    )
+    op.create_index("ix_admin_role_name", "admin_role", ["name"], unique=True)
+
+    tbl = sa.table(
+        "admin_role",
+        sa.column("name", sa.String),
+        sa.column("label", sa.String),
+        sa.column("pages", _JSON),
+        sa.column("is_builtin", sa.Boolean),
+    )
+    op.bulk_insert(tbl, _BUILTIN)
+
+
+def downgrade() -> None:
+    op.drop_index("ix_admin_role_name", table_name="admin_role")
+    op.drop_table("admin_role")

alembic/versions/admin_tables.py

@@ -0,0 +1,65 @@
+"""admin_user + admin_audit_log tables (运营 admin 后台:账号 + 操作审计)
+
+Revision ID: ad60a1b2c3d4
+Revises: 8ac524a8ea02
+Create Date: 2026-06-03 10:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'ad60a1b2c3d4'
+down_revision: Union[str, Sequence[str], None] = '8ac524a8ea02'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'admin_user',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('username', sa.String(length=64), nullable=False),
+        sa.Column('password_hash', sa.String(length=255), nullable=False),
+        sa.Column('role', sa.String(length=20), nullable=False, server_default='operator'),
+        sa.Column('status', sa.String(length=20), nullable=False, server_default='active'),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('last_login_at', sa.DateTime(timezone=True), nullable=True),
+        sa.PrimaryKeyConstraint('id'),
+    )
+    with op.batch_alter_table('admin_user', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_admin_user_username'), ['username'], unique=True)
+
+    op.create_table(
+        'admin_audit_log',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('admin_id', sa.Integer(), nullable=False),
+        sa.Column('admin_username', sa.String(length=64), nullable=False),
+        sa.Column('action', sa.String(length=64), nullable=False),
+        sa.Column('target_type', sa.String(length=32), nullable=False),
+        sa.Column('target_id', sa.String(length=64), nullable=True),
+        sa.Column('detail', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=True),
+        sa.Column('ip', sa.String(length=64), nullable=True),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.ForeignKeyConstraint(['admin_id'], ['admin_user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+    )
+    with op.batch_alter_table('admin_audit_log', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_admin_audit_log_admin_id'), ['admin_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_admin_audit_log_action'), ['action'], unique=False)
+        batch_op.create_index(batch_op.f('ix_admin_audit_log_created_at'), ['created_at'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('admin_audit_log', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_admin_audit_log_created_at'))
+        batch_op.drop_index(batch_op.f('ix_admin_audit_log_action'))
+        batch_op.drop_index(batch_op.f('ix_admin_audit_log_admin_id'))
+    op.drop_table('admin_audit_log')
+
+    with op.batch_alter_table('admin_user', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_admin_user_username'))
+    op.drop_table('admin_user')

alembic/versions/admin_user_plain_password.py

@@ -0,0 +1,29 @@
+"""admin_user 加 plain_password 列(明文登录密码,仅后台 UI 建/重置的账号留存)
+
+权限管理页需能复看某成员已确定的登录密码转交本人。系统只存哈希无法反推,故对「后台 UI 创建/重置」
+的管理员额外留存一份明文;脚本/起后台建的超管为 None(前端不显示密码)。旧账号无此列值 → 同样不显示。
+
+Revision ID: admin_user_plain_password
+Revises: admin_role_table
+Create Date: 2026-07-04 00:00:00.000000
+"""
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+revision: str = "admin_user_plain_password"
+down_revision: str | Sequence[str] | None = "admin_role_table"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("admin_user", schema=None) as batch_op:
+        batch_op.add_column(sa.Column("plain_password", sa.String(length=128), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("admin_user", schema=None) as batch_op:
+        batch_op.drop_column("plain_password")

alembic/versions/app_config_table.py

@@ -0,0 +1,33 @@
+"""app_config table (运营可配置项:rewards 常量等后台化)
+
+Revision ID: cfg1a2b3c4d5
+Revises: ad60a1b2c3d4
+Create Date: 2026-06-04 12:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'cfg1a2b3c4d5'
+down_revision: Union[str, Sequence[str], None] = 'ad60a1b2c3d4'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'app_config',
+        sa.Column('key', sa.String(length=64), nullable=False),
+        sa.Column('value', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=False),
+        sa.Column('updated_by_admin_id', sa.Integer(), nullable=True),
+        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.PrimaryKeyConstraint('key'),
+    )
+
+
+def downgrade() -> None:
+    op.drop_table('app_config')

alembic/versions/b3f1a2c4d5e6_user_username_and_default_nickname.py

@@ -0,0 +1,79 @@
+"""user.username(对外展示账号 ID)+ 默认昵称,并回填存量用户
+
+Revision ID: b3f1a2c4d5e6
+Revises: 45047b5a884c
+Create Date: 2026-06-16 12:00:00.000000
+
+加 user.username(11 位纯数字、首位非 1 与手机号天然区分、全局唯一)。存量用户:
+生成唯一 username,昵称为空的补 9 位字母数字随机昵称。
+迁移自包含(不 import app 业务代码,逻辑冻结为当时快照)。
+"""
+import secrets
+import string
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'b3f1a2c4d5e6'
+down_revision: Union[str, Sequence[str], None] = '45047b5a884c'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+_USERNAME_FIRST = "23456789"      # 首位避前导 0、避 1(手机号都以 1 开头)
+_USERNAME_DIGITS = "0123456789"
+_NICKNAME_ALPHABET = string.ascii_letters + string.digits
+
+
+def _gen_username() -> str:
+    return secrets.choice(_USERNAME_FIRST) + "".join(
+        secrets.choice(_USERNAME_DIGITS) for _ in range(10)
+    )
+
+
+def _gen_nickname() -> str:
+    return "".join(secrets.choice(_NICKNAME_ALPHABET) for _ in range(9))
+
+
+def upgrade() -> None:
+    # 1. 先加可空列(存量行此刻还没值)
+    with op.batch_alter_table('user', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('username', sa.String(length=11), nullable=True))
+
+    # 2. 回填存量:每人一个唯一 username;昵称为空的补随机昵称。
+    #    表里 username 原本全空,本批用 used 防互撞即可(无既有非空值需规避)。
+    #    "user" 是 PostgreSQL 保留字,必须加双引号(SQLite 也接受双引号标识符)。
+    bind = op.get_bind()
+    rows = bind.execute(sa.text('SELECT id, nickname FROM "user"')).fetchall()
+    used = set()
+    for row in rows:
+        uid, nick = row[0], row[1]
+        while True:
+            uname = _gen_username()
+            if uname not in used:
+                used.add(uname)
+                break
+        if nick is None or not str(nick).strip():
+            bind.execute(
+                sa.text('UPDATE "user" SET username = :u, nickname = :n WHERE id = :i'),
+                {"u": uname, "n": _gen_nickname(), "i": uid},
+            )
+        else:
+            bind.execute(
+                sa.text('UPDATE "user" SET username = :u WHERE id = :i'),
+                {"u": uname, "i": uid},
+            )
+
+    # 3. 收紧:NOT NULL + 唯一索引(对齐模型 unique=True, index=True)
+    with op.batch_alter_table('user', schema=None) as batch_op:
+        batch_op.alter_column('username', existing_type=sa.String(length=11), nullable=False)
+        batch_op.create_index('ix_user_username', ['username'], unique=True)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('user', schema=None) as batch_op:
+        batch_op.drop_index('ix_user_username')
+        batch_op.drop_column('username')

alembic/versions/b7e2c1a9f4d3_comparison_record_best_deeplink.py

@@ -0,0 +1,32 @@
+"""comparison_record 加 best_deeplink(再次比价直达商家深链)
+
+Revision ID: b7e2c1a9f4d3
+Revises: 9258bddde4ea
+Create Date: 2026-06-05
+
+「再次比价」要直达上次最低价平台的商家/商品页。深链(link)在比价时客户端已从剪贴板采到
+(collectedLinks[best_index]),本列把它落库;再次比价时写剪贴板 + launch 该平台 App 直达。
+旧记录无此列值(None) → 前端降级为打开对应 App 首页。
+
+手写迁移(只加一列):autogenerate 会误报"删 order_record"(降级为审计的历史表,model 已移除、
+库表保留),手写规避该噪音。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+revision: str = "b7e2c1a9f4d3"
+down_revision: Union[str, Sequence[str], None] = "9258bddde4ea"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("comparison_record", schema=None) as batch_op:
+        batch_op.add_column(sa.Column("best_deeplink", sa.String(length=1024), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("comparison_record", schema=None) as batch_op:
+        batch_op.drop_column("best_deeplink")

alembic/versions/bcfcaf07152b_merge_meituan_coupon_image_meta_and_.py

@@ -0,0 +1,26 @@
+"""merge meituan_coupon_image_meta and comparison/ad heads
+
+Revision ID: bcfcaf07152b
+Revises: 4dc2af7ebe74, meituan_coupon_image_meta
+Create Date: 2026-06-24 20:34:09.475641
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'bcfcaf07152b'
+down_revision: Union[str, Sequence[str], None] = ('4dc2af7ebe74', 'meituan_coupon_image_meta')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/c2874d2bf705_add_ad_type_to_ad_feed_reward_and_feed_.py

@@ -0,0 +1,46 @@
+"""add ad_type to ad_feed_reward and feed_scene to ad_ecpm
+
+把信息流广告全面改造成 Draw 信息流(draw):
+- ad_feed_reward_record.ad_type:广告形态 feed(信息流) / draw(Draw 信息流)。可空,旧数据 NULL
+  一律视为 feed(向后兼容);每日上限与因子2(LT)仍按本表全表 unit 累计,不按 ad_type 拆。
+- ad_ecpm_record.feed_scene:点位场景 comparison(比价) / coupon(领券) / welfare(福利),供广告
+  收益报表区分比价/领券 Draw 收益;仅信息流/Draw 上报,激励视频为 NULL。
+
+注:autogenerate 会顺带探测到 analytics_event / cps_* / invite_fingerprint 等无关索引差异(本地库与
+metadata 漂移、analytics 模型尚未并入 __init__),与本次改动无关,已手工剔除——本迁移只 add 两列。
+SQLite 经 env.py 的 render_as_batch 自动走 batch_alter_table 重建表。
+
+Revision ID: c2874d2bf705
+Revises: 1699fc2c069f
+Create Date: 2026-06-26 16:48:52.158609
+
+"""
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "c2874d2bf705"
+down_revision: str | Sequence[str] | None = "1699fc2c069f"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    # 信息流发奖记录:新增广告形态 feed/draw(可空,旧数据 NULL=feed)
+    op.add_column(
+        "ad_feed_reward_record",
+        sa.Column("ad_type", sa.String(length=16), nullable=True),
+    )
+    # eCPM 展示上报:新增点位场景 comparison/coupon/welfare(可空,激励视频/旧数据 NULL)
+    op.add_column(
+        "ad_ecpm_record",
+        sa.Column("feed_scene", sa.String(length=16), nullable=True),
+    )
+
+
+def downgrade() -> None:
+    op.drop_column("ad_ecpm_record", "feed_scene")
+    op.drop_column("ad_feed_reward_record", "ad_type")

alembic/versions/ceb286289426_merge_add_feed_scene_and_launch_confirm_.py

@@ -0,0 +1,26 @@
+"""merge add_feed_scene and launch_confirm_sample heads
+
+Revision ID: ceb286289426
+Revises: add_feed_scene, launch_confirm_sample_table
+Create Date: 2026-06-20 23:52:57.853400
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'ceb286289426'
+down_revision: Union[str, Sequence[str], None] = ('add_feed_scene', 'launch_confirm_sample_table')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/coin_reward_points_phase1.py

@@ -0,0 +1,76 @@
+"""coin reward points phase 1 tables
+
+Revision ID: coin_reward_phase1
+Revises: pstat3growth
+Create Date: 2026-06-07 15:30:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'coin_reward_phase1'
+down_revision: Union[str, Sequence[str], None] = 'pstat3growth'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'signin_boost_record',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('signin_date', sa.Date(), nullable=False),
+        sa.Column('coin_awarded', sa.Integer(), nullable=False),
+        sa.Column('ad_ref_id', sa.String(length=64), nullable=True),
+        sa.Column(
+            'created_at', sa.DateTime(timezone=True),
+            server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False,
+        ),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('user_id', 'signin_date', name='uq_signin_boost_user_date'),
+    )
+    with op.batch_alter_table('signin_boost_record', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_signin_boost_record_user_id'), ['user_id'], unique=False)
+
+    op.create_table(
+        'ad_feed_reward_record',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('client_event_id', sa.String(length=64), nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('reward_date', sa.String(length=10), nullable=False),
+        sa.Column('duration_seconds', sa.Integer(), nullable=False),
+        sa.Column('unit_count', sa.Integer(), nullable=False),
+        sa.Column('ecpm_raw', sa.String(length=32), nullable=False),
+        sa.Column('adn', sa.String(length=32), nullable=True),
+        sa.Column('slot_id', sa.String(length=64), nullable=True),
+        sa.Column('coin', sa.Integer(), nullable=False),
+        sa.Column('status', sa.String(length=16), nullable=False),
+        sa.Column(
+            'created_at', sa.DateTime(timezone=True),
+            server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False,
+        ),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('client_event_id', name='uq_ad_feed_reward_client_event'),
+    )
+    with op.batch_alter_table('ad_feed_reward_record', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_ad_feed_reward_record_user_id'), ['user_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_ad_feed_reward_record_reward_date'), ['reward_date'], unique=False)
+        batch_op.create_index(batch_op.f('ix_ad_feed_reward_record_created_at'), ['created_at'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('ad_feed_reward_record', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_ad_feed_reward_record_created_at'))
+        batch_op.drop_index(batch_op.f('ix_ad_feed_reward_record_reward_date'))
+        batch_op.drop_index(batch_op.f('ix_ad_feed_reward_record_user_id'))
+    op.drop_table('ad_feed_reward_record')
+
+    with op.batch_alter_table('signin_boost_record', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_signin_boost_record_user_id'))
+    op.drop_table('signin_boost_record')

alembic/versions/coin_reward_points_phase2.py

@@ -0,0 +1,71 @@
+"""coin reward points phase2
+
+Revision ID: coin_reward_phase2
+Revises: 0cf18d590b1d
+Create Date: 2026-06-08
+"""
+
+from __future__ import annotations
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision = "coin_reward_phase2"
+down_revision = "0cf18d590b1d"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    op.add_column(
+        "ad_ecpm_record",
+        sa.Column("ad_session_id", sa.String(length=64), nullable=True),
+    )
+    op.create_index(
+        op.f("ix_ad_ecpm_record_ad_session_id"),
+        "ad_ecpm_record",
+        ["ad_session_id"],
+        unique=False,
+    )
+    op.create_index(
+        "uq_ad_ecpm_record_session",
+        "ad_ecpm_record",
+        ["ad_session_id"],
+        unique=True,
+    )
+
+    op.add_column(
+        "ad_reward_record",
+        sa.Column(
+            "reward_scene",
+            sa.String(length=32),
+            nullable=False,
+            server_default="reward_video",
+        ),
+    )
+    op.add_column(
+        "ad_reward_record",
+        sa.Column("ad_session_id", sa.String(length=64), nullable=True),
+    )
+    op.add_column(
+        "ad_reward_record",
+        sa.Column("ecpm_raw", sa.String(length=32), nullable=True),
+    )
+    op.create_index(
+        op.f("ix_ad_reward_record_ad_session_id"),
+        "ad_reward_record",
+        ["ad_session_id"],
+        unique=False,
+    )
+
+
+def downgrade() -> None:
+    op.drop_index(op.f("ix_ad_reward_record_ad_session_id"), table_name="ad_reward_record")
+    op.drop_column("ad_reward_record", "ecpm_raw")
+    op.drop_column("ad_reward_record", "ad_session_id")
+    op.drop_column("ad_reward_record", "reward_scene")
+
+    op.drop_index("uq_ad_ecpm_record_session", table_name="ad_ecpm_record")
+    op.drop_index(op.f("ix_ad_ecpm_record_ad_session_id"), table_name="ad_ecpm_record")
+    op.drop_column("ad_ecpm_record", "ad_session_id")

alembic/versions/coin_transaction_task_ref_unique.py

@@ -0,0 +1,40 @@
+"""add coin_transaction task ref unique index
+
+可重复任务(task_enable_notification「打开消息提醒」逐次减半领取)的发放路径是无锁的
+读-算-写:并发/连点的两个 claim 会都读到同一已领次数、算出同一 ref_id(task_key:N)、
+各发一笔,导致双倍发钱。给 coin_transaction 加 (user_id, biz_type, ref_id) 部分唯一索引
+(仅 biz_type LIKE 'task%' 且 ref_id 非空),第二笔撞唯一约束 → IntegrityError,被
+task.claim_task 兜底成 AlreadyClaimedError(409)。与 cash_transaction 提现退款、
+withdraw_order 在途单的「唯一约束 + IntegrityError 兜底」同模式。
+
+Revision ID: coin_txn_task_ref_uq
+Revises: drop_force_onboarding
+Create Date: 2026-06-12 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "coin_txn_task_ref_uq"
+down_revision: Union[str, Sequence[str], None] = "drop_force_onboarding"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_index(
+        "ux_coin_transaction_task_ref",
+        "coin_transaction",
+        ["user_id", "biz_type", "ref_id"],
+        unique=True,
+        sqlite_where=sa.text("biz_type LIKE 'task%' AND ref_id IS NOT NULL"),
+        postgresql_where=sa.text("biz_type LIKE 'task%' AND ref_id IS NOT NULL"),
+    )
+
+
+def downgrade() -> None:
+    op.drop_index("ux_coin_transaction_task_ref", table_name="coin_transaction")

alembic/versions/comparison_debug_fields.py

@@ -0,0 +1,54 @@
+"""comparison_record: 客户端环境 / 性能统计 / LLM 明细 debug 字段
+
+Revision ID: comparison_debug_fields
+Revises: cps_activity_image
+Create Date: 2026-06-17 22:00:00.000000
+
+给 admin 比价记录 debug 页新增采集列，全部 nullable(旧记录 / 未发版客户端为 NULL，向后兼容)：
+- 客户端环境: device_*/rom_*/android_*/app_version*/source_app_version/经纬度
+- 过程统计: total_ms/step_count/llm_call_count/retry_count
+- LLM 明细: llm_calls(JSONB，server 按 trace_id 同机拉 pricebot 落库)
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+
+revision: str = "comparison_debug_fields"
+down_revision: Union[str, Sequence[str], None] = "cps_activity_image"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+_JSONB = sa.JSON().with_variant(postgresql.JSONB(), "postgresql")
+
+
+def upgrade() -> None:
+    op.add_column("comparison_record", sa.Column("device_model", sa.String(64), nullable=True))
+    op.add_column("comparison_record", sa.Column("device_manufacturer", sa.String(64), nullable=True))
+    op.add_column("comparison_record", sa.Column("rom_vendor", sa.String(32), nullable=True))
+    op.add_column("comparison_record", sa.Column("rom_name", sa.String(32), nullable=True))
+    op.add_column("comparison_record", sa.Column("rom_version", sa.Integer(), nullable=True))
+    op.add_column("comparison_record", sa.Column("android_version", sa.String(16), nullable=True))
+    op.add_column("comparison_record", sa.Column("android_sdk", sa.Integer(), nullable=True))
+    op.add_column("comparison_record", sa.Column("app_version", sa.String(32), nullable=True))
+    op.add_column("comparison_record", sa.Column("app_version_code", sa.Integer(), nullable=True))
+    op.add_column("comparison_record", sa.Column("source_app_version", sa.String(32), nullable=True))
+    op.add_column("comparison_record", sa.Column("longitude", sa.Float(), nullable=True))
+    op.add_column("comparison_record", sa.Column("latitude", sa.Float(), nullable=True))
+    op.add_column("comparison_record", sa.Column("total_ms", sa.Integer(), nullable=True))
+    op.add_column("comparison_record", sa.Column("step_count", sa.Integer(), nullable=True))
+    op.add_column("comparison_record", sa.Column("llm_call_count", sa.Integer(), nullable=True))
+    op.add_column("comparison_record", sa.Column("retry_count", sa.Integer(), nullable=True))
+    op.add_column("comparison_record", sa.Column("llm_calls", _JSONB, nullable=True))
+
+
+def downgrade() -> None:
+    for col in (
+        "llm_calls", "retry_count", "llm_call_count", "step_count", "total_ms",
+        "latitude", "longitude", "source_app_version", "app_version_code",
+        "app_version", "android_sdk", "android_version", "rom_version",
+        "rom_name", "rom_vendor", "device_manufacturer", "device_model",
+    ):
+        op.drop_column("comparison_record", col)

alembic/versions/comparison_milestone_claim_table.py

@@ -0,0 +1,41 @@
+"""comparison_milestone_claim table (比价战绩里程碑领取记录)
+
+Revision ID: d4e5f6a7b8c9
+Revises: c3d4e5f6a7b8
+Create Date: 2026-05-31 18:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'd4e5f6a7b8c9'
+down_revision: Union[str, Sequence[str], None] = 'c3d4e5f6a7b8'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'comparison_milestone_claim',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('milestone', sa.Integer(), nullable=False),
+        sa.Column('coin_awarded', sa.Integer(), nullable=False),
+        sa.Column('claimed_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('user_id', 'milestone', name='uq_compare_milestone_user'),
+    )
+    with op.batch_alter_table('comparison_milestone_claim', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_comparison_milestone_claim_user_id'), ['user_id'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('comparison_milestone_claim', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_comparison_milestone_claim_user_id'))
+
+    op.drop_table('comparison_milestone_claim')

alembic/versions/comparison_product_names.py

@@ -0,0 +1,70 @@
+"""comparison_record 加 product_names 列(下单商品名派生串)+ 回填历史行
+
+admin 比价记录页要把原「店/商品」一列拆成「店」+「商品」两列、并支持按商品名搜索。
+items 是 JSON(SQLite 下 json.dumps ensure_ascii 会把中文转义存成 \\uXXXX,无法直接 CAST+LIKE
+命中中文),故把商品名派生成普通文本列,跨库 LIKE 一致、可索引。
+
+写路径(upsert_record / harvest_done)已同步派生;本迁移建列并从已存 items 回填历史行。
+全新环境顺序应用即得空列 + 回填(表本为空,回填 no-op)。
+
+Revision ID: comparison_product_names
+Revises: comparison_record_trace_unique
+Create Date: 2026-07-04 00:00:00.000000
+"""
+
+import json
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+revision: str = "comparison_product_names"
+down_revision: str | Sequence[str] | None = "comparison_record_trace_unique"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def _product_names(items) -> str | None:
+    """items([{name,...}]) → 顿号分隔的商品名串(去重保序)。与 repositories.comparison
+    ._product_names_from_items 同口径,迁移内联一份避免耦合 app 代码。"""
+    if isinstance(items, str):  # SQLite: items 以 JSON 文本存,取回是 str
+        try:
+            items = json.loads(items)
+        except (ValueError, TypeError):
+            return None
+    if not isinstance(items, list) or not items:
+        return None
+    names: list[str] = []
+    for it in items:
+        name = it.get("name") if isinstance(it, dict) else None
+        if not name:
+            continue
+        s = str(name).strip()
+        if s and s not in names:
+            names.append(s)
+    joined = "、".join(names)
+    return joined[:500] or None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("comparison_record", schema=None) as batch_op:
+        batch_op.add_column(sa.Column("product_names", sa.String(length=512), nullable=True))
+
+    # 回填:从已存 items 派生商品名。items 小(菜名列表),一次取回即可;只更新有商品名的行。
+    bind = op.get_bind()
+    rows = bind.execute(
+        sa.text("SELECT id, items FROM comparison_record")
+    ).fetchall()
+    for rid, items in rows:
+        pn = _product_names(items)
+        if pn:
+            bind.execute(
+                sa.text("UPDATE comparison_record SET product_names = :pn WHERE id = :id"),
+                {"pn": pn, "id": rid},
+            )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("comparison_record", schema=None) as batch_op:
+        batch_op.drop_column("product_names")

alembic/versions/comparison_record_information.py

@@ -0,0 +1,28 @@
+"""comparison_record.information (done 帧文案/失败原因)
+
+Revision ID: c3d4e5f6a7b8
+Revises: b2c3d4e5f6a7
+Create Date: 2026-05-31 18:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'c3d4e5f6a7b8'
+down_revision: Union[str, Sequence[str], None] = 'b2c3d4e5f6a7'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('comparison_record', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('information', sa.String(length=256), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('comparison_record', schema=None) as batch_op:
+        batch_op.drop_column('information')

alembic/versions/comparison_record_table.py

@@ -0,0 +1,64 @@
+"""comparison_record table (比价记录:每次比价完整明细,「我的比价记录」数据源)
+
+Revision ID: b2c3d4e5f6a7
+Revises: a1b2c3d4e5f6
+Create Date: 2026-05-31 15:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'b2c3d4e5f6a7'
+down_revision: Union[str, Sequence[str], None] = 'a1b2c3d4e5f6'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'comparison_record',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('device_id', sa.String(length=64), nullable=True),
+        sa.Column('business_type', sa.String(length=16), nullable=False),
+        sa.Column('trace_id', sa.String(length=64), nullable=False),
+        sa.Column('source_platform_id', sa.String(length=32), nullable=True),
+        sa.Column('source_platform_name', sa.String(length=32), nullable=True),
+        sa.Column('source_package', sa.String(length=128), nullable=True),
+        sa.Column('source_price_cents', sa.Integer(), nullable=True),
+        sa.Column('best_platform_id', sa.String(length=32), nullable=True),
+        sa.Column('best_platform_name', sa.String(length=32), nullable=True),
+        sa.Column('best_price_cents', sa.Integer(), nullable=True),
+        sa.Column('saved_amount_cents', sa.Integer(), nullable=True),
+        sa.Column('is_source_best', sa.Boolean(), nullable=True),
+        sa.Column('store_name', sa.String(length=128), nullable=True),
+        sa.Column('total_dish_count', sa.Integer(), nullable=True),
+        sa.Column('skipped_dish_count', sa.Integer(), nullable=True),
+        sa.Column('status', sa.String(length=16), nullable=False),
+        # PG 上为 JSONB,其它(SQLite)为 JSON——与模型层 with_variant 对齐
+        sa.Column('items', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=False),
+        sa.Column('comparison_results', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=False),
+        sa.Column('skipped_dish_names', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=False),
+        sa.Column('raw_payload', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=True),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('user_id', 'trace_id', name='uq_comparison_user_trace'),
+    )
+    with op.batch_alter_table('comparison_record', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_comparison_record_user_id'), ['user_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_comparison_record_business_type'), ['business_type'], unique=False)
+        batch_op.create_index(batch_op.f('ix_comparison_record_created_at'), ['created_at'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('comparison_record', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_comparison_record_created_at'))
+        batch_op.drop_index(batch_op.f('ix_comparison_record_business_type'))
+        batch_op.drop_index(batch_op.f('ix_comparison_record_user_id'))
+
+    op.drop_table('comparison_record')

alembic/versions/comparison_record_trace_unique.py

@@ -0,0 +1,49 @@
+"""comparison_record: 唯一键 (user_id,trace_id) → trace_id 单列 + user_id 可空
+
+Revision ID: comparison_record_trace_unique
+Revises: feedback_type_reply
+Create Date: 2026-07-03 12:00:00.000000
+
+比价记录改「后端 harvest」：app-server 在帧0(pricebot 出 trace_id)即建行，随 done/finalize
+逐步补全，客户端不再 POST 记录。故本表要两处调整：
+  1. user_id 改**可空**——harvest 建行那刻(软鉴权 / 老客户端匿名)可能还没有 user_id。
+  2. 唯一键 (user_id, trace_id) → **trace_id 单列**——trace_id 由 app-server 签发、全局唯一，
+     一次比价一行；harvest 建行时 user_id 还没有，不能再用复合键去重。
+
+⚠️ 上线前(QA)务必确认 comparison_record 无重复 trace_id：旧复合唯一键**允许**同 trace_id
+   跨不同 user_id（实际上客户端 UUID 从不重复，但约束没拦），若真有重复,建 trace 单列唯一会失败。
+   查:  SELECT trace_id, COUNT(*) c FROM comparison_record GROUP BY trace_id HAVING c > 1;
+
+SQLite 不支持直接 drop/alter 约束/列，用 batch_alter_table（建临时表+拷数据+换名），
+与 coupon_engage_per_package / store_mapping_* 同款；Postgres 直接执行原生 ALTER。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'comparison_record_trace_unique'
+down_revision: Union[str, Sequence[str], None] = 'feedback_type_reply'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('comparison_record', schema=None) as batch_op:
+        # harvest 帧0 建行时 user_id 可能暂缺 → 放开非空。
+        batch_op.alter_column('user_id', existing_type=sa.Integer(), nullable=True)
+        # 复合唯一 → trace_id 单列唯一。
+        batch_op.drop_constraint('uq_comparison_user_trace', type_='unique')
+        batch_op.create_unique_constraint('uq_comparison_trace', ['trace_id'])
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('comparison_record', schema=None) as batch_op:
+        batch_op.drop_constraint('uq_comparison_trace', type_='unique')
+        batch_op.create_unique_constraint(
+            'uq_comparison_user_trace', ['user_id', 'trace_id']
+        )
+        # 回退非空前提是当时无 null user_id 行(harvest 期可能有孤儿行,回滚需先清理)。
+        batch_op.alter_column('user_id', existing_type=sa.Integer(), nullable=False)

alembic/versions/comparison_status_created_idx.py

@@ -0,0 +1,48 @@
+"""add composite index (status, created_at) on comparison_record
+
+首页轮播 feed(按 status='success' 过滤 + created_at 近期排序)与省钱战绩聚合
+都吃这个过滤+排序;复合索引避免随数据量增大退化成全表扫。
+
+Revision ID: comparison_status_created_idx
+Revises: a8c47fc4dc39
+Create Date: 2026-06-10
+"""
+
+from __future__ import annotations
+
+from alembic import op
+
+revision = "comparison_status_created_idx"
+down_revision = "a8c47fc4dc39"
+branch_labels = None
+depends_on = None
+
+INDEX_NAME = "ix_comparison_status_created"
+
+
+def upgrade() -> None:
+    bind = op.get_bind()
+    if bind.dialect.name == "postgresql":
+        # PG 上 comparison_record 已有数据量, 普通 CREATE INDEX 持表写锁会阻塞线上写入;
+        # 用 CONCURRENTLY 不锁表(须脱离事务, autocommit_block 切到自动提交)。
+        with op.get_context().autocommit_block():
+            op.create_index(
+                INDEX_NAME, "comparison_record", ["status", "created_at"],
+                unique=False, postgresql_concurrently=True,
+            )
+    else:
+        op.create_index(
+            INDEX_NAME, "comparison_record", ["status", "created_at"], unique=False
+        )
+
+
+def downgrade() -> None:
+    bind = op.get_bind()
+    if bind.dialect.name == "postgresql":
+        with op.get_context().autocommit_block():
+            op.drop_index(
+                INDEX_NAME, table_name="comparison_record",
+                postgresql_concurrently=True,
+            )
+    else:
+        op.drop_index(INDEX_NAME, table_name="comparison_record")

alembic/versions/comparison_token_cols.py

@@ -0,0 +1,37 @@
+"""add input_tokens / output_tokens to comparison_record
+
+比价记录增加本次 LLM 累计 token(server 从 llm_calls[].usage 累加),
+供 admin 比价记录页展示 token 数与估算成本。
+
+Revision ID: comparison_token_cols
+Revises: feedback_review_fields
+Create Date: 2026-06-23 00:40:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "comparison_token_cols"
+down_revision: Union[str, Sequence[str], None] = "feedback_review_fields"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.add_column(
+        "comparison_record",
+        sa.Column("input_tokens", sa.Integer(), nullable=True),
+    )
+    op.add_column(
+        "comparison_record",
+        sa.Column("output_tokens", sa.Integer(), nullable=True),
+    )
+
+
+def downgrade() -> None:
+    op.drop_column("comparison_record", "output_tokens")
+    op.drop_column("comparison_record", "input_tokens")

alembic/versions/coupon_daily_completion_table.py

@@ -0,0 +1,58 @@
+"""coupon daily completion table(今日跑完整轮领券 → 首页置灰源)
+
+Revision ID: coupon_daily_completion
+Revises: f8d3b1e60a27
+Create Date: 2026-06-10 00:00:00.000000
+
+"""
+
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "coupon_daily_completion"
+down_revision: str | Sequence[str] | None = "f8d3b1e60a27"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        "coupon_daily_completion",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("device_id", sa.String(length=64), nullable=False),
+        sa.Column("user_id", sa.Integer(), nullable=True),
+        sa.Column("complete_date", sa.Date(), nullable=False),
+        sa.Column("trace_id", sa.String(length=64), nullable=True),
+        sa.Column(
+            "created_at",
+            sa.DateTime(timezone=True),
+            server_default=sa.text("(CURRENT_TIMESTAMP)"),
+            nullable=False,
+        ),
+        sa.Column(
+            "updated_at",
+            sa.DateTime(timezone=True),
+            server_default=sa.text("(CURRENT_TIMESTAMP)"),
+            nullable=False,
+        ),
+        sa.PrimaryKeyConstraint("id"),
+        sa.UniqueConstraint("device_id", "complete_date", name="uq_coupon_completion_device_date"),
+    )
+    with op.batch_alter_table("coupon_daily_completion", schema=None) as batch_op:
+        batch_op.create_index(
+            batch_op.f("ix_coupon_daily_completion_user_id"), ["user_id"], unique=False
+        )
+        batch_op.create_index(
+            batch_op.f("ix_coupon_daily_completion_trace_id"), ["trace_id"], unique=False
+        )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("coupon_daily_completion", schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f("ix_coupon_daily_completion_trace_id"))
+        batch_op.drop_index(batch_op.f("ix_coupon_daily_completion_user_id"))
+    op.drop_table("coupon_daily_completion")

alembic/versions/coupon_engage_per_package.py

@@ -0,0 +1,53 @@
+"""coupon_prompt_engagement 频控加 package 维度(美团/淘宝/京东各自独立弹)
+
+Revision ID: coupon_engage_per_package
+Revises: store_mapping_jd_cols
+Create Date: 2026-06-14 11:00:00.000000
+
+需求:领券引导窗按 (device, App, 自然日) 频控——在美团弹过/领过,不影响淘宝、京东今天
+仍各弹一次。原表唯一键是 (device_id, engage_date),缺 package → 任一 App 弹过就把整台
+设备当天标记 engage,其余 App 被压住不弹(bug)。
+
+本迁移:
+  1. 加 package 列(NOT NULL,旧行用 server_default "" 填占位,不影响新逻辑判断)。
+  2. 旧唯一约束 (device_id, engage_date) → 新 (device_id, package, engage_date)。
+
+SQLite 不支持直接 drop/add 约束,用 batch_alter_table(建临时表 + 拷数据 + 换名,
+与 store_mapping_* 同款)。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'coupon_engage_per_package'
+down_revision: Union[str, Sequence[str], None] = 'store_mapping_jd_cols'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('coupon_prompt_engagement', schema=None) as batch_op:
+        # 加 package 列。旧行(改造前的全局记录)填 "" 占位:它们对应的是"老的全局态",
+        # 新逻辑按 (device, package, 日) 判,占位 "" 不会与真实包名(com.xxx)碰撞。
+        batch_op.add_column(
+            sa.Column('package', sa.String(length=64), nullable=False, server_default='')
+        )
+        # 旧唯一约束 (device_id, engage_date) → 新三元组 (device_id, package, engage_date)。
+        batch_op.drop_constraint('uq_coupon_engage_device_date', type_='unique')
+        batch_op.create_unique_constraint(
+            'uq_coupon_engage_device_pkg_date',
+            ['device_id', 'package', 'engage_date'],
+        )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('coupon_prompt_engagement', schema=None) as batch_op:
+        batch_op.drop_constraint('uq_coupon_engage_device_pkg_date', type_='unique')
+        batch_op.create_unique_constraint(
+            'uq_coupon_engage_device_date',
+            ['device_id', 'engage_date'],
+        )
+        batch_op.drop_column('package')

alembic/versions/coupon_session_origin_package.py

@@ -0,0 +1,32 @@
+"""coupon_session 加 origin_package 列(发起来源 App 包名 → admin「发起平台」)
+
+null=App 内(傻瓜比价首页)发起,非空=从美团/淘宝/京东弹券发起。与 trace_url 同理单独成迁移,
+已建表环境靠它补列、全新环境顺序应用,不重复加列。
+
+Revision ID: coupon_session_origin_package
+Revises: coupon_session_trace_url
+Create Date: 2026-06-30 00:00:00.000000
+
+"""
+
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "coupon_session_origin_package"
+down_revision: str | Sequence[str] | None = "coupon_session_trace_url"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("coupon_session", schema=None) as batch_op:
+        batch_op.add_column(sa.Column("origin_package", sa.String(length=64), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("coupon_session", schema=None) as batch_op:
+        batch_op.drop_column("origin_package")

alembic/versions/coupon_session_table.py

@@ -0,0 +1,81 @@
+"""coupon session table(领券任务全程流水 → admin「领券数据」看板数据源)
+
+一次领券一行(trace_id 唯一):客户端 POST /api/v1/coupon/session 两段上报 —— 发起建行
+(status=started)、收尾(completed/failed/abandoned)按 trace_id 更新同一行。记全程耗时
+elapsed_ms + 各平台耗时 platform_elapsed + 机型/ROM,供 admin 算发起/完成数、耗时分位、机型维度。
+
+Revision ID: coupon_session_table
+Revises: feedback_submit_env
+Create Date: 2026-06-30 00:00:00.000000
+
+"""
+
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "coupon_session_table"
+down_revision: str | Sequence[str] | None = "feedback_submit_env"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+# PG 用 JSONB,SQLite(本地/测试)退化为通用 JSON(同 model 的 _JSON variant)。
+_JSON = sa.JSON().with_variant(postgresql.JSONB(), "postgresql")
+
+
+def upgrade() -> None:
+    op.create_table(
+        "coupon_session",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("trace_id", sa.String(length=64), nullable=False),
+        sa.Column("device_id", sa.String(length=64), nullable=False),
+        sa.Column("user_id", sa.Integer(), nullable=True),
+        sa.Column("status", sa.String(length=16), nullable=False),
+        sa.Column("app_env", sa.String(length=16), nullable=True),
+        sa.Column("platforms", _JSON, nullable=True),
+        sa.Column("device_model", sa.String(length=128), nullable=True),
+        sa.Column("rom", sa.String(length=64), nullable=True),
+        sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
+        sa.Column("started_date", sa.Date(), nullable=False),
+        sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
+        sa.Column("elapsed_ms", sa.Integer(), nullable=True),
+        sa.Column("platform_elapsed", _JSON, nullable=True),
+        sa.Column("claimed_count", sa.Integer(), nullable=True),
+        sa.Column(
+            "created_at",
+            sa.DateTime(timezone=True),
+            server_default=sa.text("(CURRENT_TIMESTAMP)"),
+            nullable=False,
+        ),
+        sa.Column(
+            "updated_at",
+            sa.DateTime(timezone=True),
+            server_default=sa.text("(CURRENT_TIMESTAMP)"),
+            nullable=False,
+        ),
+        sa.PrimaryKeyConstraint("id"),
+        sa.UniqueConstraint("trace_id", name="uq_coupon_session_trace"),
+    )
+    with op.batch_alter_table("coupon_session", schema=None) as batch_op:
+        batch_op.create_index(
+            batch_op.f("ix_coupon_session_user_id"), ["user_id"], unique=False
+        )
+        batch_op.create_index(
+            batch_op.f("ix_coupon_session_app_env"), ["app_env"], unique=False
+        )
+        # admin 主聚合/筛选:按上海自然日 + 环境。
+        batch_op.create_index(
+            "ix_coupon_session_date_env", ["started_date", "app_env"], unique=False
+        )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("coupon_session", schema=None) as batch_op:
+        batch_op.drop_index("ix_coupon_session_date_env")
+        batch_op.drop_index(batch_op.f("ix_coupon_session_app_env"))
+        batch_op.drop_index(batch_op.f("ix_coupon_session_user_id"))
+    op.drop_table("coupon_session")

alembic/versions/coupon_session_trace_url.py

@@ -0,0 +1,32 @@
+"""coupon_session 加 trace_url 列(pricebot done 帧公网调试链接)
+
+建表迁移 coupon_session_table 落地后才追加本列,故单独成一个迁移:已建表的环境(本地/已 upgrade 过)
+靠它补列,全新环境则「建表(无 trace_url)→ 本迁移加列」,两条路一致、不重复加列。
+
+Revision ID: coupon_session_trace_url
+Revises: coupon_session_table
+Create Date: 2026-06-30 00:00:00.000000
+
+"""
+
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "coupon_session_trace_url"
+down_revision: str | Sequence[str] | None = "coupon_session_table"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("coupon_session", schema=None) as batch_op:
+        batch_op.add_column(sa.Column("trace_url", sa.String(length=512), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("coupon_session", schema=None) as batch_op:
+        batch_op.drop_column("trace_url")

alembic/versions/coupon_state_tables.py

@@ -0,0 +1,77 @@
+"""coupon state tables (领券今日状态:弹窗频控 engagement + 领券记录 claim)
+
+Revision ID: coupon_state_tables
+Revises: 11a1d08c6f55
+Create Date: 2026-06-08 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'coupon_state_tables'
+down_revision: Union[str, Sequence[str], None] = '11a1d08c6f55'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+# PG 上为 JSONB,其它(SQLite dev)为 JSON——与模型层 with_variant 对齐
+_JSON = sa.JSON().with_variant(postgresql.JSONB(), 'postgresql')
+
+
+def upgrade() -> None:
+    # 领券记录(资产层,当前不参与去重判断)
+    op.create_table(
+        'coupon_claim_record',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('device_id', sa.String(length=64), nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=True),
+        sa.Column('coupon_id', sa.String(length=64), nullable=False),
+        sa.Column('claim_date', sa.Date(), nullable=False),
+        sa.Column('status', sa.String(length=24), nullable=False),
+        sa.Column('vendor', sa.String(length=48), nullable=True),
+        sa.Column('coupon_name', sa.String(length=128), nullable=True),
+        sa.Column('claimed_count', sa.Integer(), nullable=True),
+        sa.Column('trace_id', sa.String(length=64), nullable=True),
+        sa.Column('reason', sa.String(length=255), nullable=True),
+        sa.Column('extra', _JSON, nullable=True),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('device_id', 'coupon_id', 'claim_date', name='uq_coupon_claim_device_coupon_date'),
+    )
+    with op.batch_alter_table('coupon_claim_record', schema=None) as batch_op:
+        batch_op.create_index('ix_coupon_claim_device_date', ['device_id', 'claim_date'], unique=False)
+        batch_op.create_index(batch_op.f('ix_coupon_claim_record_user_id'), ['user_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_coupon_claim_record_trace_id'), ['trace_id'], unique=False)
+
+    # 弹窗频控(今天 engage 过就不再弹)
+    op.create_table(
+        'coupon_prompt_engagement',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('device_id', sa.String(length=64), nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=True),
+        sa.Column('engage_date', sa.Date(), nullable=False),
+        sa.Column('engage_type', sa.String(length=16), nullable=False),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('device_id', 'engage_date', name='uq_coupon_engage_device_date'),
+    )
+    with op.batch_alter_table('coupon_prompt_engagement', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_coupon_prompt_engagement_user_id'), ['user_id'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('coupon_prompt_engagement', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_coupon_prompt_engagement_user_id'))
+    op.drop_table('coupon_prompt_engagement')
+
+    with op.batch_alter_table('coupon_claim_record', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_coupon_claim_record_trace_id'))
+        batch_op.drop_index(batch_op.f('ix_coupon_claim_record_user_id'))
+        batch_op.drop_index('ix_coupon_claim_device_date')
+    op.drop_table('coupon_claim_record')

alembic/versions/cps_activity_image.py

@@ -0,0 +1,38 @@
+"""cps activity 落地页图: cps_activity.image_url + 回填存量淘宝活动
+
+新建淘宝活动时运营提供落地页主视觉图(上传或选已有),落地页按活动展示对应图。
+存量淘宝活动回填为此前写死的那张图,无缝衔接。
+
+Revision ID: cps_activity_image
+Revises: cps_v2_platforms
+Create Date: 2026-06-17 12:00:00.000000
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+from app.core.config import settings
+
+
+revision: str = "cps_activity_image"
+down_revision: Union[str, Sequence[str], None] = "cps_v2_platforms"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.add_column("cps_activity", sa.Column("image_url", sa.String(512), nullable=True))
+    # 回填存量淘宝活动 = 此前写死的那张图(绝对 URL,基于落地页域名 CPS_REDIRECT_BASE;
+    # dev 未配则相对路径)。新活动由运营自传/选,不回填。
+    base = settings.CPS_REDIRECT_BASE.rstrip("/")
+    default_img = f"{base}/media/taobao_landing.jpg" if base else "/media/taobao_landing.jpg"
+    op.execute(
+        sa.text(
+            "UPDATE cps_activity SET image_url = :img WHERE platform = 'taobao'"
+        ).bindparams(img=default_img)
+    )
+
+
+def downgrade() -> None:
+    op.drop_column("cps_activity", "image_url")

alembic/versions/cps_link_tables.py

@@ -0,0 +1,61 @@
+"""cps_link / cps_click tables (群发短链 + 点击统计)
+
+Revision ID: cps_link_tables
+Revises: cps_tables
+Create Date: 2026-06-16 13:20:00.000000
+
+群发券链接套一层 /c/{code} 做点击统计:cps_link(短码→美团目标) + cps_click(点击事件)。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = "cps_link_tables"
+down_revision: Union[str, Sequence[str], None] = "cps_tables"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+_NOW = sa.text("CURRENT_TIMESTAMP")
+
+
+def upgrade() -> None:
+    op.create_table(
+        "cps_link",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("code", sa.String(length=16), nullable=False),
+        sa.Column("group_id", sa.Integer(), nullable=False),
+        sa.Column("activity_id", sa.Integer(), nullable=False),
+        sa.Column("sid", sa.String(length=64), nullable=False),
+        sa.Column("platform", sa.String(length=20), nullable=False, server_default="meituan"),
+        sa.Column("target_url", sa.String(length=1024), nullable=False),
+        sa.Column("created_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
+        sa.PrimaryKeyConstraint("id"),
+        sa.UniqueConstraint("code", name="uq_cps_link_code"),
+    )
+    op.create_index("ix_cps_link_code", "cps_link", ["code"])
+    op.create_index("ix_cps_link_group_id", "cps_link", ["group_id"])
+    op.create_index("ix_cps_link_activity_id", "cps_link", ["activity_id"])
+    op.create_index("ix_cps_link_sid", "cps_link", ["sid"])
+
+    op.create_table(
+        "cps_click",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("link_id", sa.Integer(), nullable=False),
+        sa.Column("group_id", sa.Integer(), nullable=False),
+        sa.Column("sid", sa.String(length=64), nullable=False),
+        sa.Column("ip", sa.String(length=64), nullable=True),
+        sa.Column("ua", sa.String(length=512), nullable=True),
+        sa.Column("clicked_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
+        sa.PrimaryKeyConstraint("id"),
+    )
+    op.create_index("ix_cps_click_link_id", "cps_click", ["link_id"])
+    op.create_index("ix_cps_click_group_id", "cps_click", ["group_id"])
+    op.create_index("ix_cps_click_sid", "cps_click", ["sid"])
+    op.create_index("ix_cps_click_clicked_at", "cps_click", ["clicked_at"])
+
+
+def downgrade() -> None:
+    op.drop_table("cps_click")
+    op.drop_table("cps_link")

alembic/versions/cps_tables.py

@@ -0,0 +1,92 @@
+"""cps_group / cps_activity / cps_order tables (CPS 分发与对账)
+
+Revision ID: cps_tables
+Revises: b3f1a2c4d5e6
+Create Date: 2026-06-16 12:40:00.000000
+
+群发 CPS 优惠券的分发与对账:cps_group(群=sid) + cps_activity(可推广活动池) +
+cps_order(美团 query_order 按 sid 拉回的订单明细)。金额统一存「分」。
+order_id 全局唯一(reconcile 按它 upsert,订单状态会变则更新)。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+
+revision: str = "cps_tables"
+down_revision: Union[str, Sequence[str], None] = "b3f1a2c4d5e6"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+_NOW = sa.text("CURRENT_TIMESTAMP")
+
+
+def upgrade() -> None:
+    op.create_table(
+        "cps_group",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("sid", sa.String(length=64), nullable=False),
+        sa.Column("name", sa.String(length=128), nullable=False),
+        sa.Column("member_count", sa.Integer(), nullable=True),
+        sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
+        sa.Column("remark", sa.String(length=256), nullable=True),
+        sa.Column("created_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
+        sa.PrimaryKeyConstraint("id"),
+        sa.UniqueConstraint("sid", name="uq_cps_group_sid"),
+    )
+    op.create_index("ix_cps_group_sid", "cps_group", ["sid"])
+
+    op.create_table(
+        "cps_activity",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("platform", sa.String(length=20), nullable=False, server_default="meituan"),
+        sa.Column("name", sa.String(length=128), nullable=False),
+        sa.Column("act_id", sa.String(length=64), nullable=True),
+        sa.Column("product_view_sign", sa.String(length=128), nullable=True),
+        sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
+        sa.Column("remark", sa.String(length=256), nullable=True),
+        sa.Column("created_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
+        sa.PrimaryKeyConstraint("id"),
+    )
+
+    op.create_table(
+        "cps_order",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("order_id", sa.String(length=64), nullable=False),
+        sa.Column("sid", sa.String(length=64), nullable=True),
+        sa.Column("act_id", sa.String(length=64), nullable=True),
+        sa.Column("biz_line", sa.Integer(), nullable=True),
+        sa.Column("trade_type", sa.Integer(), nullable=True),
+        sa.Column("pay_price_cents", sa.Integer(), nullable=True),
+        sa.Column("commission_cents", sa.Integer(), nullable=True),
+        sa.Column("commission_rate", sa.String(length=16), nullable=True),
+        sa.Column("refund_price_cents", sa.Integer(), nullable=True),
+        sa.Column("refund_profit_cents", sa.Integer(), nullable=True),
+        sa.Column("mt_status", sa.String(length=8), nullable=True),
+        sa.Column("invalid_reason", sa.String(length=128), nullable=True),
+        sa.Column("product_name", sa.String(length=512), nullable=True),
+        sa.Column("pay_time", sa.DateTime(timezone=True), nullable=True),
+        sa.Column("mt_update_time", sa.DateTime(timezone=True), nullable=True),
+        sa.Column(
+            "raw",
+            sa.JSON().with_variant(postgresql.JSONB(), "postgresql"),
+            nullable=False,
+        ),
+        sa.Column("first_seen", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
+        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
+        sa.PrimaryKeyConstraint("id"),
+        sa.UniqueConstraint("order_id", name="uq_cps_order_order_id"),
+    )
+    op.create_index("ix_cps_order_order_id", "cps_order", ["order_id"])
+    op.create_index("ix_cps_order_sid", "cps_order", ["sid"])
+    op.create_index("ix_cps_order_act_id", "cps_order", ["act_id"])
+    op.create_index("ix_cps_order_mt_status", "cps_order", ["mt_status"])
+    op.create_index("ix_cps_order_pay_time", "cps_order", ["pay_time"])
+
+
+def downgrade() -> None:
+    op.drop_table("cps_order")
+    op.drop_table("cps_activity")
+    op.drop_table("cps_group")

alembic/versions/cps_v2_platforms.py

@@ -0,0 +1,86 @@
+"""cps v2: 活动 payload(淘口令/京东链接) + 群多平台/sid 可空 + 点击 event_type
+
+Revision ID: cps_v2_platforms
+Revises: cps_link_tables
+Create Date: 2026-06-17 11:00:00.000000
+
+接入淘宝/京东:
+- cps_activity.payload   淘宝整段淘口令文本 / 京东推广链接(美团仍用 act_id)
+- cps_group.platforms    该群发哪些平台(多选);sid 改可空(纯淘宝/京东无 sid)
+- cps_link.sid 可空 + target_url 加长(淘口令较长)
+- cps_click.event_type   visit / copy(淘宝落地页"复制口令")
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+
+revision: str = "cps_v2_platforms"
+down_revision: Union[str, Sequence[str], None] = "cps_link_tables"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def _has_column(insp: sa.Inspector, table: str, column: str) -> bool:
+    return any(c["name"] == column for c in insp.get_columns(table))
+
+
+def upgrade() -> None:
+    bind = op.get_bind()
+    insp = sa.inspect(bind)
+    # SQLite 不认 ::jsonb cast(会报 "unrecognized token: :"),只有 PG 用显式 cast。
+    # 两边列类型都靠上面的 with_variant 区分,这里只管 server_default 文本。
+    platforms_default = (
+        sa.text("'[\"meituan\"]'::jsonb")
+        if bind.dialect.name == "postgresql"
+        else sa.text("'[\"meituan\"]'")
+    )
+
+    # 活动:淘宝淘口令 / 京东链接
+    # 加 add_column 存在性守卫:本迁移历史上在 SQLite 第一次跑时加完 payload 即在
+    # platforms 那步炸掉(::jsonb),DDL 非事务不回滚 → 半应用状态。守卫让其可自愈重跑。
+    if not _has_column(insp, "cps_activity", "payload"):
+        op.add_column("cps_activity", sa.Column("payload", sa.Text(), nullable=True))
+
+    # 群:多平台(现有群都是美团,server_default 回填)+ sid 可空。
+    # SQLite 不支持 ALTER COLUMN,必须用 batch(重建表)才能改 nullable;PG 走原生 ALTER。
+    with op.batch_alter_table("cps_group") as batch_op:
+        if not _has_column(insp, "cps_group", "platforms"):
+            batch_op.add_column(
+                sa.Column(
+                    "platforms",
+                    sa.JSON().with_variant(postgresql.JSONB(), "postgresql"),
+                    nullable=False,
+                    server_default=platforms_default,
+                )
+            )
+        batch_op.alter_column("sid", existing_type=sa.String(64), nullable=True)
+
+    # link:sid 可空 + target 加长
+    with op.batch_alter_table("cps_link") as batch_op:
+        batch_op.alter_column("sid", existing_type=sa.String(64), nullable=True)
+        batch_op.alter_column("target_url", existing_type=sa.String(1024), type_=sa.String(2048))
+
+    # click:sid 可空 + 事件类型
+    with op.batch_alter_table("cps_click") as batch_op:
+        batch_op.alter_column("sid", existing_type=sa.String(64), nullable=True)
+        if not _has_column(insp, "cps_click", "event_type"):
+            batch_op.add_column(
+                sa.Column("event_type", sa.String(16), nullable=False, server_default="visit")
+            )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("cps_click") as batch_op:
+        batch_op.drop_column("event_type")
+        batch_op.alter_column("sid", existing_type=sa.String(64), nullable=False)
+    with op.batch_alter_table("cps_link") as batch_op:
+        batch_op.alter_column("target_url", existing_type=sa.String(2048), type_=sa.String(1024))
+        batch_op.alter_column("sid", existing_type=sa.String(64), nullable=False)
+    with op.batch_alter_table("cps_group") as batch_op:
+        batch_op.drop_column("platforms")
+        batch_op.alter_column("sid", existing_type=sa.String(64), nullable=False)
+    with op.batch_alter_table("cps_activity") as batch_op:
+        batch_op.drop_column("payload")

alembic/versions/cps_wx_user.py

@@ -0,0 +1,48 @@
+"""cps 微信用户表 cps_wx_user + cps_click 加 openid
+
+CPS 落地页微信网页授权拿 openid/昵称头像,做用户级群统计。
+
+Revision ID: cps_wx_user
+Revises: comparison_debug_fields
+Create Date: 2026-06-19 12:00:00.000000
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = "cps_wx_user"
+down_revision: Union[str, Sequence[str], None] = "comparison_debug_fields"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        "cps_wx_user",
+        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
+        sa.Column("openid", sa.String(64), nullable=False),
+        sa.Column("unionid", sa.String(64), nullable=True),
+        sa.Column("nickname", sa.String(128), nullable=True),
+        sa.Column("headimgurl", sa.String(512), nullable=True),
+        sa.Column("first_code", sa.String(16), nullable=True),
+        sa.Column("first_group_id", sa.Integer(), nullable=True),
+        sa.Column("first_seen", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
+        sa.Column("last_seen", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
+    )
+    op.create_index("ix_cps_wx_user_openid", "cps_wx_user", ["openid"], unique=True)
+    op.create_index("ix_cps_wx_user_unionid", "cps_wx_user", ["unionid"])
+    op.create_index("ix_cps_wx_user_first_group_id", "cps_wx_user", ["first_group_id"])
+
+    op.add_column("cps_click", sa.Column("openid", sa.String(64), nullable=True))
+    op.create_index("ix_cps_click_openid", "cps_click", ["openid"])
+
+
+def downgrade() -> None:
+    op.drop_index("ix_cps_click_openid", table_name="cps_click")
+    op.drop_column("cps_click", "openid")
+    op.drop_index("ix_cps_wx_user_first_group_id", table_name="cps_wx_user")
+    op.drop_index("ix_cps_wx_user_unionid", table_name="cps_wx_user")
+    op.drop_index("ix_cps_wx_user_openid", table_name="cps_wx_user")
+    op.drop_table("cps_wx_user")

alembic/versions/d4b87c5847de_merge_comparison_idx_and_coupon_daily_.py

@@ -0,0 +1,26 @@
+"""merge comparison idx and coupon daily completion heads
+
+Revision ID: d4b87c5847de
+Revises: comparison_status_created_idx, onboarding_completion
+Create Date: 2026-06-10 16:45:50.285917
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'd4b87c5847de'
+down_revision: Union[str, Sequence[str], None] = ('comparison_status_created_idx', 'onboarding_completion')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/d4e68464761d_merge_ad_revenue_report_cols_and_store_.py

@@ -0,0 +1,26 @@
+"""merge ad_revenue_report_cols and store_mapping_tb_dl_invalid heads
+
+Revision ID: d4e68464761d
+Revises: ad_revenue_report_cols, store_mapping_tb_dl_invalid
+Create Date: 2026-06-15 21:55:58.115692
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'd4e68464761d'
+down_revision: Union[str, Sequence[str], None] = ('ad_revenue_report_cols', 'store_mapping_tb_dl_invalid')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/device_first_protected_at.py

@@ -0,0 +1,31 @@
+"""device_liveness 加 first_protected_at(首次开无障碍时刻)
+
+Revision ID: device_first_protected_at
+Revises: bcfcaf07152b
+Create Date: 2026-06-25 00:00:00.000000
+
+admin 设备存活页要展示「首次无障碍开启时间」。touch_heartbeat 在 ever_protected 首次翻 true
+时记一次(后续心跳不覆盖)。仅新增可空列,SQLite 原生支持 add_column、不用 batch;downgrade
+的 drop_column 在 SQLite 走 batch_alter_table 兜底。老设备无此时刻 → 留 NULL(无法准确回填)。
+"""
+from typing import Sequence, Union
+
+import sqlalchemy as sa
+from alembic import op
+
+revision: str = "device_first_protected_at"
+down_revision: Union[str, Sequence[str], None] = "bcfcaf07152b"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.add_column(
+        "device_liveness",
+        sa.Column("first_protected_at", sa.DateTime(timezone=True), nullable=True),
+    )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("device_liveness") as batch_op:
+        batch_op.drop_column("first_protected_at")

alembic/versions/device_kill_alert_pending.py

@@ -0,0 +1,39 @@
+"""device.kill_alert_pending (无障碍掉线「后置检测」待提醒标记)
+
+Revision ID: device_kill_alert_pending
+Revises: device_liveness_table
+Create Date: 2026-06-19
+
+后置检测 pull 版(spec/accessibility-liveness-pull-prompt.md):worker 检出心跳掉线即置此标记,
+客户端下次进 App 拉取到 → 弹「开启自启动」引导,交互后 ack 清回。与 liveness_state 解耦,故单列。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'device_kill_alert_pending'
+down_revision: Union[str, Sequence[str], None] = 'device_liveness_table'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # server_default=sa.false() 跨方言: SQLite 渲染 0 / PG 渲染 false(别用 sa.text("0"),PG 布尔严格会报错),
+    # 给存量行回填 False。
+    with op.batch_alter_table('device_liveness', schema=None) as batch_op:
+        batch_op.add_column(
+            sa.Column(
+                'kill_alert_pending',
+                sa.Boolean(),
+                nullable=False,
+                server_default=sa.false(),
+            )
+        )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('device_liveness', schema=None) as batch_op:
+        batch_op.drop_column('kill_alert_pending')

alembic/versions/device_table.py

@@ -0,0 +1,53 @@
+"""device table (无障碍保护存活检测 + 极光推送)
+
+Revision ID: device_liveness_table
+Revises: ceb286289426
+Create Date: 2026-06-15 12:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'device_liveness_table'
+down_revision: Union[str, Sequence[str], None] = 'ceb286289426'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'device_liveness',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('device_id', sa.String(length=128), nullable=False),
+        sa.Column('registration_id', sa.String(length=64), nullable=True),
+        sa.Column('platform', sa.String(length=16), nullable=False),
+        sa.Column('app_version', sa.String(length=32), nullable=True),
+        sa.Column('ever_protected', sa.Boolean(), nullable=False),
+        sa.Column('last_heartbeat_at', sa.DateTime(timezone=True), nullable=True),
+        sa.Column('last_report_protection_on', sa.Boolean(), nullable=False),
+        sa.Column('liveness_state', sa.String(length=16), nullable=False),
+        sa.Column('notified_at', sa.DateTime(timezone=True), nullable=True),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('user_id', 'device_id', name='uq_device_liveness_user_device'),
+    )
+    with op.batch_alter_table('device_liveness', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_device_liveness_user_id'), ['user_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_device_liveness_device_id'), ['device_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_device_liveness_last_heartbeat_at'), ['last_heartbeat_at'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('device_liveness', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_device_liveness_last_heartbeat_at'))
+        batch_op.drop_index(batch_op.f('ix_device_liveness_device_id'))
+        batch_op.drop_index(batch_op.f('ix_device_liveness_user_id'))
+
+    op.drop_table('device_liveness')

alembic/versions/drop_user_force_onboarding.py

@@ -0,0 +1,40 @@
+"""drop user.force_onboarding column
+
+force_onboarding(运营「按用户」强制新手引导)机制废弃:客户端实际按 onboarding_completion
+(设备+账号 维度)判断是否走引导,该 force_onboarding 列从未被客户端正确响应(被
+onboarding_completed 压过)。改用 admin「设备维度」重置(删 onboarding_completion 记录)替代,
+见 app/admin/routers/onboarding.py。本迁移删列。
+
+Revision ID: drop_force_onboarding
+Revises: 9b894f5fff05
+Create Date: 2026-06-12 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "drop_force_onboarding"
+down_revision: Union[str, Sequence[str], None] = "9b894f5fff05"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.drop_column("user", "force_onboarding")
+
+
+def downgrade() -> None:
+    # 回滚:重建列(同 user_force_onboarding 迁移的 upgrade)。sa.false() 两端兼容。
+    op.add_column(
+        "user",
+        sa.Column(
+            "force_onboarding",
+            sa.Boolean(),
+            nullable=False,
+            server_default=sa.false(),
+        ),
+    )

alembic/versions/ebb6af5c0b56_merge_app_config_and_price_report_heads.py

@@ -0,0 +1,26 @@
+"""merge app_config and price_report heads
+
+Revision ID: ebb6af5c0b56
+Revises: cfg1a2b3c4d5, b7e2c1a9f4d3
+Create Date: 2026-06-06 02:29:13.475265
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'ebb6af5c0b56'
+down_revision: Union[str, Sequence[str], None] = ('cfg1a2b3c4d5', 'b7e2c1a9f4d3')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

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"
+    )

alembic/versions/f01db5d77dac_merge_pg_jsonb_and_feedback_heads.py

@@ -0,0 +1,26 @@
+"""merge pg_jsonb and feedback heads
+
+Revision ID: f01db5d77dac
+Revises: ef96beb47b1e, d1e2f3a4b5c6
+Create Date: 2026-05-29 16:09:58.498935
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'f01db5d77dac'
+down_revision: Union[str, Sequence[str], None] = ('ef96beb47b1e', 'd1e2f3a4b5c6')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/f8d3b1e60a27_add_debug_trace_and_merge_heads.py

@@ -0,0 +1,49 @@
+"""add user.debug_trace_enabled + comparison_record.trace_url
+
+调试链接权限功能：
+  - user.debug_trace_enabled：运营后台给指定用户开「复制调试链接」权限
+  - comparison_record.trace_url：比价记录页「复制调试链接」的数据（pricebot done 帧给，
+    dir 名含落盘时分秒、前端/server 拼不出，必须落库）
+
+注：本迁移最初基于旧 main（#31，双 head）写、down_revision 曾指向那对父；但 #33 已用
+a8c47fc4dc39 合并了那对双 head，故改为线性接在 a8c47fc4dc39 之后、只负责加两列
+（避免与 a8c47fc4dc39 平行再造一个双 head）。
+
+Revision ID: f8d3b1e60a27
+Revises: a8c47fc4dc39
+Create Date: 2026-06-10 02:40:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "f8d3b1e60a27"
+down_revision: Union[str, Sequence[str], None] = "a8c47fc4dc39"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # sa.false() 渲染成 PG 的 false / SQLite 的 0，两端兼容（避免字符串 "false" 在 SQLite 上存歪）
+    op.add_column(
+        "user",
+        sa.Column(
+            "debug_trace_enabled",
+            sa.Boolean(),
+            nullable=False,
+            server_default=sa.false(),
+        ),
+    )
+    op.add_column(
+        "comparison_record",
+        sa.Column("trace_url", sa.String(length=512), nullable=True),
+    )
+
+
+def downgrade() -> None:
+    op.drop_column("comparison_record", "trace_url")
+    op.drop_column("user", "debug_trace_enabled")

alembic/versions/feedback_review_fields.py

@@ -0,0 +1,78 @@
+"""feedback review fields
+
+Revision ID: feedback_review_fields
+Revises: ceb286289426
+Create Date: 2026-06-22 20:00:00.000000
+
+"""
+
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "feedback_review_fields"
+down_revision: str | Sequence[str] | None = "ceb286289426"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("feedback", schema=None) as batch_op:
+        batch_op.add_column(sa.Column("reject_reason", sa.String(length=256), nullable=True))
+        batch_op.add_column(sa.Column("reward_coins", sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column("review_note", sa.String(length=256), nullable=True))
+        batch_op.add_column(sa.Column("reviewed_by_admin_id", sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column("reviewed_at", sa.DateTime(timezone=True), nullable=True))
+        batch_op.create_index(batch_op.f("ix_feedback_status"), ["status"], unique=False)
+        batch_op.create_foreign_key(
+            batch_op.f("fk_feedback_reviewed_by_admin_id_admin_user"),
+            "admin_user",
+            ["reviewed_by_admin_id"],
+            ["id"],
+        )
+
+    feedback = sa.table(
+        "feedback",
+        sa.column("status", sa.String(length=16)),
+    )
+    op.execute(
+        feedback.update()
+        .where(feedback.c.status == "new")
+        .values(status="pending")
+    )
+    op.execute(
+        feedback.update()
+        .where(feedback.c.status == "handled")
+        .values(status="adopted")
+    )
+
+
+def downgrade() -> None:
+    feedback = sa.table(
+        "feedback",
+        sa.column("status", sa.String(length=16)),
+    )
+    op.execute(
+        feedback.update()
+        .where(feedback.c.status == "pending")
+        .values(status="new")
+    )
+    op.execute(
+        feedback.update()
+        .where(feedback.c.status == "adopted")
+        .values(status="handled")
+    )
+
+    with op.batch_alter_table("feedback", schema=None) as batch_op:
+        batch_op.drop_constraint(
+            batch_op.f("fk_feedback_reviewed_by_admin_id_admin_user"),
+            type_="foreignkey",
+        )
+        batch_op.drop_index(batch_op.f("ix_feedback_status"))
+        batch_op.drop_column("reviewed_at")
+        batch_op.drop_column("reviewed_by_admin_id")
+        batch_op.drop_column("review_note")
+        batch_op.drop_column("reward_coins")
+        batch_op.drop_column("reject_reason")

alembic/versions/feedback_submit_env.py

@@ -0,0 +1,34 @@
+"""feedback 加提交端环境快照(app_version / device_model / rom_name / android_version)
+
+Revision ID: feedback_submit_env
+Revises: ad_pangle_daily_revenue
+Create Date: 2026-06-29 00:00:00.000000
+
+admin 用户反馈页要展示「提交版本号」「机型OS版本」,需在提交时落库端环境。仅新增可空列,
+SQLite 原生支持 add_column、不用 batch;downgrade 的 drop_column 在 SQLite 走 batch 兜底。
+历史反馈无此快照 → 留 NULL(无法回填);客户端改版带上后的新反馈才有值。
+"""
+from typing import Sequence, Union
+
+import sqlalchemy as sa
+from alembic import op
+
+revision: str = "feedback_submit_env"
+down_revision: Union[str, Sequence[str], None] = "ad_pangle_daily_revenue"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.add_column("feedback", sa.Column("app_version", sa.String(length=32), nullable=True))
+    op.add_column("feedback", sa.Column("device_model", sa.String(length=64), nullable=True))
+    op.add_column("feedback", sa.Column("rom_name", sa.String(length=32), nullable=True))
+    op.add_column("feedback", sa.Column("android_version", sa.String(length=16), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("feedback") as batch_op:
+        batch_op.drop_column("android_version")
+        batch_op.drop_column("rom_name")
+        batch_op.drop_column("device_model")
+        batch_op.drop_column("app_version")

alembic/versions/feedback_type_reply.py

@@ -0,0 +1,48 @@
+"""feedback 加反馈来源/场景/运营回复(source / scene / admin_reply)
+
+admin「用户反馈」页要展示并筛选「反馈类型」(比价反馈 / 普通反馈),并支持审核时给用户留言:
+  - source: 反馈来源入口(profile=「我的」页 / comparison=比价结果页)。NOT NULL,
+    旧数据 + 普通反馈默认 profile(server_default)。加索引供 admin 按类型筛选。
+  - scene: 比价反馈的问题场景(找错商品/优惠不对…),普通反馈为 NULL。
+  - admin_reply: 运营给用户的回复留言(用户端可见),随「我的反馈」历史下发。
+
+均为新增列(SQLite 原生支持 add_column);downgrade 的 drop_column 在 SQLite 走 batch 兜底。
+
+Revision ID: feedback_type_reply
+Revises: 761ef181ce7c
+Create Date: 2026-07-02 00:00:00.000000
+"""
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "feedback_type_reply"
+down_revision: str | Sequence[str] | None = "761ef181ce7c"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    op.add_column(
+        "feedback",
+        sa.Column(
+            "source",
+            sa.String(length=16),
+            nullable=False,
+            server_default="profile",
+        ),
+    )
+    op.add_column("feedback", sa.Column("scene", sa.String(length=32), nullable=True))
+    op.add_column("feedback", sa.Column("admin_reply", sa.String(length=256), nullable=True))
+    op.create_index("ix_feedback_source", "feedback", ["source"])
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("feedback") as batch_op:
+        batch_op.drop_index("ix_feedback_source")
+        batch_op.drop_column("admin_reply")
+        batch_op.drop_column("scene")
+        batch_op.drop_column("source")

alembic/versions/invite_cash_compare_reward.py

@@ -0,0 +1,88 @@
+"""invite cash account isolation + compare reward tracking (🅱-1)
+
+Revision ID: invite_cash_compare_reward
+Revises: feedback_review_fields
+Create Date: 2026-06-23 00:00:00.000000
+
+邀请功能 v2 账户隔离 + 比价发奖追踪:
+  - coin_account 加 invite_cash_balance_cents(邀请奖励金独立余额,与金币兑换的 cash 物理隔离)
+  - withdraw_order 加 source(标记提现扣哪个账户,退款退回对应账户;旧单默认 coin_cash)
+  - invite_relation 加比价发奖追踪三列(好友比价多次只发一次)
+  - 新增 invite_cash_transaction 表(邀请奖励金独立流水账本)
+
+加列均 NOT NULL + server_default,存量行自动填默认值,安全。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'invite_cash_compare_reward'
+down_revision: Union[str, Sequence[str], None] = 'feedback_review_fields'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # 1. 邀请奖励金独立余额(与金币兑换的 cash_balance_cents 物理隔离;红线:两本账不可累加)
+    op.add_column(
+        'coin_account',
+        sa.Column('invite_cash_balance_cents', sa.Integer(), nullable=False, server_default='0'),
+    )
+    # 2. 提现单标记账户来源:coin_cash / invite_cash,退款退回对应账户(旧单默认 coin_cash)
+    op.add_column(
+        'withdraw_order',
+        sa.Column('source', sa.String(length=16), nullable=False, server_default='coin_cash'),
+    )
+    # 3. 邀请关系加比价发奖追踪(好友"下载+登录+比价一次"→ 给邀请人发奖,只发一次)
+    op.add_column(
+        'invite_relation',
+        sa.Column('compare_reward_granted', sa.Boolean(), nullable=False, server_default=sa.false()),
+    )
+    op.add_column(
+        'invite_relation',
+        sa.Column('compare_reward_cents', sa.Integer(), nullable=False, server_default='0'),
+    )
+    op.add_column(
+        'invite_relation',
+        sa.Column('compare_rewarded_at', sa.DateTime(timezone=True), nullable=True),
+    )
+    # 4. 邀请奖励金独立流水表(结构同 cash_transaction;balance_after 记 invite_cash_balance_cents)
+    op.create_table(
+        'invite_cash_transaction',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('amount_cents', sa.Integer(), nullable=False),
+        sa.Column('balance_after_cents', sa.Integer(), nullable=False),
+        sa.Column('biz_type', sa.String(length=32), nullable=False),
+        sa.Column('ref_id', sa.String(length=64), nullable=True),
+        sa.Column('remark', sa.String(length=128), nullable=True),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id']),
+        sa.PrimaryKeyConstraint('id'),
+    )
+    op.create_index('ix_invite_cash_transaction_user_id', 'invite_cash_transaction', ['user_id'])
+    op.create_index('ix_invite_cash_transaction_created_at', 'invite_cash_transaction', ['created_at'])
+    # 提现退款幂等:一个提现单只退一次(partial unique，对齐 cash_transaction 的 withdraw_refund 去重)
+    op.create_index(
+        'ux_invite_cash_txn_refund_ref',
+        'invite_cash_transaction',
+        ['ref_id'],
+        unique=True,
+        sqlite_where=sa.text("biz_type = 'invite_withdraw_refund' AND ref_id IS NOT NULL"),
+        postgresql_where=sa.text("biz_type = 'invite_withdraw_refund' AND ref_id IS NOT NULL"),
+    )
+
+
+def downgrade() -> None:
+    op.drop_index('ux_invite_cash_txn_refund_ref', table_name='invite_cash_transaction')
+    op.drop_index('ix_invite_cash_transaction_created_at', table_name='invite_cash_transaction')
+    op.drop_index('ix_invite_cash_transaction_user_id', table_name='invite_cash_transaction')
+    op.drop_table('invite_cash_transaction')
+    op.drop_column('invite_relation', 'compare_rewarded_at')
+    op.drop_column('invite_relation', 'compare_reward_cents')
+    op.drop_column('invite_relation', 'compare_reward_granted')
+    op.drop_column('withdraw_order', 'source')
+    op.drop_column('coin_account', 'invite_cash_balance_cents')

alembic/versions/invite_code_and_relation.py

@@ -0,0 +1,54 @@
+"""user.invite_code + invite_relation table
+
+Revision ID: invite_code_and_relation
+Revises: 11a1d08c6f55
+Create Date: 2026-06-08 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'invite_code_and_relation'
+down_revision: Union[str, Sequence[str], None] = '11a1d08c6f55'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # user 加邀请码列(唯一索引;nullable 允许多个 NULL=未生成的用户)
+    with op.batch_alter_table('user', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('invite_code', sa.String(length=16), nullable=True))
+        batch_op.create_index('ix_user_invite_code', ['invite_code'], unique=True)
+
+    # 邀请关系表
+    op.create_table(
+        'invite_relation',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('inviter_user_id', sa.Integer(), nullable=False),
+        sa.Column('invitee_user_id', sa.Integer(), nullable=False),
+        sa.Column('channel', sa.String(length=16), nullable=False, server_default='clipboard'),
+        sa.Column('status', sa.String(length=16), nullable=False, server_default='effective'),
+        sa.Column('inviter_coin', sa.Integer(), nullable=False, server_default='0'),
+        sa.Column('invitee_coin', sa.Integer(), nullable=False, server_default='0'),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
+        sa.ForeignKeyConstraint(['inviter_user_id'], ['user.id']),
+        sa.ForeignKeyConstraint(['invitee_user_id'], ['user.id']),
+        sa.PrimaryKeyConstraint('id'),
+    )
+    op.create_index('ix_invite_relation_inviter_user_id', 'invite_relation', ['inviter_user_id'])
+    op.create_index('ix_invite_relation_invitee_user_id', 'invite_relation', ['invitee_user_id'], unique=True)
+    op.create_index('ix_invite_relation_created_at', 'invite_relation', ['created_at'])
+
+
+def downgrade() -> None:
+    op.drop_index('ix_invite_relation_created_at', table_name='invite_relation')
+    op.drop_index('ix_invite_relation_invitee_user_id', table_name='invite_relation')
+    op.drop_index('ix_invite_relation_inviter_user_id', table_name='invite_relation')
+    op.drop_table('invite_relation')
+    with op.batch_alter_table('user', schema=None) as batch_op:
+        batch_op.drop_index('ix_user_invite_code')
+        batch_op.drop_column('invite_code')

alembic/versions/invite_fingerprint_table.py

@@ -0,0 +1,50 @@
+"""invite_fingerprint table (任务 3 指纹归因兜底)
+
+Revision ID: invite_fingerprint_table
+Revises: 0cf18d590b1d
+Create Date: 2026-06-08 14:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'invite_fingerprint_table'
+down_revision: Union[str, Sequence[str], None] = '0cf18d590b1d'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # 邀请指纹归因表:落地页访问时记一行,登录后剪贴板没拿到邀请码时反查
+    op.create_table(
+        'invite_fingerprint',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('inviter_user_id', sa.Integer(), nullable=False),
+        sa.Column('ip', sa.String(length=64), nullable=False),
+        sa.Column('device_model', sa.String(length=64), nullable=False, server_default=''),
+        sa.Column('screen', sa.String(length=32), nullable=False, server_default=''),
+        sa.Column('user_agent', sa.Text(), nullable=False, server_default=''),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
+        sa.ForeignKeyConstraint(['inviter_user_id'], ['user.id']),
+        sa.PrimaryKeyConstraint('id'),
+    )
+    # 反查主索引:(ip, screen, device_model, created_at)
+    # /bind 兜底分支 WHERE ip=? AND screen=? AND device_model=? AND created_at > now - 7d
+    # ORDER BY created_at DESC LIMIT 1 — SQLite/PG 优化器都能反向扫该索引
+    op.create_index(
+        'ix_invite_fp_match',
+        'invite_fingerprint',
+        ['ip', 'screen', 'device_model', 'created_at'],
+    )
+    # 兜底/统计索引:按 inviter 看"这个邀请人的落地页被谁访问过"
+    op.create_index('ix_invite_fp_inviter', 'invite_fingerprint', ['inviter_user_id'])
+
+
+def downgrade() -> None:
+    op.drop_index('ix_invite_fp_inviter', table_name='invite_fingerprint')
+    op.drop_index('ix_invite_fp_match', table_name='invite_fingerprint')
+    op.drop_table('invite_fingerprint')

alembic/versions/jd_cps_order_fields.py

@@ -0,0 +1,56 @@
+"""add jd cps order fields
+
+Revision ID: jd_cps_order_fields
+Revises: 7db22acee504
+Create Date: 2026-06-28 20:30:00.000000
+"""
+from __future__ import annotations
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision = "jd_cps_order_fields"
+down_revision = "7db22acee504"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("cps_order") as batch_op:
+        batch_op.add_column(
+            sa.Column("platform", sa.String(length=20), nullable=False, server_default="meituan")
+        )
+        batch_op.add_column(sa.Column("external_order_id", sa.String(length=128), nullable=True))
+        batch_op.add_column(sa.Column("external_row_id", sa.String(length=128), nullable=True))
+        batch_op.add_column(sa.Column("estimated_commission_cents", sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column("actual_commission_cents", sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column("jd_valid_code", sa.String(length=16), nullable=True))
+        batch_op.add_column(sa.Column("settle_month", sa.String(length=16), nullable=True))
+        batch_op.add_column(sa.Column("site_id", sa.String(length=128), nullable=True))
+        batch_op.add_column(sa.Column("position_id", sa.String(length=128), nullable=True))
+        batch_op.add_column(sa.Column("pid", sa.String(length=128), nullable=True))
+        batch_op.add_column(sa.Column("sub_union_id", sa.String(length=128), nullable=True))
+        batch_op.create_index("ix_cps_order_platform", ["platform"])
+        batch_op.create_index("ix_cps_order_external_order_id", ["external_order_id"])
+        batch_op.create_index("ix_cps_order_external_row_id", ["external_row_id"])
+        batch_op.create_index("ix_cps_order_jd_valid_code", ["jd_valid_code"])
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("cps_order") as batch_op:
+        batch_op.drop_index("ix_cps_order_jd_valid_code")
+        batch_op.drop_index("ix_cps_order_external_row_id")
+        batch_op.drop_index("ix_cps_order_external_order_id")
+        batch_op.drop_index("ix_cps_order_platform")
+        batch_op.drop_column("sub_union_id")
+        batch_op.drop_column("pid")
+        batch_op.drop_column("position_id")
+        batch_op.drop_column("site_id")
+        batch_op.drop_column("settle_month")
+        batch_op.drop_column("jd_valid_code")
+        batch_op.drop_column("actual_commission_cents")
+        batch_op.drop_column("estimated_commission_cents")
+        batch_op.drop_column("external_row_id")
+        batch_op.drop_column("external_order_id")
+        batch_op.drop_column("platform")

alembic/versions/launch_confirm_sample_table.py

@@ -0,0 +1,53 @@
+"""launch_confirm_sample table (启动确认窗 agent 兜底样本: LLM 放行的弹窗样本沉淀)
+
+Revision ID: launch_confirm_sample_table
+Revises: cps_wx_user
+Create Date: 2026-06-20 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "launch_confirm_sample_table"
+down_revision: Union[str, Sequence[str], None] = "cps_wx_user"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        "launch_confirm_sample",
+        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.text("(CURRENT_TIMESTAMP)"), nullable=False),
+        sa.Column("trace_id", sa.String(length=64), nullable=True),
+        sa.Column("device_id", sa.String(length=64), nullable=True),
+        sa.Column("host_package", sa.String(length=128), nullable=True),
+        sa.Column("target_app", sa.String(length=128), nullable=True),
+        sa.Column("system_locale", sa.String(length=32), nullable=True),
+        sa.Column("exec_success", sa.Boolean(), nullable=False),
+        sa.Column("dialog_title", sa.String(length=256), nullable=True),
+        # PG 上为 JSONB,其它(SQLite)为 JSON——与模型层 with_variant 对齐
+        sa.Column("payload", sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), "postgresql"), nullable=True),
+        sa.PrimaryKeyConstraint("id"),
+    )
+    with op.batch_alter_table("launch_confirm_sample", schema=None) as batch_op:
+        batch_op.create_index(batch_op.f("ix_launch_confirm_sample_created_at"), ["created_at"], unique=False)
+        batch_op.create_index(batch_op.f("ix_launch_confirm_sample_trace_id"), ["trace_id"], unique=False)
+        batch_op.create_index(batch_op.f("ix_launch_confirm_sample_device_id"), ["device_id"], unique=False)
+        batch_op.create_index(batch_op.f("ix_launch_confirm_sample_host_package"), ["host_package"], unique=False)
+        batch_op.create_index(batch_op.f("ix_launch_confirm_sample_system_locale"), ["system_locale"], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("launch_confirm_sample", schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f("ix_launch_confirm_sample_system_locale"))
+        batch_op.drop_index(batch_op.f("ix_launch_confirm_sample_host_package"))
+        batch_op.drop_index(batch_op.f("ix_launch_confirm_sample_device_id"))
+        batch_op.drop_index(batch_op.f("ix_launch_confirm_sample_trace_id"))
+        batch_op.drop_index(batch_op.f("ix_launch_confirm_sample_created_at"))
+
+    op.drop_table("launch_confirm_sample")

alembic/versions/marquee_seed_range.py

@@ -0,0 +1,50 @@
+"""marquee_seed 升级为「生成规则」:用户名可空 + 金额拆 min/max 区间
+
+把种子从「死记录」升级:
+- masked_user 改 nullable(留空 → feed 随机合成脱敏用户名)
+- saved_amount_cents(单值)拆成 min_cents / max_cents(feed 在区间内随机取值;固定金额则相等)
+现有 6 条种子:min_cents = max_cents = 原 saved_amount_cents,masked_user 原样保留。
+
+Revision ID: marquee_seed02
+Revises: marquee_seed01
+Create Date: 2026-06-07 11:00:00.000000
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = 'marquee_seed02'
+down_revision: Union[str, Sequence[str], None] = 'marquee_seed01'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # 1) 先加可空新列,使现有行合法
+    with op.batch_alter_table('marquee_seed', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('min_cents', sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column('max_cents', sa.Integer(), nullable=True))
+    # 2) 用旧单值回填区间(固定金额)
+    op.execute('UPDATE marquee_seed SET min_cents = saved_amount_cents, max_cents = saved_amount_cents')
+    # 3) 收紧:区间非空、用户名可空、删旧列
+    with op.batch_alter_table('marquee_seed', schema=None) as batch_op:
+        batch_op.alter_column('min_cents', existing_type=sa.Integer(), nullable=False)
+        batch_op.alter_column('max_cents', existing_type=sa.Integer(), nullable=False)
+        batch_op.alter_column('masked_user', existing_type=sa.String(length=64), nullable=True)
+        batch_op.drop_column('saved_amount_cents')
+
+
+def downgrade() -> None:
+    # 反向:恢复单值列(取下限),用户名回非空
+    with op.batch_alter_table('marquee_seed', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('saved_amount_cents', sa.Integer(), nullable=True))
+    op.execute('UPDATE marquee_seed SET saved_amount_cents = min_cents')
+    # 自动合成名的种子 masked_user 为 NULL,降级前补占位符以满足非空
+    op.execute("UPDATE marquee_seed SET masked_user = '用户********000' WHERE masked_user IS NULL")
+    with op.batch_alter_table('marquee_seed', schema=None) as batch_op:
+        batch_op.alter_column('saved_amount_cents', existing_type=sa.Integer(), nullable=False)
+        batch_op.alter_column('masked_user', existing_type=sa.String(length=64), nullable=False)
+        batch_op.drop_column('max_cents')
+        batch_op.drop_column('min_cents')

alembic/versions/marquee_seed_table.py

@@ -0,0 +1,52 @@
+"""marquee_seed table (首页轮播种子) + 合并 platform_stat / price_observation 两 head
+
+Revision ID: marquee_seed01
+Revises: pstat2anchor01, price_observation_table
+Create Date: 2026-06-07 10:00:00.000000
+
+兼并当前两个 head 为单 head,同时建 marquee_seed 表并播种客户端原写死的 6 条轮播
+(作为初始种子,保证上线即有内容)。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = 'marquee_seed01'
+down_revision: Union[str, Sequence[str], None] = ('pstat2anchor01', 'price_observation_table')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    table = op.create_table(
+        'marquee_seed',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('masked_user', sa.String(length=64), nullable=False),
+        sa.Column('saved_amount_cents', sa.Integer(), nullable=False),
+        sa.Column('enabled', sa.Boolean(), nullable=False, server_default=sa.true()),
+        sa.Column('sort_order', sa.Integer(), nullable=False, server_default='0'),
+        sa.Column(
+            'created_at', sa.DateTime(timezone=True),
+            server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False,
+        ),
+        sa.PrimaryKeyConstraint('id'),
+    )
+
+    # 初始种子 = 客户端原写死的 6 条(金额转成分)
+    op.bulk_insert(
+        table,
+        [
+            {'masked_user': '用户********a52', 'saved_amount_cents': 1860, 'enabled': True, 'sort_order': 1},
+            {'masked_user': '用户********k89', 'saved_amount_cents': 4240, 'enabled': True, 'sort_order': 2},
+            {'masked_user': '用户********m71', 'saved_amount_cents': 790, 'enabled': True, 'sort_order': 3},
+            {'masked_user': '用户********p46', 'saved_amount_cents': 15630, 'enabled': True, 'sort_order': 4},
+            {'masked_user': '用户********r93', 'saved_amount_cents': 2350, 'enabled': True, 'sort_order': 5},
+            {'masked_user': '用户********c28', 'saved_amount_cents': 8940, 'enabled': True, 'sort_order': 6},
+        ],
+    )
+
+
+def downgrade() -> None:
+    op.drop_table('marquee_seed')

alembic/versions/meituan_coupon_image_meta.py

@@ -0,0 +1,34 @@
+"""meituan_coupon image size & type
+
+给 meituan_coupon 增加 image_size(字节)/ image_type(MIME)两列,采集时 HEAD 头图得到,
+供读取侧按图片大小决定是否拼缩放参数提速。两列均 nullable,不动存量行;下一轮全量 ETL
+upsert 自动回填,无需手动 backfill。
+
+Revision ID: meituan_coupon_image_meta
+Revises: feedback_review_fields
+Create Date: 2026-06-23 00:00:00.000000
+
+"""
+
+from collections.abc import Sequence
+
+import sqlalchemy as sa
+from alembic import op
+
+# revision identifiers, used by Alembic.
+revision: str = "meituan_coupon_image_meta"
+down_revision: str | Sequence[str] | None = "feedback_review_fields"
+branch_labels: str | Sequence[str] | None = None
+depends_on: str | Sequence[str] | None = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table("meituan_coupon", schema=None) as batch_op:
+        batch_op.add_column(sa.Column("image_size", sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column("image_type", sa.String(length=32), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table("meituan_coupon", schema=None) as batch_op:
+        batch_op.drop_column("image_type")
+        batch_op.drop_column("image_size")

alembic/versions/meituan_coupon_table.py

@@ -0,0 +1,62 @@
+"""meituan_coupon table(美团 CPS 券本地缓存,供销量/佣金排序从库里捞、本地排序)
+
+Revision ID: meituan_coupon_table
+Revises: withdraw_review_ad_watch
+Create Date: 2026-06-06 12:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy.dialects import postgresql
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'meituan_coupon_table'
+down_revision: Union[str, Sequence[str], None] = 'withdraw_review_ad_watch'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'meituan_coupon',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('source', sa.String(length=16), nullable=False),
+        sa.Column('platform', sa.Integer(), nullable=False),
+        sa.Column('biz_line', sa.Integer(), nullable=True),
+        sa.Column('city_id', sa.String(length=32), nullable=False),
+        sa.Column('product_view_sign', sa.String(length=128), nullable=False),
+        sa.Column('sku_view_id', sa.String(length=128), nullable=True),
+        sa.Column('name', sa.String(length=256), nullable=True),
+        sa.Column('brand_name', sa.String(length=128), nullable=True),
+        sa.Column('sell_price_cents', sa.Integer(), nullable=True),
+        sa.Column('original_price_cents', sa.Integer(), nullable=True),
+        sa.Column('head_url', sa.String(length=512), nullable=True),
+        sa.Column('sale_volume', sa.String(length=32), nullable=True),
+        sa.Column('sale_volume_num', sa.Integer(), nullable=True),
+        sa.Column('commission_percent', sa.Float(), nullable=True),
+        sa.Column('commission_amount_cents', sa.Integer(), nullable=True),
+        sa.Column('poi_name', sa.String(length=128), nullable=True),
+        sa.Column('available_poi_num', sa.Integer(), nullable=True),
+        sa.Column('delivery_distance_m', sa.Float(), nullable=True),
+        sa.Column('dedup_key', sa.String(length=64), nullable=False),
+        sa.Column('raw', sa.JSON().with_variant(postgresql.JSONB(), 'postgresql'), nullable=False),
+        sa.Column('first_seen', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('last_seen', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('source', 'product_view_sign', name='uq_meituan_coupon_source_sign'),
+    )
+    op.create_index('ix_meituan_coupon_source', 'meituan_coupon', ['source'])
+    op.create_index('ix_meituan_coupon_city_id', 'meituan_coupon', ['city_id'])
+    op.create_index('ix_meituan_coupon_brand_name', 'meituan_coupon', ['brand_name'])
+    op.create_index('ix_meituan_coupon_sale_volume_num', 'meituan_coupon', ['sale_volume_num'])
+    op.create_index('ix_meituan_coupon_commission_percent', 'meituan_coupon', ['commission_percent'])
+    op.create_index('ix_meituan_coupon_dedup_key', 'meituan_coupon', ['dedup_key'])
+    op.create_index('ix_meituan_coupon_last_seen', 'meituan_coupon', ['last_seen'])
+
+
+def downgrade() -> None:
+    op.drop_table('meituan_coupon')

alembic/versions/merge_ad_feed_reward_session_and_withdraw_safety_heads.py

@@ -0,0 +1,26 @@
+"""merge ad feed reward session and withdraw safety heads
+
+Revision ID: 044dce6e9b1f
+Revises: ad_feed_reward_session, withdraw_safety_indexes
+Create Date: 2026-06-09 09:52:08.276767
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = '044dce6e9b1f'
+down_revision: Union[str, Sequence[str], None] = ('ad_feed_reward_session', 'withdraw_safety_indexes')
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    pass
+
+
+def downgrade() -> None:
+    pass

alembic/versions/onboarding_completion_table.py

@@ -0,0 +1,39 @@
+"""onboarding_completion table (新手引导完成标记,按 user+device 去重)
+
+Revision ID: onboarding_completion
+Revises: coupon_daily_completion
+Create Date: 2026-06-10 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'onboarding_completion'
+down_revision: Union[str, Sequence[str], None] = 'coupon_daily_completion'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # (账号, 设备) 一条完成标记;device_id = 客户端硬件级 ANDROID_ID(跨卸载重装稳定)。
+    op.create_table(
+        'onboarding_completion',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('device_id', sa.String(length=64), nullable=False),
+        sa.Column('completed_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('user_id', 'device_id', name='uq_onboarding_user_device'),
+    )
+    with op.batch_alter_table('onboarding_completion', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_onboarding_completion_user_id'), ['user_id'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('onboarding_completion', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_onboarding_completion_user_id'))
+    op.drop_table('onboarding_completion')

alembic/versions/ops_rename_tables.py

@@ -0,0 +1,28 @@
+"""运营可配表加 ops_ 前缀:platform_stat_display→ops_stat_config / marquee_seed→ops_marquee_seed
+
+把首页门面数据的两张「运营可配」表重命名,与代码侧 ops_ 前缀(模型 OpsStatConfig/OpsMarqueeSeed、
+repo ops_stat/ops_marquee、admin ops_stat_config/ops_marquee_seed)对齐。仅改表名,不动列与数据。
+
+Revision ID: opsrename01
+Revises: coin_reward_phase1
+Create Date: 2026-06-07 16:00:00.000000
+"""
+from typing import Sequence, Union
+
+from alembic import op
+
+
+revision: str = 'opsrename01'
+down_revision: Union[str, Sequence[str], None] = 'coin_reward_phase1'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.rename_table('platform_stat_display', 'ops_stat_config')
+    op.rename_table('marquee_seed', 'ops_marquee_seed')
+
+
+def downgrade() -> None:
+    op.rename_table('ops_stat_config', 'platform_stat_display')
+    op.rename_table('ops_marquee_seed', 'marquee_seed')

alembic/versions/platform_stat_anchor_minutes.py

@@ -0,0 +1,30 @@
+"""platform_stat_display 增加 random_anchor_minutes(自增长触发时刻对齐偏移)
+
+Revision ID: pstat2anchor01
+Revises: pstat1d2e3f4a
+Create Date: 2026-06-06 13:00:00.000000
+
+自增长 tick 从「启动时刻 + 间隔」改为「北京时间钟点对齐(anchor + k*interval)」,
+新增列存对齐偏移(距 0 点分钟数)。已有行默认 0(= 对齐到自然边界:天→0点、小时→整点)。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = 'pstat2anchor01'
+down_revision: Union[str, Sequence[str], None] = 'pstat1d2e3f4a'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.add_column(
+        'platform_stat_display',
+        sa.Column('random_anchor_minutes', sa.Integer(), nullable=False, server_default='0'),
+    )
+
+
+def downgrade() -> None:
+    op.drop_column('platform_stat_display', 'random_anchor_minutes')

alembic/versions/platform_stat_display_table.py

@@ -0,0 +1,59 @@
+"""platform_stat_display table (首页三统计展示配置:每指标独立 real/manual/random)
+
+Revision ID: pstat1d2e3f4a
+Revises: withdraw_review_ad_watch
+Create Date: 2026-06-06 12:00:00.000000
+
+播种 3 行:默认 manual + 客户端原写死门面值(12847 人 / 86532 次 / 37621.4 元),
+保证上线前后展示一致,运营再按需切模式。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'pstat1d2e3f4a'
+down_revision: Union[str, Sequence[str], None] = 'withdraw_review_ad_watch'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    table = op.create_table(
+        'platform_stat_display',
+        sa.Column('metric', sa.String(length=32), nullable=False),
+        sa.Column('mode', sa.String(length=16), nullable=False),
+        sa.Column('manual_value', sa.Integer(), nullable=True),
+        sa.Column('random_mult_min', sa.Integer(), nullable=False),
+        sa.Column('random_mult_max', sa.Integer(), nullable=False),
+        sa.Column('random_tick_seconds', sa.Integer(), nullable=False),
+        sa.Column('random_current', sa.Integer(), nullable=True),
+        sa.Column('random_last_tick_at', sa.DateTime(timezone=True), nullable=True),
+        sa.Column('updated_by_admin_id', sa.Integer(), nullable=True),
+        sa.Column(
+            'updated_at', sa.DateTime(timezone=True),
+            server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False,
+        ),
+        sa.PrimaryKeyConstraint('metric'),
+    )
+
+    _common = {
+        'mode': 'manual',
+        'random_mult_min': 1000,  # 1.000(只增不减下限)
+        'random_mult_max': 1100,  # 1.100
+        'random_tick_seconds': 86400,  # 1 天
+    }
+    op.bulk_insert(
+        table,
+        [
+            {'metric': 'help_users', 'manual_value': 12847, **_common},
+            {'metric': 'total_compares', 'manual_value': 86532, **_common},
+            {'metric': 'total_saved', 'manual_value': 3762140, **_common},  # 37621.40 元
+        ],
+    )
+
+
+def downgrade() -> None:
+    op.drop_table('platform_stat_display')

alembic/versions/platform_stat_growth_offset.py

@@ -0,0 +1,39 @@
+"""platform_stat_display 增强:绝对增量增长 / 真实值偏移 / 只增不减护栏
+
+给首页三统计配置加 5 列:
+- random_kind ('mult'/'add') + random_step_min/max:自增长支持「绝对增量」(每周期 +[a,b]),不只是 ×倍率
+- real_offset:真实值模式基数偏移(展示=真实+偏移,冷启动既真实又体面)
+- allow_decrease:门面数字默认「只增不减」,开此开关才允许展示值下降
+
+Revision ID: pstat3growth
+Revises: marquee_seed02
+Create Date: 2026-06-07 12:00:00.000000
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+revision: str = 'pstat3growth'
+down_revision: Union[str, Sequence[str], None] = 'marquee_seed02'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('platform_stat_display', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('random_kind', sa.String(length=8), nullable=False, server_default='mult'))
+        batch_op.add_column(sa.Column('random_step_min', sa.Integer(), nullable=False, server_default='0'))
+        batch_op.add_column(sa.Column('random_step_max', sa.Integer(), nullable=False, server_default='0'))
+        batch_op.add_column(sa.Column('real_offset', sa.Integer(), nullable=False, server_default='0'))
+        batch_op.add_column(sa.Column('allow_decrease', sa.Boolean(), nullable=False, server_default=sa.false()))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('platform_stat_display', schema=None) as batch_op:
+        batch_op.drop_column('allow_decrease')
+        batch_op.drop_column('real_offset')
+        batch_op.drop_column('random_step_max')
+        batch_op.drop_column('random_step_min')
+        batch_op.drop_column('random_kind')

alembic/versions/price_observation_table.py

@@ -0,0 +1,74 @@
+"""price_observation table (价格观测:比价沉淀的价格资产层,server 侧无条件落库)
+
+Revision ID: price_observation_table
+Revises: wx_transfer_auth
+Create Date: 2026-06-07 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'price_observation_table'
+down_revision: Union[str, Sequence[str], None] = 'wx_transfer_auth'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'price_observation',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('observed_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('trace_id', sa.String(length=64), nullable=False),
+        sa.Column('business_type', sa.String(length=16), nullable=False),
+        sa.Column('platform', sa.String(length=32), nullable=False),
+        sa.Column('platform_store_id', sa.String(length=64), nullable=True),
+        sa.Column('store_name', sa.String(length=128), nullable=True),
+        sa.Column('city', sa.String(length=64), nullable=True),
+        sa.Column('geohash', sa.String(length=16), nullable=True),
+        sa.Column('lng', sa.Float(), nullable=True),
+        sa.Column('lat', sa.Float(), nullable=True),
+        sa.Column('is_source', sa.Boolean(), nullable=False),
+        sa.Column('scope', sa.String(length=16), nullable=False),
+        sa.Column('price_cents', sa.Integer(), nullable=True),
+        sa.Column('coupon_saved_cents', sa.Integer(), nullable=True),
+        sa.Column('coupon_name', sa.String(length=64), nullable=True),
+        sa.Column('store_closed', sa.String(length=32), nullable=True),
+        sa.Column('rank', sa.Integer(), nullable=True),
+        # PG 上为 JSONB,其它(SQLite)为 JSON——与模型层 with_variant 对齐
+        sa.Column('dishes', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=True),
+        sa.Column('attrs', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=True),
+        sa.Column('source_device_id', sa.String(length=64), nullable=True),
+        sa.Column('source_user_id', sa.Integer(), nullable=True),
+        sa.Column('confidence', sa.Float(), nullable=False),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.PrimaryKeyConstraint('id'),
+        sa.UniqueConstraint('trace_id', 'platform', 'scope', name='uq_price_obs_trace_platform_scope'),
+    )
+    with op.batch_alter_table('price_observation', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_price_observation_observed_at'), ['observed_at'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_observation_trace_id'), ['trace_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_observation_business_type'), ['business_type'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_observation_platform'), ['platform'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_observation_store_name'), ['store_name'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_observation_geohash'), ['geohash'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_observation_source_device_id'), ['source_device_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_price_observation_source_user_id'), ['source_user_id'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('price_observation', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_price_observation_source_user_id'))
+        batch_op.drop_index(batch_op.f('ix_price_observation_source_device_id'))
+        batch_op.drop_index(batch_op.f('ix_price_observation_geohash'))
+        batch_op.drop_index(batch_op.f('ix_price_observation_store_name'))
+        batch_op.drop_index(batch_op.f('ix_price_observation_platform'))
+        batch_op.drop_index(batch_op.f('ix_price_observation_business_type'))
+        batch_op.drop_index(batch_op.f('ix_price_observation_trace_id'))
+        batch_op.drop_index(batch_op.f('ix_price_observation_observed_at'))
+
+    op.drop_table('price_observation')

alembic/versions/savings_report_fields.py

@@ -0,0 +1,49 @@
+"""savings_record:真实比价上报字段 + (user_id, client_event_id) 幂等唯一约束
+
+把 savings_record 升级为「记账唯一真相表」:真实上报(source='compare')写入
+原价/比价价/支付渠道/平台包名/源平台名/源链接/幂等键/device_id;
+demo seeder 行(source='demo')这些列均为 NULL。
+
+Revision ID: savings_report_fields
+Revises: f01db5d77dac
+Create Date: 2026-05-31 19:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'savings_report_fields'
+down_revision: Union[str, Sequence[str], None] = 'f01db5d77dac'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('savings_record', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('original_price_cents', sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column('compared_price_cents', sa.Integer(), nullable=True))
+        batch_op.add_column(sa.Column('pay_channel', sa.String(length=16), nullable=True))
+        batch_op.add_column(sa.Column('platform_package', sa.String(length=128), nullable=True))
+        batch_op.add_column(sa.Column('source_platform_name', sa.String(length=32), nullable=True))
+        batch_op.add_column(sa.Column('source_deeplink', sa.String(length=512), nullable=True))
+        batch_op.add_column(sa.Column('client_event_id', sa.String(length=64), nullable=True))
+        batch_op.add_column(sa.Column('device_id', sa.String(length=128), nullable=True))
+        # (user_id, client_event_id) 幂等;demo 行 client_event_id 为 NULL 不冲突
+        batch_op.create_unique_constraint('uq_savings_user_event', ['user_id', 'client_event_id'])
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('savings_record', schema=None) as batch_op:
+        batch_op.drop_constraint('uq_savings_user_event', type_='unique')
+        batch_op.drop_column('device_id')
+        batch_op.drop_column('client_event_id')
+        batch_op.drop_column('source_deeplink')
+        batch_op.drop_column('source_platform_name')
+        batch_op.drop_column('platform_package')
+        batch_op.drop_column('pay_channel')
+        batch_op.drop_column('compared_price_cents')
+        batch_op.drop_column('original_price_cents')

alembic/versions/store_mapping_jd_cols.py

@@ -0,0 +1,40 @@
+"""store_mapping 加京东原料列(jd_vender_id + 分享链/反查 URL/deeplink)
+
+Revision ID: store_mapping_jd_cols
+Revises: store_mapping_meituan_cols
+Create Date: 2026-06-13 00:00:00.000000
+
+京东秒送接入店内搜索 deeplink 链路: 3.cn 短链 → 跟随重定向反查 venderId+storeId →
+openapp.jdmobile:// deeplink。京东店铺身份是**两个**稳定数字 id: storeId 进 id_jd(稳定
+店主键, 同 taobao 的 shopId→id_taobao), venderId 进单列 jd_vender_id(deeplink 还需它)。
+其余三列与 taobao_*/meituan_* 原料列平行。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'store_mapping_jd_cols'
+down_revision: Union[str, Sequence[str], None] = 'store_mapping_meituan_cols'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('jd_vender_id', sa.String(length=64), nullable=True))
+        batch_op.add_column(sa.Column('jd_share_url', sa.String(length=256), nullable=True))
+        batch_op.add_column(sa.Column('jd_resolved_url', sa.Text(), nullable=True))
+        batch_op.add_column(sa.Column('jd_deeplink', sa.Text(), nullable=True))
+        batch_op.create_index(batch_op.f('ix_store_mapping_jd_vender_id'), ['jd_vender_id'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_store_mapping_jd_vender_id'))
+        batch_op.drop_column('jd_deeplink')
+        batch_op.drop_column('jd_resolved_url')
+        batch_op.drop_column('jd_share_url')
+        batch_op.drop_column('jd_vender_id')

alembic/versions/store_mapping_jd_dl_invalid.py

@@ -0,0 +1,32 @@
+"""store_mapping 加京东 deeplink 失效标记列 jd_deeplink_invalid_at
+
+Revision ID: store_mapping_jd_dl_invalid
+Revises: store_mapping_tb_dl_invalid
+Create Date: 2026-06-15 00:00:00.000000
+
+京东同淘宝: 缓存的店内搜索 deeplink 会失效(打开是"当前门店超出配送范围"页)。pricebot 比价撞到
+失效页时回退正常搜店, 并 server→server 通知把该 storeId 的 deeplink 标记失效。本列记失效时刻
+(NULL=有效); lookup 反查时过滤掉已失效的京东候选, 不再返回坏 deeplink。
+与淘宝 taobao_deeplink_invalid_at 对称; 用时间戳而非布尔: 留痕可审计、可统计失效率, 不销毁原 deeplink。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'store_mapping_jd_dl_invalid'
+down_revision: Union[str, Sequence[str], None] = 'store_mapping_tb_dl_invalid'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('jd_deeplink_invalid_at', sa.DateTime(timezone=True), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.drop_column('jd_deeplink_invalid_at')

alembic/versions/store_mapping_meituan_cols.py

@@ -0,0 +1,39 @@
+"""store_mapping 加美团原料列(poi_id_str + 分享链/反查 URL/deeplink)
+
+Revision ID: store_mapping_meituan_cols
+Revises: store_mapping_table
+Create Date: 2026-06-13 00:00:00.000000
+
+美团接入店内搜索 deeplink 链路: dpurl.cn 短链 → 302 反查 poi_id_str → imeituan:// deeplink。
+poi_id_str 每次分享重新加密、非稳定主键, 单列 meituan_poi_id_str 存(不占 id_meituan,
+后者留给将来 CPS API 的稳定数字 poi_id)。其余三列与 taobao_* 原料列平行。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'store_mapping_meituan_cols'
+down_revision: Union[str, Sequence[str], None] = 'store_mapping_table'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('meituan_poi_id_str', sa.String(length=64), nullable=True))
+        batch_op.add_column(sa.Column('meituan_share_url', sa.String(length=256), nullable=True))
+        batch_op.add_column(sa.Column('meituan_resolved_url', sa.Text(), nullable=True))
+        batch_op.add_column(sa.Column('meituan_deeplink', sa.Text(), nullable=True))
+        batch_op.create_index(batch_op.f('ix_store_mapping_meituan_poi_id_str'), ['meituan_poi_id_str'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_store_mapping_meituan_poi_id_str'))
+        batch_op.drop_column('meituan_deeplink')
+        batch_op.drop_column('meituan_resolved_url')
+        batch_op.drop_column('meituan_share_url')
+        batch_op.drop_column('meituan_poi_id_str')

alembic/versions/store_mapping_table.py

@@ -0,0 +1,77 @@
+"""store_mapping table (平台店铺表:跨平台同店 id/名 映射,server 侧无条件落库)
+
+Revision ID: store_mapping_table
+Revises: coin_txn_task_ref_uq
+Create Date: 2026-06-13 00:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'store_mapping_table'
+down_revision: Union[str, Sequence[str], None] = 'coin_txn_task_ref_uq'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'store_mapping',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        # 跨平台身份
+        sa.Column('id_taobao', sa.String(length=64), nullable=True),
+        sa.Column('name_taobao', sa.String(length=128), nullable=True),
+        sa.Column('id_meituan', sa.String(length=64), nullable=True),
+        sa.Column('name_meituan', sa.String(length=128), nullable=True),
+        sa.Column('id_jd', sa.String(length=64), nullable=True),
+        sa.Column('name_jd', sa.String(length=128), nullable=True),
+        # 地理
+        sa.Column('city', sa.String(length=64), nullable=True),
+        sa.Column('geohash', sa.String(length=16), nullable=True),
+        sa.Column('lng', sa.Float(), nullable=True),
+        sa.Column('lat', sa.Float(), nullable=True),
+        sa.Column('taobao_address', sa.String(length=256), nullable=True),
+        # 溯源
+        sa.Column('source_platform', sa.String(length=32), nullable=True),
+        sa.Column('business_type', sa.String(length=16), nullable=False),
+        sa.Column('trace_id', sa.String(length=64), nullable=False),
+        sa.Column('source_device_id', sa.String(length=64), nullable=True),
+        sa.Column('source_user_id', sa.Integer(), nullable=True),
+        # 淘宝原料(URL 可能很长 → Text)
+        sa.Column('taobao_share_url', sa.String(length=256), nullable=True),
+        sa.Column('taobao_resolved_url', sa.Text(), nullable=True),
+        sa.Column('taobao_deeplink', sa.Text(), nullable=True),
+        # PG 上为 JSONB,其它(SQLite)为 JSON——与模型层 with_variant 对齐
+        sa.Column('attrs', sa.JSON().with_variant(sa.dialects.postgresql.JSONB(), 'postgresql'), nullable=True),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.PrimaryKeyConstraint('id'),
+        # 一次比价一行,trace_id 幂等去重(防 pricebot 重试 / replay 重复写)
+        sa.UniqueConstraint('trace_id', name='uq_store_mapping_trace'),
+    )
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_store_mapping_id_taobao'), ['id_taobao'], unique=False)
+        batch_op.create_index(batch_op.f('ix_store_mapping_id_meituan'), ['id_meituan'], unique=False)
+        batch_op.create_index(batch_op.f('ix_store_mapping_id_jd'), ['id_jd'], unique=False)
+        batch_op.create_index(batch_op.f('ix_store_mapping_geohash'), ['geohash'], unique=False)
+        batch_op.create_index(batch_op.f('ix_store_mapping_source_platform'), ['source_platform'], unique=False)
+        batch_op.create_index(batch_op.f('ix_store_mapping_source_device_id'), ['source_device_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_store_mapping_source_user_id'), ['source_user_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_store_mapping_created_at'), ['created_at'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_store_mapping_created_at'))
+        batch_op.drop_index(batch_op.f('ix_store_mapping_source_user_id'))
+        batch_op.drop_index(batch_op.f('ix_store_mapping_source_device_id'))
+        batch_op.drop_index(batch_op.f('ix_store_mapping_source_platform'))
+        batch_op.drop_index(batch_op.f('ix_store_mapping_geohash'))
+        batch_op.drop_index(batch_op.f('ix_store_mapping_id_jd'))
+        batch_op.drop_index(batch_op.f('ix_store_mapping_id_meituan'))
+        batch_op.drop_index(batch_op.f('ix_store_mapping_id_taobao'))
+
+    op.drop_table('store_mapping')

alembic/versions/store_mapping_tb_dl_invalid.py

@@ -0,0 +1,33 @@
+"""store_mapping 加淘宝 deeplink 失效标记列 taobao_deeplink_invalid_at
+
+Revision ID: store_mapping_tb_dl_invalid
+Revises: coupon_engage_per_package
+Create Date: 2026-06-15 00:00:00.000000
+
+缓存的淘宝店内搜索 deeplink 会失效(打开是"页面出错了"降级页)。pricebot 比价撞到错误页时
+回退正常搜店, 并 server→server 通知把该 shopId 的 deeplink 标记失效。本列记失效时刻
+(NULL=有效); lookup 反查时过滤掉已失效的淘宝候选, 不再返回坏 deeplink。
+只加淘宝一列(当前只接淘宝); 用时间戳而非布尔: 留痕可审计、可统计失效率, 且不销毁原 deeplink。
+无需索引: 过滤总叠在 name_taobao== 之后, 候选集已小。
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'store_mapping_tb_dl_invalid'
+down_revision: Union[str, Sequence[str], None] = 'coupon_engage_per_package'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('taobao_deeplink_invalid_at', sa.DateTime(timezone=True), nullable=True))
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('store_mapping', schema=None) as batch_op:
+        batch_op.drop_column('taobao_deeplink_invalid_at')

alembic/versions/user_force_onboarding.py

@@ -0,0 +1,39 @@
+"""add user.force_onboarding
+
+运营后台「一键开启新手引导」:置 true → 用户下次启动 App 被强制重走引导教程(即便本地已完成),
+走完引导(/api/v1/user/onboarding/complete)后端自动清回 false,不无限循环。
+见 app/models/user.py force_onboarding 列、admin POST /admin/api/users/{id}/force-onboarding。
+
+Revision ID: user_force_onboarding
+Revises: onboarding_completion
+Create Date: 2026-06-10 21:30:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "user_force_onboarding"
+down_revision: Union[str, Sequence[str], None] = "onboarding_completion"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # sa.false() 渲染成 PG 的 false / SQLite 的 0,两端兼容(同 debug_trace_enabled)。
+    op.add_column(
+        "user",
+        sa.Column(
+            "force_onboarding",
+            sa.Boolean(),
+            nullable=False,
+            server_default=sa.false(),
+        ),
+    )
+
+
+def downgrade() -> None:
+    op.drop_column("user", "force_onboarding")

alembic/versions/wechat_transfer_authorization.py

@@ -0,0 +1,48 @@
+"""wechat_transfer_authorization 表(免确认收款授权)
+
+商家转账「用户授权免确认收款模式」:用户授权一次后,后续提现免逐笔确认直接到账。
+一个用户一条(user_id 主键)。out_authorization_no 我方生成,authorization_id 微信 active 后返回。
+
+Revision ID: wx_transfer_auth
+Revises: withdraw_review_ad_watch
+Create Date: 2026-06-06 12:00:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'wx_transfer_auth'
+down_revision: Union[str, Sequence[str], None] = 'withdraw_review_ad_watch'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_table(
+        'wechat_transfer_authorization',
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('openid', sa.String(length=64), nullable=False),
+        sa.Column('out_authorization_no', sa.String(length=64), nullable=False),
+        sa.Column('authorization_id', sa.String(length=64), nullable=True),
+        sa.Column('state', sa.String(length=16), server_default='pending', nullable=False),
+        sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('user_id'),
+    )
+    with op.batch_alter_table('wechat_transfer_authorization', schema=None) as batch_op:
+        batch_op.create_index(
+            batch_op.f('ix_wechat_transfer_authorization_out_authorization_no'),
+            ['out_authorization_no'],
+            unique=True,
+        )
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('wechat_transfer_authorization', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_wechat_transfer_authorization_out_authorization_no'))
+    op.drop_table('wechat_transfer_authorization')

alembic/versions/withdraw_review_and_ad_watch.py

@@ -0,0 +1,62 @@
+"""withdraw_order.user_name 列 + ad_watch_log 表
+
+提现审核功能:WithdrawOrder 加 user_name(审核异步打款时传给微信的实名,达额转账要求)。
+看广告时长防刷:新增 ad_watch_log 表(前端 onAdClose 上报观看秒数,按 (user_id, watch_date)
+聚合做"每天 50 分钟"硬上限)。
+
+Revision ID: withdraw_review_ad_watch
+Revises: ebb6af5c0b56
+Create Date: 2026-06-05 12:00:00.000000
+
+注:本迁移原 down_revision=ad60a1b2c3d4(分支创建时的 head);rebase 合 main 后 main 侧已新增
+app_config / price_report 等迁移并 merge 出 head=ebb6af5c0b56,故改挂到其上,保持单 head(本迁移
+与那些表互不相干,叠加顺序无影响)。
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = 'withdraw_review_ad_watch'
+down_revision: Union[str, Sequence[str], None] = 'ebb6af5c0b56'
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    # ① 提现单加实名列:审核通过后异步打款时传给微信(达额转账要求),发起提现时存下
+    with op.batch_alter_table('withdraw_order', schema=None) as batch_op:
+        batch_op.add_column(sa.Column('user_name', sa.String(length=64), nullable=True))
+
+    # ② 看广告观看时长表:前端上报观看秒数,(user_id, watch_date) 聚合做每日 50 分钟防刷主闸
+    op.create_table(
+        'ad_watch_log',
+        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
+        sa.Column('user_id', sa.Integer(), nullable=False),
+        sa.Column('watch_seconds', sa.Integer(), server_default='0', nullable=False),
+        sa.Column('watch_date', sa.String(length=10), nullable=False),
+        sa.Column(
+            'created_at', sa.DateTime(timezone=True),
+            server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False,
+        ),
+        sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
+        sa.PrimaryKeyConstraint('id'),
+    )
+    with op.batch_alter_table('ad_watch_log', schema=None) as batch_op:
+        batch_op.create_index(batch_op.f('ix_ad_watch_log_user_id'), ['user_id'], unique=False)
+        batch_op.create_index(batch_op.f('ix_ad_watch_log_watch_date'), ['watch_date'], unique=False)
+        batch_op.create_index(batch_op.f('ix_ad_watch_log_created_at'), ['created_at'], unique=False)
+
+
+def downgrade() -> None:
+    with op.batch_alter_table('ad_watch_log', schema=None) as batch_op:
+        batch_op.drop_index(batch_op.f('ix_ad_watch_log_created_at'))
+        batch_op.drop_index(batch_op.f('ix_ad_watch_log_watch_date'))
+        batch_op.drop_index(batch_op.f('ix_ad_watch_log_user_id'))
+    op.drop_table('ad_watch_log')
+
+    with op.batch_alter_table('withdraw_order', schema=None) as batch_op:
+        batch_op.drop_column('user_name')

alembic/versions/withdraw_safety_indexes.py

@@ -0,0 +1,42 @@
+"""add withdraw concurrency safety indexes
+
+Revision ID: withdraw_safety_indexes
+Revises: 0cf18d590b1d
+Create Date: 2026-06-08 16:20:00.000000
+
+"""
+from typing import Sequence, Union
+
+from alembic import op
+import sqlalchemy as sa
+
+
+# revision identifiers, used by Alembic.
+revision: str = "withdraw_safety_indexes"
+down_revision: Union[str, Sequence[str], None] = "0cf18d590b1d"
+branch_labels: Union[str, Sequence[str], None] = None
+depends_on: Union[str, Sequence[str], None] = None
+
+
+def upgrade() -> None:
+    op.create_index(
+        "ux_withdraw_order_user_active",
+        "withdraw_order",
+        ["user_id"],
+        unique=True,
+        sqlite_where=sa.text("status IN ('reviewing', 'pending')"),
+        postgresql_where=sa.text("status IN ('reviewing', 'pending')"),
+    )
+    op.create_index(
+        "ux_cash_transaction_withdraw_refund_ref",
+        "cash_transaction",
+        ["ref_id"],
+        unique=True,
+        sqlite_where=sa.text("biz_type = 'withdraw_refund' AND ref_id IS NOT NULL"),
+        postgresql_where=sa.text("biz_type = 'withdraw_refund' AND ref_id IS NOT NULL"),
+    )
+
+
+def downgrade() -> None:
+    op.drop_index("ux_cash_transaction_withdraw_refund_ref", table_name="cash_transaction")
+    op.drop_index("ux_withdraw_order_user_active", table_name="withdraw_order")

app/admin/__init__.py

@@ -0,0 +1,6 @@
+"""运营 Admin 后台子应用。
+
+独立 FastAPI app(app.admin.main:admin_app),独立进程/端口运行,复用 App 的
+models/repositories/integrations + 同一个 DB,但鉴权完全隔离(独立 JWT secret)。
+现有 app.main:app 不 import 本包,admin 崩溃不影响 App 主进程。
+"""

app/admin/audit.py

@@ -0,0 +1,38 @@
+"""审计写入门面。
+
+每个 admin 写操作调一次 write_audit,把"谁(admin)在哪个 IP 对什么(target)做了什么
+(action)+ 前后值(detail)"落进 admin_audit_log。
+
+⚠️ 涉钱/涉状态的写操作:传 commit=False,和业务写操作放同一事务一起 commit,
+保证"改了就有痕、有痕就改了"原子(见 plan 风险点 1)。轻量操作可 commit=True。
+"""
+from __future__ import annotations
+
+from sqlalchemy.orm import Session
+
+from app.admin.repositories import audit_log as audit_repo
+from app.models.admin import AdminUser
+
+
+def write_audit(
+    db: Session,
+    admin: AdminUser,
+    *,
+    action: str,
+    target_type: str,
+    target_id: str | int | None = None,
+    detail: dict | None = None,
+    ip: str | None = None,
+    commit: bool = True,
+) -> None:
+    audit_repo.add_audit_log(
+        db,
+        admin_id=admin.id,
+        admin_username=admin.username,
+        action=action,
+        target_type=target_type,
+        target_id=str(target_id) if target_id is not None else None,
+        detail=detail,
+        ip=ip,
+        commit=commit,
+    )

app/admin/deps.py

@@ -0,0 +1,84 @@
+"""Admin API 共享依赖:DB session、当前 admin、角色守卫、客户端 IP。
+
+仿 app/api/deps.py 的 get_current_user,但验的是独立的 admin JWT、查的是 admin_user 表。
+"""
+from __future__ import annotations
+
+from typing import Annotated
+
+from fastapi import Depends, HTTPException, Request, status
+from fastapi.security import HTTPAuthorizationCredentials, HTTPBearer
+from sqlalchemy.orm import Session
+
+from app.admin.repositories import admin_user as admin_repo
+from app.admin.security import AdminTokenError, decode_admin_token
+from app.db.session import get_db
+from app.models.admin import AdminUser
+
+_bearer = HTTPBearer(auto_error=False, scheme_name="AdminBearer")
+
+
+def get_current_admin(
+    credentials: Annotated[HTTPAuthorizationCredentials | None, Depends(_bearer)],
+    db: Annotated[Session, Depends(get_db)],
+) -> AdminUser:
+    """从 Authorization: Bearer <admin_token> 解出当前管理员。失败统一 401。"""
+    if credentials is None or credentials.scheme.lower() != "bearer":
+        raise HTTPException(
+            status_code=status.HTTP_401_UNAUTHORIZED,
+            detail="missing bearer token",
+            headers={"WWW-Authenticate": "Bearer"},
+        )
+    try:
+        payload = decode_admin_token(credentials.credentials)
+    except AdminTokenError as e:
+        raise HTTPException(
+            status_code=status.HTTP_401_UNAUTHORIZED,
+            detail=str(e),
+            headers={"WWW-Authenticate": "Bearer"},
+        ) from e
+
+    admin = admin_repo.get_by_id(db, int(payload["sub"]))
+    if admin is None or admin.status != "active":
+        raise HTTPException(
+            status_code=status.HTTP_401_UNAUTHORIZED,
+            detail="admin not found or disabled",
+            headers={"WWW-Authenticate": "Bearer"},
+        )
+    return admin
+
+
+CurrentAdmin = Annotated[AdminUser, Depends(get_current_admin)]
+AdminDb = Annotated[Session, Depends(get_db)]
+
+
+def require_role(*roles: str):
+    """角色守卫依赖工厂。super_admin 恒通过(全权)。
+
+    用法:在路由签名加 `_: Annotated[AdminUser, Depends(require_role("finance"))]`,
+    或 `dependencies=[Depends(require_role("finance"))]`(不需要拿 admin 时)。
+    """
+    allowed = set(roles)
+
+    def _checker(admin: CurrentAdmin) -> AdminUser:
+        if admin.role != "super_admin" and admin.role not in allowed:
+            need = sorted(allowed | {"super_admin"})
+            raise HTTPException(
+                status_code=status.HTTP_403_FORBIDDEN,
+                detail=f"role '{admin.role}' not allowed (need one of {need})",
+            )
+        return admin
+
+    return _checker
+
+
+def get_client_ip(request: Request) -> str:
+    """取客户端 IP(审计日志用)。生产经 nginx 反代,优先 X-Forwarded-For 第一段;否则直连 IP。
+
+    ⚠️ XFF 可被客户端伪造。nginx 必须用 `proxy_set_header X-Forwarded-For $remote_addr`
+    覆盖客户端传入值(见 M4 部署),否则审计里的 IP 可被伪造。审计 IP 仅作记录、不参与鉴权。
+    """
+    xff = request.headers.get("x-forwarded-for")
+    if xff:
+        return xff.split(",")[0].strip()
+    return request.client.host if request.client else ""

app/admin/main.py

@@ -0,0 +1,110 @@
+"""Admin 后台 FastAPI app(独立进程)。
+
+启动:uvicorn app.admin.main:admin_app --host 127.0.0.1 --port 8771
+复用 App 的 DB/models/repositories/integrations;鉴权独立(admin JWT,见 app/admin/security.py)。
+现有 app.main:app 不 import 本模块,两进程互不影响。
+"""
+from __future__ import annotations
+
+import logging
+from collections.abc import AsyncIterator
+from contextlib import asynccontextmanager
+
+from fastapi import FastAPI
+from fastapi.middleware.cors import CORSMiddleware
+
+from app.admin.routers.ad_audit import router as ad_audit_router
+from app.admin.routers.ad_config import router as ad_config_router
+from app.admin.routers.ad_revenue import router as ad_revenue_router
+from app.admin.routers.admins import router as admins_router
+from app.admin.routers.audit import router as audit_router
+from app.admin.routers.auth import router as auth_router
+from app.admin.routers.comparison import router as comparison_router
+from app.admin.routers.config import router as config_router
+from app.admin.routers.coupon_data import router as coupon_data_router
+from app.admin.routers.cps import router as cps_router
+from app.admin.routers.dashboard import router as dashboard_router
+from app.admin.routers.device_liveness import router as device_liveness_router
+from app.admin.routers.ops_stat_config import router as ops_stat_config_router
+from app.admin.routers.event_logs import router as event_logs_router
+from app.admin.routers.feedback import router as feedback_router
+from app.admin.routers.feedback_qr import router as feedback_qr_router
+from app.admin.routers.onboarding import router as onboarding_router
+from app.admin.routers.ops_marquee_seed import router as ops_marquee_seed_router
+from app.admin.routers.price_report import router as price_report_router
+from app.admin.routers.roles import router as roles_router
+from app.admin.routers.users import router as users_router
+from app.admin.routers.wallet import router as wallet_router
+from app.admin.routers.withdraw import router as withdraw_router
+from app.core.config import settings
+from app.core.logging import setup_logging
+
+setup_logging(debug=settings.APP_DEBUG)
+logger = logging.getLogger("shagua.admin")
+
+
+@asynccontextmanager
+async def lifespan(_: FastAPI) -> AsyncIterator[None]:
+    logger.info(
+        "admin app started env=%s db=%s",
+        settings.APP_ENV,
+        settings.DATABASE_URL.split("://", 1)[0],
+    )
+    yield
+    logger.info("admin app shutting down")
+
+
+admin_app = FastAPI(
+    title=f"{settings.APP_NAME} · Admin",
+    version="0.1.0",
+    docs_url="/admin/docs" if not settings.is_prod else None,
+    redoc_url=None,
+    lifespan=lifespan,
+)
+
+# admin 前端独立部署。生产同域(nginx)无需 CORS;本地 next dev 跨域需放行开发源。
+_dev_origins = [
+    "http://localhost:3001",
+    "http://127.0.0.1:3001",
+    "http://localhost:3000",
+    "http://127.0.0.1:3000",
+]
+_origins = settings.cors_origins_list or ([] if settings.is_prod else _dev_origins)
+if _origins:
+    admin_app.add_middleware(
+        CORSMiddleware,
+        allow_origins=_origins,
+        allow_credentials=True,
+        allow_methods=["*"],
+        allow_headers=["*"],
+    )
+
+
+@admin_app.get("/admin/api/health", tags=["meta"])
+def health() -> dict[str, str]:
+    return {"status": "ok", "service": "admin"}
+
+
+admin_app.include_router(auth_router)
+admin_app.include_router(dashboard_router)
+admin_app.include_router(device_liveness_router)
+admin_app.include_router(ops_stat_config_router)
+admin_app.include_router(ops_marquee_seed_router)
+admin_app.include_router(users_router)
+admin_app.include_router(onboarding_router)
+admin_app.include_router(wallet_router)
+admin_app.include_router(withdraw_router)
+admin_app.include_router(price_report_router)
+admin_app.include_router(feedback_router)
+admin_app.include_router(event_logs_router)
+admin_app.include_router(feedback_qr_router)
+admin_app.include_router(admins_router)
+admin_app.include_router(roles_router)
+admin_app.include_router(audit_router)
+admin_app.include_router(config_router)
+admin_app.include_router(comparison_router)
+admin_app.include_router(cps_router)
+admin_app.include_router(coupon_data_router)
+admin_app.include_router(ad_audit_router)
+admin_app.include_router(ad_config_router)
+admin_app.include_router(ad_revenue_router)

app/admin/permissions.py

@@ -0,0 +1,80 @@
+"""admin 后台「页面权限目录」—— RBAC 的权限侧单一真源。
+
+一个权限 = 一个页面(= 左侧导航项)。角色持有一组 page key,登录后左侧只展示这些页(见前端
+layout.tsx 按 pages 过滤导航)。key 必须与前端路由一级对齐(/dashboard → "dashboard")。
+
+super_admin 为内建全权角色,恒可见全部页(effective_pages 特判)。新增页面 = 这里加一条 +
+前端导航加对应项(key 一致即可被各角色勾选可见)。
+"""
+from __future__ import annotations
+
+SUPER_ADMIN_ROLE = "super_admin"
+
+# 分组镜像前端导航(app/(main)/layout.tsx 的 NAV_GROUPS);key = 路由一级
+PERMISSION_CATALOG: list[dict] = [
+    {"group": "看板", "pages": [
+        {"key": "dashboard", "label": "数据大盘"},
+        {"key": "coupon-data", "label": "领券数据"},
+        {"key": "ad-revenue-report", "label": "广告收益"},
+        {"key": "comparison-records", "label": "比价记录"},
+        {"key": "cps", "label": "CPS收益"},
+        {"key": "device-liveness", "label": "设备存活"},
+    ]},
+    {"group": "奖励审核", "pages": [
+        {"key": "withdraws", "label": "提现审核"},
+        {"key": "price-reports", "label": "低价审核"},
+        {"key": "feedbacks", "label": "用户反馈"},
+    ]},
+    {"group": "数据配置", "pages": [
+        {"key": "config", "label": "系统配置"},
+        {"key": "ad-revenue", "label": "广告配置"},
+        {"key": "users", "label": "用户管理"},
+    ]},
+    {"group": "其他", "pages": [
+        {"key": "admins", "label": "权限管理"},
+        {"key": "event-logs", "label": "埋点日志"},
+        {"key": "audit-logs", "label": "审计日志"},
+    ]},
+]
+
+# 全部页面 key(super_admin 有效可见 = 此全集;也用于校验角色 pages 合法性)
+ALL_PAGE_KEYS: list[str] = [p["key"] for g in PERMISSION_CATALOG for p in g["pages"]]
+_ALL_SET = set(ALL_PAGE_KEYS)
+
+# 内建角色定义(种子 / ensure 兜底的单一真源):(key, 中文展示名, 默认可见页)。
+# 页集对齐 Prototypes/dashboard/permissions.md 的 ROLES;super_admin 恒全权(pages 空、bypass)。
+# key 承重(require_role / admin_user.role),勿改;label 为 UI 展示名。
+BUILTIN_ROLES: list[dict] = [
+    {"name": SUPER_ADMIN_ROLE, "label": "管理员", "pages": []},
+    {"name": "operator", "label": "运营", "pages": [
+        "dashboard", "coupon-data", "ad-revenue-report", "comparison-records",
+        "cps", "device-liveness", "price-reports", "feedbacks",
+    ]},
+    {"name": "finance", "label": "财务", "pages": [
+        "dashboard", "ad-revenue-report", "cps", "withdraws",
+    ]},
+    {"name": "tech", "label": "技术", "pages": [
+        "dashboard", "device-liveness", "config", "ad-revenue", "event-logs", "audit-logs",
+    ]},
+]
+
+# 内建 key → 中文展示名(展示 / 自愈用)
+BUILTIN_LABELS: dict[str, str] = {r["name"]: r["label"] for r in BUILTIN_ROLES}
+
+
+def sanitize_pages(pages: list[str] | None) -> list[str]:
+    """过滤掉不在目录里的 key(去重、保序),防脏数据/目录收缩后的悬空 key。"""
+    seen: set[str] = set()
+    out: list[str] = []
+    for k in pages or []:
+        if k in _ALL_SET and k not in seen:
+            seen.add(k)
+            out.append(k)
+    return out
+
+
+def effective_pages(role_name: str, role_pages: list[str] | None) -> list[str]:
+    """某角色的有效可见页:super_admin → 全部;其余 → 其 pages 与目录取交(自愈悬空 key)。"""
+    if role_name == SUPER_ADMIN_ROLE:
+        return list(ALL_PAGE_KEYS)
+    return sanitize_pages(role_pages)

app/admin/repositories/__init__.py

@@ -0,0 +1 @@
+"""admin 专用数据访问层(跨用户查询 + admin 账号 + 审计 + 大盘聚合)。"""

app/admin/repositories/ad_audit.py

@@ -0,0 +1,292 @@
+"""看广告金币审计:复算 expected_coin 并与实发对比。
+
+只读。复用 [app.core.rewards] 的公式函数(不另写公式,避免与正式发奖口径漂移):
+- 看视频:每条 granted = 1 份,第 N 份 = 该用户 granted 的 reward_video **账号累计**顺序号
+  (与 ad_reward.grant_ad_reward 里 `_granted_cumulative + 1` 一致;LT 因子不按天重置,
+  故复算时要把当日序号叠加上该用户在本日**之前**的累计已发份数)。
+- 信息流:**每条 granted = 1 份**(与 ad_feed_reward.grant_feed_reward 同口径:看满一份即发该条
+  满额,**不按 unit_count 逐份累加**),LT 序号 = 该用户 granted **条数**账号累计
+  (与 ad_feed_reward.granted_unit_total 的 COUNT 一致;不按天重置,复算需叠加本日之前的累计条数)。
+
+非 granted(capped/ecpm_missing)不占用份序号、应发恒 0,据此校验闸口是否确实没发。
+"""
+from __future__ import annotations
+
+from sqlalchemy import func, select
+from sqlalchemy.orm import Session
+
+from app.core import rewards
+from app.models.ad_feed_reward import AdFeedRewardRecord
+from app.models.ad_reward import AdRewardRecord
+from app.repositories.ad_feed_reward import FEED_REWARD_UNIT_SECONDS
+
+
+def _prior_granted_counts(
+    db: Session, *, date: str, user_id: int | None
+) -> dict[int, int]:
+    """各用户在 date **之前**已发奖的 reward_video 累计份数,作为当日复算的 LT 序号起点。
+    LT 因子改账号累计后,当日第 1 份并非全局第 1 份,需叠加历史累计。"""
+    stmt = (
+        select(AdRewardRecord.user_id, func.count())
+        .where(
+            AdRewardRecord.reward_date < date,
+            AdRewardRecord.reward_scene == "reward_video",
+            AdRewardRecord.status == "granted",
+        )
+        .group_by(AdRewardRecord.user_id)
+    )
+    if user_id is not None:
+        stmt = stmt.where(AdRewardRecord.user_id == user_id)
+    return {uid: n for uid, n in db.execute(stmt).all()}
+
+
+def _reward_video_rows(
+    db: Session, *, date: str, user_id: int | None
+) -> list[dict]:
+    """看视频记录复算。按 (user_id, created_at) 升序还原账号累计第 N 份(含本日之前的累计)。"""
+    stmt = (
+        select(AdRewardRecord)
+        .where(
+            AdRewardRecord.reward_date == date,
+            AdRewardRecord.reward_scene == "reward_video",
+        )
+        .order_by(AdRewardRecord.user_id, AdRewardRecord.created_at, AdRewardRecord.id)
+    )
+    if user_id is not None:
+        stmt = stmt.where(AdRewardRecord.user_id == user_id)
+
+    # 用本日之前的累计份数做起点,当日 granted 在其上继续递增 → 与 _granted_cumulative+1 对齐
+    granted_n: dict[int, int] = _prior_granted_counts(db, date=date, user_id=user_id)
+    rows: list[dict] = []
+    for rec in db.execute(stmt).scalars():
+        if rec.status == "granted":
+            nth = granted_n.get(rec.user_id, 0) + 1
+            granted_n[rec.user_id] = nth
+            expected = rewards.calculate_ad_reward_coin(rec.ecpm_raw, nth)
+            rows.append({
+                "scene": "reward_video",
+                "record_id": rec.id,
+                "user_id": rec.user_id,
+                "ad_session_id": rec.ad_session_id,
+                "app_env": rec.app_env,
+                "our_code_id": rec.our_code_id,
+                "created_at": rec.created_at,
+                "status": rec.status,
+                "ecpm": rec.ecpm_raw,
+                "ecpm_factor": rewards.ad_ecpm_factor(rewards.parse_ecpm_yuan(rec.ecpm_raw)),
+                "units": 1,
+                "lt_index_start": nth,
+                "lt_index_end": nth,
+                "lt_factor_start": rewards.ad_lt_factor(nth),
+                "lt_factor_end": rewards.ad_lt_factor(nth),
+                "expected_coin": expected,
+                "actual_coin": rec.coin,
+                "matched": expected == rec.coin,
+            })
+        else:
+            # capped / ecpm_missing:不发金币,校验实发确为 0
+            rows.append({
+                "scene": "reward_video",
+                "record_id": rec.id,
+                "user_id": rec.user_id,
+                "ad_session_id": rec.ad_session_id,
+                "app_env": rec.app_env,
+                "our_code_id": rec.our_code_id,
+                "created_at": rec.created_at,
+                "status": rec.status,
+                "ecpm": rec.ecpm_raw,
+                "ecpm_factor": None,
+                "units": 1,
+                "lt_index_start": None,
+                "lt_index_end": None,
+                "lt_factor_start": None,
+                "lt_factor_end": None,
+                "expected_coin": 0,
+                "actual_coin": rec.coin,
+                "matched": rec.coin == 0,
+            })
+    return rows
+
+
+def _feed_prior_granted_count(
+    db: Session, *, date: str, user_id: int | None
+) -> dict[int, int]:
+    """各用户在 date **之前** granted 的信息流**条数**累计,作为当日复算的 LT 序号起点。
+
+    与发奖侧 ad_feed_reward.granted_unit_total(COUNT status=granted)对齐:一条广告 = 1 份,
+    LT 按账号累计**条数**递进。**不再用 SUM(unit_count)**——那是「一条按时长折多份」的过时口径,
+    与现行发奖(每条 1 份)漂移,会让 unit_count>1 的记录复算虚高、对账恒「不符」。"""
+    stmt = (
+        select(
+            AdFeedRewardRecord.user_id,
+            func.count(),
+        )
+        .where(
+            AdFeedRewardRecord.reward_date < date,
+            AdFeedRewardRecord.status == "granted",
+        )
+        .group_by(AdFeedRewardRecord.user_id)
+    )
+    if user_id is not None:
+        stmt = stmt.where(AdFeedRewardRecord.user_id == user_id)
+    return {uid: int(n) for uid, n in db.execute(stmt).all()}
+
+
+def _feed_scene_matches(rec: AdFeedRewardRecord, scene: str | None) -> bool:
+    """该信息流记录是否落入请求的展示筛选 scene。
+    - scene=="feed":ad_type in ("feed", NULL)(旧数据 NULL 视为 feed,向后兼容)
+    - scene=="draw":ad_type=="draw"
+    - scene=="feed_all":所有信息流(feed/draw/NULL 都要)——业务已全切 Draw 信息流,收益报表把「Draw 信息流」
+      当作整个信息流口径(含历史误标 feed/NULL),用它避免筛选漏历史。
+    - scene 为 None:不筛(两类都要)。
+    """
+    if scene == "feed":
+        return rec.ad_type in (None, "feed")
+    if scene == "draw":
+        return rec.ad_type == "draw"
+    if scene == "feed_all":
+        return True
+    return True
+
+
+def _feed_rows(
+    db: Session, *, date: str, user_id: int | None, scene: str | None = None
+) -> list[dict]:
+    """信息流记录复算。**每条 granted = 1 份**(与发奖同口径,不按 unit_count 累加),
+    LT 序号沿用账号累计**条数**(含本日之前)。
+
+    **关键:LT 因子账号累计按全表 granted 条数累计(feed+draw 共享同一发奖池/上限),不按 ad_type 拆分**——
+    故无论 scene 怎么筛展示,这里都遍历当日**全部**信息流记录维持 granted_count 累加;scene 只决定
+    哪些行被**留下展示**(由 _feed_scene_matches 判断),不影响累计基线,保证复算序号与正式发奖一致。
+    """
+    stmt = (
+        select(AdFeedRewardRecord)
+        .where(AdFeedRewardRecord.reward_date == date)
+        .order_by(AdFeedRewardRecord.user_id, AdFeedRewardRecord.created_at, AdFeedRewardRecord.id)
+    )
+    if user_id is not None:
+        stmt = stmt.where(AdFeedRewardRecord.user_id == user_id)
+
+    # 本日之前的累计**条数**做起点,与发奖侧 granted_unit_total(COUNT granted)对齐
+    granted_count: dict[int, int] = _feed_prior_granted_count(db, date=date, user_id=user_id)
+    rows: list[dict] = []
+    for rec in db.execute(stmt).scalars():
+        keep = _feed_scene_matches(rec, scene)  # 累计照常推进,这里只决定是否展示本行
+        if rec.status == "granted":
+            # 一条广告 = 1 份(与 grant_feed_reward 同口径:看满一份即发该条满额,不按 unit_count 累加)。
+            # nth = 账号累计第几**条**(含本日之前),与发奖侧 granted_unit_total+1 对齐;累计照常推进
+            # (即便 scene 不匹配不展示也要 +1,保证序号与正式发奖一致)。
+            nth = granted_count.get(rec.user_id, 0) + 1
+            granted_count[rec.user_id] = nth
+            if not keep:
+                continue
+            expected = rewards.calculate_ad_reward_coin(rec.ecpm_raw, nth)
+            rows.append({
+                "scene": "feed",
+                "ad_type": rec.ad_type or "feed",
+                "feed_scene": rec.feed_scene,
+                "record_id": rec.id,
+                "user_id": rec.user_id,
+                "ad_session_id": rec.ad_session_id,
+                "trace_id": rec.trace_id,
+                "app_env": rec.app_env,
+                "our_code_id": rec.our_code_id,
+                "created_at": rec.created_at,
+                "status": rec.status,
+                "ecpm": rec.ecpm_raw,
+                "ecpm_factor": rewards.ad_ecpm_factor(rewards.parse_ecpm_yuan(rec.ecpm_raw)),
+                "units": 1,
+                "lt_index_start": nth,
+                "lt_index_end": nth,
+                "lt_factor_start": rewards.ad_lt_factor(nth),
+                "lt_factor_end": rewards.ad_lt_factor(nth),
+                "expected_coin": expected,
+                "actual_coin": rec.coin,
+                "matched": expected == rec.coin,
+            })
+        else:
+            if not keep:
+                continue
+            rows.append({
+                "scene": "feed",
+                "ad_type": rec.ad_type or "feed",
+                "feed_scene": rec.feed_scene,
+                "record_id": rec.id,
+                "user_id": rec.user_id,
+                "ad_session_id": rec.ad_session_id,
+                "trace_id": rec.trace_id,
+                "app_env": rec.app_env,
+                "our_code_id": rec.our_code_id,
+                "created_at": rec.created_at,
+                "status": rec.status,
+                "ecpm": rec.ecpm_raw,
+                "ecpm_factor": None,
+                "units": rec.unit_count,
+                "lt_index_start": None,
+                "lt_index_end": None,
+                "lt_factor_start": None,
+                "lt_factor_end": None,
+                "expected_coin": 0,
+                "actual_coin": rec.coin,
+                "matched": rec.coin == 0,
+            })
+    return rows
+
+
+def audit_rows(
+    db: Session, *, date: str, user_id: int | None, scene: str | None = None
+) -> list[dict]:
+    """当日逐条发奖复算行(未排序)。scene: None=两类 / "reward_video" / "feed" / "draw"。
+
+    "feed" 与 "draw" 都查 ad_feed_reward_record(同一发奖表),按 ad_type 区分:feed 含历史 NULL,
+    draw 仅 ad_type=="draw"。信息流行额外带 `ad_type`/`feed_scene`,供收益报表区分比价/领券 Draw 收益。
+    每行含 `app_env`/`our_code_id`/`expected_coin`/`actual_coin` 等,供金币审计逐条对账,
+    也供广告收益报表把「应发/实发」按 用户×类型×应用×代码位 聚合(见 ad_revenue,复用同一复算口径)。
+    **LT 因子账号累计仍按全表 unit 累计(feed+draw 共享),scene 只筛展示,不拆累计。**
+    """
+    rows: list[dict] = []
+    if scene in (None, "reward_video"):
+        rows.extend(_reward_video_rows(db, date=date, user_id=user_id))
+    if scene in (None, "feed", "draw", "feed_all"):
+        rows.extend(_feed_rows(db, date=date, user_id=user_id, scene=scene))
+    return rows
+
+
+def ad_coin_audit(
+    db: Session,
+    *,
+    date: str,
+    user_id: int | None,
+    scene: str | None,
+    limit: int,
+    only_mismatch: bool = False,
+) -> dict:
+    """当日发奖复算。返回 {total, mismatch_count, truncated, items}。
+
+    scene: None=两类都要 / "reward_video" / "feed";only_mismatch=True 只展示不一致(✗)行。
+    关键:`total` 与 `mismatch_count` 在**全量**(截断前)上统计,故对账数字始终可信,不受 limit
+    影响;`items` 才是展示集(only_mismatch 时只取 ✗ 行)按 created_at 倒序截断到 limit。
+    份序号在全天数据上已算好,limit 只影响展示条数、不影响 expected 复算正确性。
+    """
+    rows = audit_rows(db, date=date, user_id=user_id, scene=scene)
+    rows.sort(key=lambda r: (r["created_at"], r["record_id"]), reverse=True)
+
+    total = len(rows)
+    mismatch_count = sum(1 for r in rows if not r["matched"])
+    display = [r for r in rows if not r["matched"]] if only_mismatch else rows
+    return {
+        "total": total,
+        "mismatch_count": mismatch_count,
+        "truncated": len(display) > limit,
+        "items": display[:limit],
+    }
+
+
+def formula_snapshot() -> dict:
+    """当前公式参数快照(给前端展示规则参照)。直接读 rewards 常量,与发奖同源。"""
+    return {
+        "coin_per_yuan": rewards.COIN_PER_YUAN,
+        "feed_unit_seconds": FEED_REWARD_UNIT_SECONDS,
+        "ecpm_factor_tiers": [list(t) for t in rewards.AD_ECPM_FACTOR_TABLE],
+        "lt_factor_tiers": [list(t) for t in rewards.AD_LT_FACTOR_TABLE],
+    }

app/admin/repositories/ad_revenue.py

@@ -0,0 +1,421 @@
+"""admin 广告收益报表:**逐条广告事件**列表(每行一次广告,含展示 + 发奖对账)。
+
+只读。每行 = 一次广告事件(不再按用户聚合):
+- **激励视频**:一次观看 = 1 条展示(ad_ecpm)+ 1 条发奖(ad_reward),按 ad_session_id 合并成一行,
+  直接给出 eCPM / 收益 + 状态 / 应发 / 实发 / 一致;点开看该条金币复算因子。
+- **信息流(比价/领券)**:一次比价 / 一次领券 = 一条整场发奖(ad_feed_reward)一行,给出 eCPM /
+  发奖金币 + 应发 / 实发 / 一致;点开看金币复算因子。⚠️ draw 的逐条展示(ad_ecpm,impressionId 各自
+  独立、与整场发奖无公共键、无法归到「哪一次」)**不再单独占行**(2026-07 按「一次比价/领券放一块」调整)——
+  其展示数 / eCPM / 预估收益仍进全量统计(合计 / 趋势 / 分类大盘 / 穿山甲对照),只是主表不逐条铺开。
+- 兜底:激励视频有展示无发奖(中途关 / 未达发奖)、有发奖无展示(未上报 eCPM)仍各自成行。
+
+展示与收益来自 ad_ecpm_record(收益 = eCPM元 ÷ 1000);应发 / 实发金币复用金币审计逐条复算
+(ad_audit.audit_rows,与正式发奖同一公式口径,不另写公式)。合计与对账在全量上统计,
+不受 limit(只截断 items)影响。
+
+每行带 ad_type(reward_video/feed/draw)与 feed_scene(comparison/coupon/welfare),供前端区分
+「比价 Draw 收益」与「领券 Draw 收益」(比价/领券共用同一代码位,只能靠 feed_scene 分)。
+
+⚠️ 局限:① 历史信息流/Draw 发奖 ad_type 为 NULL 的旧记录统一视为 feed(向后兼容);Draw 仅
+ad_type=="draw" 的新记录单独成类。② 跨天 S2S 回调:同一次广告的展示与发奖偶尔落相邻日,各自按
+report_date / reward_date 归日。
+"""
+from __future__ import annotations
+
+from datetime import UTC, datetime, timedelta
+from datetime import date as _date
+
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from app.admin.repositories import ad_audit
+from app.admin.repositories import stats as admin_stats
+from app.core import rewards
+from app.models.ad_ecpm import AdEcpmRecord
+from app.models.user import User
+from app.repositories import ad_pangle_revenue
+
+
+def _cn_hour(dt: datetime) -> int:
+    """created_at(UTC 口径)→ 北京时间小时(0–23)。naive 当 UTC 处理(sqlite),tz-aware 直接换算(pg)。"""
+    if dt.tzinfo is None:
+        dt = dt.replace(tzinfo=UTC)
+    return dt.astimezone(rewards.CN_TZ).hour
+
+
+def _date_range(date_from: str, date_to: str) -> list[str]:
+    """闭区间内逐日 'YYYY-MM-DD' 串(含首尾)。date_from > date_to 时返回空。"""
+    d0 = _date.fromisoformat(date_from)
+    d1 = _date.fromisoformat(date_to)
+    out: list[str] = []
+    d = d0
+    while d <= d1:
+        out.append(d.isoformat())
+        d += timedelta(days=1)
+    return out
+
+
+# 报表 ad_type 与审计 scene 取值一致(reward_video / feed / draw):feed 与 draw 同查发奖表
+# ad_feed_reward_record,由 audit 内部按 ad_type 区分(feed 含历史 NULL,draw 仅 ad_type=="draw")。
+_AUDIT_SCENES = {"reward_video", "feed", "draw"}
+
+
+# 发奖复算明细字段(展开下钻看「金币怎么算出来的」)——从 audit 行原样取这些 key。
+_REWARD_DETAIL_KEYS = (
+    "record_id", "created_at", "status", "ecpm", "ecpm_factor", "units",
+    "lt_index_start", "lt_index_end", "lt_factor_start", "lt_factor_end",
+    "expected_coin", "actual_coin", "matched",
+)
+
+
+def _reward_detail(row: dict) -> dict:
+    """从 audit 行抽出发奖复算明细(给前端展开行渲染因子1/因子2/份数/LT/应发实发)。"""
+    return {k: row[k] for k in _REWARD_DETAIL_KEYS}
+
+
+def ad_revenue_report(
+    db: Session,
+    *,
+    date_from: str,
+    date_to: str,
+    user_id: int | None = None,
+    ad_type: str | None = None,
+    feed_scene: str | None = None,
+    app_env: str | None = None,
+    granularity: str = "day",
+    limit: int = 500,
+    offset: int = 0,
+    sort: str = "time",
+) -> dict:
+    """日期区间(北京时间,闭区间)**逐条广告事件**列表 + 发奖对账。单日时 date_from==date_to。
+
+    每个 item = 一次广告事件(展示与发奖按 ad_session_id 合并;信息流展示 / 发奖各自成行)。
+    ad_type: None=全部 / reward_video / feed / draw。feed_scene: None=全部 /
+    comparison / coupon / welfare,作为全局筛选(同时作用于明细、合计与 daily/hourly 趋势)。
+    granularity=hour 时每行带北京小时(由各自时间算),并额外返回全量 hourly 序列。
+    事件按时间倒序(新→旧)排列;limit/offset 对排序后的全量做分页切片(items 为当前页),
+    total 与 total_* / daily / hourly 在全量上统计,不受分页影响。
+    """
+    by_hour = granularity == "hour"
+
+    # 1) 发奖行(逐日 audit 复算):建 (user_id, ad_session_id) → [行] 映射用于和展示合并;
+    #    同时保留全量列表,未被展示合并的成「纯发奖」事件。
+    reward_by_session: dict[tuple[int, str], list[dict]] = {}
+    all_reward_rows: list[dict] = []
+    # 报表 ad_type → audit scene:reward_video/feed 直传;**draw(前端「Draw 信息流」)映射成 feed_all**
+    # ——业务已全切 Draw,把「Draw 信息流」当作整个信息流口径(含历史误标 feed/NULL),否则筛选会漏历史。
+    if ad_type == "draw":
+        audit_scene = "feed_all"
+    elif ad_type in _AUDIT_SCENES:
+        audit_scene = ad_type
+    else:
+        audit_scene = None
+    if ad_type is None or audit_scene is not None:
+        for d in _date_range(date_from, date_to):
+            for row in ad_audit.audit_rows(db, date=d, user_id=user_id, scene=audit_scene):
+                row["_report_date"] = d
+                all_reward_rows.append(row)
+                sid = row.get("ad_session_id")
+                if sid:
+                    reward_by_session.setdefault((row["user_id"], sid), []).append(row)
+
+    used_reward_ids: set[int] = set()
+    events: list[dict] = []
+
+    def _pop_reward(uid: int, sid: str | None) -> dict | None:
+        """取一条与 (uid, sid) 匹配且未被用过的发奖行(激励视频展示↔发奖按会话 1:1 合并)。"""
+        if not sid:
+            return None
+        for r in reward_by_session.get((uid, sid), ()):
+            if r["record_id"] not in used_reward_ids:
+                used_reward_ids.add(r["record_id"])
+                return r
+        return None
+
+    # 2) 展示记录(ad_ecpm):每条一个事件;能匹配到发奖则合并成「展示 + 发奖」一行。
+    stmt = select(AdEcpmRecord).where(
+        AdEcpmRecord.report_date >= date_from,
+        AdEcpmRecord.report_date <= date_to,
+    )
+    if user_id is not None:
+        stmt = stmt.where(AdEcpmRecord.user_id == user_id)
+    if ad_type == "draw":
+        # draw = 所有信息流展示(业务已全 Draw,含历史误标 feed);展示行只进统计,不占主表行
+        stmt = stmt.where(AdEcpmRecord.ad_type.in_(["draw", "feed"]))
+    elif ad_type is not None:
+        stmt = stmt.where(AdEcpmRecord.ad_type == ad_type)
+    for rec in db.execute(stmt).scalars():
+        rwd = _pop_reward(rec.user_id, rec.ad_session_id)
+        ev = {
+            "event_key": f"imp-{rec.id}",
+            "report_date": rec.report_date,
+            "user_id": rec.user_id,
+            "ad_type": rec.ad_type,
+            "feed_scene": rec.feed_scene,
+            "app_env": rec.app_env,
+            "our_code_id": rec.our_code_id,
+            "created_at": rec.created_at,
+            "hour": _cn_hour(rec.created_at) if by_hour else None,
+            "has_impression": True,
+            "impressions": 1,
+            "ecpm": rec.ecpm_raw,
+            # 单次展示收益(元)= eCPM元 ÷ 1000(每千次→单次)。eCPM 先钳到 AD_ECPM_MAX_FEN(¥500 CPM)
+            # 再折收益,与发奖口径 [rewards.calculate_ad_reward_coin] 一致(2026-06-29 修:原裸 parse_ecpm_yuan
+            # 不钳,伪造/异常天价 eCPM 会把报表预估收益冲到任意大;金币侧已钳、收益侧漏钳)。
+            "revenue_yuan": round(
+                min(rewards.parse_ecpm_yuan(rec.ecpm_raw), rewards.AD_ECPM_MAX_FEN / 100.0) / 1000.0, 6,
+            ),
+            "adn": rec.adn,
+            "slot_id": rec.slot_id,
+            "sub_rewards": [],
+            "sub_count": 1,
+        }
+        if rwd is not None:
+            ev.update({
+                "has_reward": True,
+                "status": rwd["status"],
+                "expected_coin": int(rwd["expected_coin"]),
+                "actual_coin": int(rwd["actual_coin"]),
+                "matched": bool(rwd["matched"]),
+                "reward_detail": _reward_detail(rwd),
+            })
+        else:
+            # 纯展示(信息流逐条展示、激励视频缺发奖记录):不计对账,matched=True。
+            ev.update({
+                "has_reward": False, "status": None,
+                "expected_coin": 0, "actual_coin": 0, "matched": True,
+                "reward_detail": None,
+            })
+        events.append(ev)
+
+    # 3) 未被展示合并的发奖行 → 事件:
+    #    - 激励视频(reward_video):逐条成「纯发奖」事件(每次一个 ad_session_id;有发奖无展示等)。
+    #    - 信息流(feed/draw):同一次比价/领券的多条广告共享**整场 ad_session_id**(客户端整场复用),
+    #      按 (user_id, ad_session_id) 聚成**一次比价 / 一次领券**父事件;sub_rewards 为组内逐条明细,
+    #      应发/实发取组内合计;业务已全 Draw → 类型统一 "draw"。session 缺失(极少旧数据)各自单独成组。
+    feed_groups: dict[tuple[int, str], list[dict]] = {}
+    for row in all_reward_rows:
+        if row["record_id"] in used_reward_ids:
+            continue
+        if row["scene"] == "reward_video":
+            events.append({
+                "event_key": f"rwd-{row['record_id']}",
+                "report_date": row["_report_date"],
+                "user_id": row["user_id"],
+                "ad_type": "reward_video",
+                "feed_scene": row.get("feed_scene"),
+                "app_env": row.get("app_env"),
+                "our_code_id": row.get("our_code_id"),
+                "created_at": row["created_at"],
+                "hour": _cn_hour(row["created_at"]) if by_hour else None,
+                "has_impression": False,
+                "impressions": 0,
+                "ecpm": row["ecpm"],
+                "revenue_yuan": 0.0,
+                "adn": None,
+                "slot_id": None,
+                "has_reward": True,
+                "status": row["status"],
+                "expected_coin": int(row["expected_coin"]),
+                "actual_coin": int(row["actual_coin"]),
+                "matched": bool(row["matched"]),
+                "reward_detail": _reward_detail(row),
+                "sub_rewards": [],
+                "sub_count": 1,
+            })
+        else:
+            # 聚合单位 = 一次完整比价/领券流程:优先用 trace_id(比价带 comparisonTraceId、领券带 sessionTraceId,
+            # 整个流程不变;即使中途点广告致浮层关闭重弹、ad_session_id 变了,trace_id 仍不变 → 全流程聚成一行)。
+            # 无 trace_id(历史领券未上报 / 旧数据)回退整场 ad_session_id;再无则 record_id 各自成组、不误并。
+            grp_key = row.get("trace_id") or row.get("ad_session_id") or f"_rid-{row['record_id']}"
+            feed_groups.setdefault((row["user_id"], grp_key), []).append(row)
+
+    # 信息流分组 → 「一次比价 / 一次领券」父事件(收益恒 0:收益只算展示侧,避免与展示行重复计)。
+    for (uid, grp_key), group in feed_groups.items():
+        group.sort(key=lambda r: (r["created_at"], r["record_id"]))
+        rep = group[-1]  # 代表条(最新一条):时间/场景/应用/代码位取它
+        expected_sum = sum(int(g["expected_coin"]) for g in group)
+        actual_sum = sum(int(g["actual_coin"]) for g in group)
+        # 父行 eCPM:组内各条 eCPM(分)均值(展示用,各条不同);无有效值则取代表条
+        ecpm_fens = [rewards.parse_ecpm_fen(g["ecpm"]) for g in group if g.get("ecpm")]
+        avg_ecpm = str(round(sum(ecpm_fens) / len(ecpm_fens))) if ecpm_fens else rep.get("ecpm")
+        # 主表逐行显示用:这次发奖广告的预估收益之和(发奖侧 eCPM 折算,钳顶同展示侧)。只放进
+        # row_revenue_yuan 给主表逐行展示,不进 revenue_yuan/合计/趋势——避免与展示侧 total 重复计。
+        row_revenue = round(sum(
+            min(rewards.parse_ecpm_yuan(g["ecpm"]), rewards.AD_ECPM_MAX_FEN / 100.0) / 1000.0
+            for g in group if g.get("ecpm")
+        ), 6)
+        events.append({
+            "event_key": f"feedgrp-{uid}-{grp_key}",
+            "report_date": rep["_report_date"],
+            "user_id": uid,
+            "ad_type": "draw",  # 业务已全切 Draw 信息流,聚合行统一 draw
+            "feed_scene": rep.get("feed_scene"),
+            "app_env": rep.get("app_env"),
+            "our_code_id": rep.get("our_code_id"),
+            "created_at": rep["created_at"],
+            "hour": _cn_hour(rep["created_at"]) if by_hour else None,
+            "has_impression": False,
+            "impressions": 0,
+            "ecpm": avg_ecpm,
+            "revenue_yuan": 0.0,
+            "row_revenue_yuan": row_revenue,
+            "adn": None,
+            "slot_id": None,
+            "has_reward": True,
+            "status": rep["status"],  # 代表状态(逐条见展开)
+            "expected_coin": expected_sum,
+            "actual_coin": actual_sum,
+            "matched": all(bool(g["matched"]) for g in group),
+            "reward_detail": None,
+            "sub_rewards": [_reward_detail(g) for g in group],
+            "sub_count": len(group),
+        })
+
+    # 「场景」作为全局筛选(与 user_id/ad_type 一致):同时作用于明细、合计与 daily/hourly 趋势。
+    # feed_scene 仅信息流 / Draw 有值,激励视频与旧数据为 None;选中后只保留该场景事件。
+    if feed_scene is not None:
+        events = [e for e in events if e.get("feed_scene") == feed_scene]
+
+    # app_env 过滤(2026-06-29 新增能力,修隐患:测试应用上报的假 eCPM 如 ¥678 CPM 会污染正式收益合计/平均):
+    # 显式传 "prod"/"test" 只看该环境;不传=全部(维持现状)。**不擅自把默认改成排除 test**——本地 dev 库多为
+    # test 数据、默认排除会使本地报表空,且「正式报表是否含 test」属产品口径。建议前端报表页加 app_env 筛选器
+    # (默认选 prod),或产品确认后再把默认改成排除 test。注:穿山甲后台收益列(total_pangle_*)暂未联动此过滤
+    # (它是独立对照列,且 pangle 的 test 是真实小额、非客户端那种假值)。
+    if app_env is not None:
+        events = [e for e in events if e.get("app_env") == app_env]
+
+    # 排序:time=按时间倒序(新→旧);ecpm=按 eCPM 数值倒序(eCPM 原值是字符串「分」,转数值排;
+    # 纯发奖行用其发奖采用的 eCPM,缺失/非法计 0 排末尾)。
+    if sort == "ecpm":
+        events.sort(key=lambda e: rewards.parse_ecpm_fen(e["ecpm"]), reverse=True)
+    else:
+        events.sort(key=lambda e: (e["report_date"], e["created_at"]), reverse=True)
+
+    # 补手机号(admin 展示用,完整不脱敏,与用户 / 钱包 / 比价记录页一致):批量一次查,避免 N+1。
+    uids = {e["user_id"] for e in events}
+    phone_map: dict[int, str] = {}
+    if uids:
+        phone_map = {
+            uid: phone
+            for uid, phone in db.execute(
+                select(User.id, User.phone).where(User.id.in_(uids))
+            ).all()
+        }
+    for e in events:
+        e["user_phone"] = phone_map.get(e["user_id"])
+
+    total_impressions = sum(e["impressions"] for e in events)
+    total_revenue_yuan = round(sum(e["revenue_yuan"] for e in events), 6)
+    total_expected_coin = sum(e["expected_coin"] for e in events)
+    total_actual_coin = sum(e["actual_coin"] for e in events)
+    mismatch_count = sum(1 for e in events if e["has_reward"] and not e["matched"])
+
+    # 按日期汇总(全量,不受 limit):供前端按天趋势图。
+    daily_map: dict[str, dict] = {}
+    for e in events:
+        d = daily_map.get(e["report_date"])
+        if d is None:
+            d = {"date": e["report_date"], "impressions": 0, "revenue_yuan": 0.0,
+                 "expected_coin": 0, "actual_coin": 0}
+            daily_map[e["report_date"]] = d
+        d["impressions"] += e["impressions"]
+        d["revenue_yuan"] += e["revenue_yuan"]
+        d["expected_coin"] += e["expected_coin"]
+        d["actual_coin"] += e["actual_coin"]
+    daily = [
+        {**d, "revenue_yuan": round(d["revenue_yuan"], 6)}
+        for d in sorted(daily_map.values(), key=lambda x: x["date"])
+    ]
+
+    # 穿山甲后台收益(GroMore 数据 API,T+1 入库 ad_pangle_daily_revenue):汇总 + 按天趋势级展示,
+    # 与上面客户端自报 eCPM 折算的预估并列对照(看 gap)。穿山甲数据**无用户/场景/类型维度**,故仅在
+    # 「全量视图」(未按 user_id / ad_type / feed_scene 过滤)给值;一旦带这些过滤,穿山甲数无法对应口径
+    # → 置 None,前端显示「-」并提示。逐条事件行不动(仍是客户端预估)。
+    pangle_filterable = user_id is None and ad_type is None and feed_scene is None
+    total_pangle_revenue_yuan: float | None = None
+    total_pangle_api_revenue_yuan: float | None = None
+    if pangle_filterable:
+        pangle_aggs = ad_pangle_revenue.aggregate_by_date(db, date_from=date_from, date_to=date_to)
+        if pangle_aggs:
+            by_date = {a["date"]: a for a in pangle_aggs}
+            for d in daily:
+                pa = by_date.get(d["date"])
+                d["pangle_revenue_yuan"] = pa["revenue_yuan"] if pa else None
+                d["pangle_api_revenue_yuan"] = pa["api_revenue_yuan"] if pa else None
+            total_pangle_revenue_yuan = round(sum(a["revenue_yuan"] for a in pangle_aggs), 6)
+            api_vals = [a["api_revenue_yuan"] for a in pangle_aggs if a["api_revenue_yuan"] is not None]
+            total_pangle_api_revenue_yuan = round(sum(api_vals), 6) if api_vals else None
+
+    # 按小时汇总(全量,不受分页 limit/offset 影响):供前端按小时趋势图(单日 granularity=hour 时用)。
+    # 只在 by_hour 下聚合(此时每个 event 带 hour);否则空。前端按天趋势仍用 daily。
+    hourly: list[dict] = []
+    if by_hour:
+        hour_map: dict[int, dict] = {}
+        for e in events:
+            h = e["hour"]
+            if h is None:
+                continue
+            hd = hour_map.get(h)
+            if hd is None:
+                hd = {"hour": h, "impressions": 0, "revenue_yuan": 0.0,
+                      "expected_coin": 0, "actual_coin": 0}
+                hour_map[h] = hd
+            hd["impressions"] += e["impressions"]
+            hd["revenue_yuan"] += e["revenue_yuan"]
+            hd["expected_coin"] += e["expected_coin"]
+            hd["actual_coin"] += e["actual_coin"]
+        hourly = [
+            {**hd, "revenue_yuan": round(hd["revenue_yuan"], 6)}
+            for hd in sorted(hour_map.values(), key=lambda x: x["hour"])
+        ]
+
+    # 分广告类型小计(按 ad_type:展示条数 + 预估收益;eCPM 由前端用 收益÷展示×1000 算)。
+    # 基于全量(已按 feed_scene 过滤)events;前端只取 draw / reward_video 两类展示。
+    type_map: dict[str, dict] = {}
+    for e in events:
+        t = type_map.get(e["ad_type"])
+        if t is None:
+            t = {"impressions": 0, "revenue_yuan": 0.0}
+            type_map[e["ad_type"]] = t
+        t["impressions"] += e["impressions"]
+        t["revenue_yuan"] += e["revenue_yuan"]
+    type_stats = {
+        k: {"impressions": v["impressions"], "revenue_yuan": round(v["revenue_yuan"], 6)}
+        for k, v in type_map.items()
+    }
+
+    # DAU:复用大盘「今日活跃」口径(stats.today_dau,last_login_at)。该口径只能算今日,
+    # 故仅当查询=今日单天时给值;历史 / 多天区间返回 None,前端显示「-」。
+    is_today = date_from == date_to == rewards.cn_today().isoformat()
+    dau = admin_stats.today_dau(db) if is_today else None
+
+    # 主表「逐行」= 单次广告行为(2026-07 按「一次比价/领券放一块」聚合):激励视频 = 一次观看一行(展示+发奖
+    # 按 ad_session_id 合并);一次比价 / 一次领券 = 该次整场多条广告按 ad_session_id 聚成一行(展开看逐条)。
+    # 信息流(draw/feed)的逐条展示(ad_ecpm,impressionId 各自独立、与整场发奖无公共键)不再单独占行
+    # ——其展示数 / eCPM / 预估收益已计入上面的全量统计(total_*、daily / hourly、type_stats、穿山甲对照),
+    # 只是主表不逐条铺开;逐条明细在父行展开里看(sub_rewards)。合计 / 趋势 / 分类大盘均基于全量 events,
+    # 不受此过滤影响;total / 分页只作用于主表行。
+    main_rows = [
+        e for e in events
+        if not (e["ad_type"] in ("draw", "feed") and e["has_impression"] and not e["has_reward"])
+    ]
+
+    return {
+        "total": len(main_rows),
+        "truncated": len(main_rows) > offset + limit,
+        "total_impressions": total_impressions,
+        "total_revenue_yuan": total_revenue_yuan,
+        # 穿山甲后台收益合计(元):预估 revenue + 收益Api;非全量视图(带 user/类型/场景过滤)或无数据为 None。
+        "total_pangle_revenue_yuan": total_pangle_revenue_yuan,
+        "total_pangle_api_revenue_yuan": total_pangle_api_revenue_yuan,
+        "pangle_revenue_available": total_pangle_revenue_yuan is not None,
+        "total_expected_coin": total_expected_coin,
+        "total_actual_coin": total_actual_coin,
+        "mismatch_count": mismatch_count,
+        "daily": daily,
+        "hourly": hourly,
+        "type_stats": type_stats,
+        "dau": dau,
+        "items": main_rows[offset:offset + limit],
+    }

app/admin/repositories/admin_role.py

@@ -0,0 +1,81 @@
+"""admin_role 表 CRUD + 「角色 → 有效可见页」解析。"""
+from __future__ import annotations
+
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from app.admin.permissions import (
+    ALL_PAGE_KEYS,
+    BUILTIN_ROLES,
+    SUPER_ADMIN_ROLE,
+    sanitize_pages,
+)
+from app.models.admin_role import AdminRole
+
+
+def ensure_builtin_roles(db: Session) -> None:
+    """空表时播种内建角色(管理员/运营/财务/技术)。幂等:表非空即 no-op。
+    给 create_all 环境(测试 / 未跑迁移的全新库)兜底,迁移已播种则不重复。
+    仅 super_admin 为 is_builtin(锁定不可改删);运营/财务/技术可编辑页/删除。"""
+    if db.execute(select(AdminRole.id).limit(1)).first() is not None:
+        return
+    db.add_all([
+        AdminRole(
+            name=r["name"], label=r["label"], pages=list(r["pages"]),
+            is_builtin=(r["name"] == SUPER_ADMIN_ROLE),
+        )
+        for r in BUILTIN_ROLES
+    ])
+    db.commit()
+
+
+def list_roles(db: Session) -> list[AdminRole]:
+    ensure_builtin_roles(db)
+    # super_admin(内建)恒排最前,其余按创建序
+    return list(
+        db.execute(
+            select(AdminRole).order_by(AdminRole.is_builtin.desc(), AdminRole.id)
+        ).scalars().all()
+    )
+
+
+def get_role(db: Session, name: str) -> AdminRole | None:
+    return db.execute(
+        select(AdminRole).where(AdminRole.name == name)
+    ).scalar_one_or_none()
+
+
+def create_role(db: Session, *, name: str, label: str, pages: list[str]) -> AdminRole:
+    # 自定义角色:name(key)= label = 用户输入的名称
+    role = AdminRole(name=name, label=label, pages=sanitize_pages(pages), is_builtin=False)
+    db.add(role)
+    db.commit()
+    db.refresh(role)
+    return role
+
+
+def update_role(
+    db: Session, role: AdminRole, *, label: str | None = None, pages: list[str] | None = None
+) -> AdminRole:
+    # 只改展示名 label + 可见页;name(key)不可变(admin_user.role / require_role 承重)
+    if label is not None:
+        role.label = label
+    if pages is not None:
+        role.pages = sanitize_pages(pages)
+    db.commit()
+    db.refresh(role)
+    return role
+
+
+def delete_role(db: Session, role: AdminRole) -> None:
+    db.delete(role)
+    db.commit()
+
+
+def effective_pages_of(db: Session, role_name: str) -> list[str]:
+    """某角色名的有效可见页:super_admin → 全部;其余 → 查表 pages 与目录取交。"""
+    if role_name == SUPER_ADMIN_ROLE:
+        return list(ALL_PAGE_KEYS)
+    ensure_builtin_roles(db)
+    role = get_role(db, role_name)
+    return sanitize_pages(role.pages if role else None)

app/admin/repositories/admin_user.py

@@ -0,0 +1,72 @@
+"""admin_user 表 CRUD。"""
+from __future__ import annotations
+
+from datetime import datetime, timezone
+
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from app.core.security import hash_password
+from app.models.admin import AdminUser
+
+
+def get_by_id(db: Session, admin_id: int) -> AdminUser | None:
+    return db.get(AdminUser, admin_id)
+
+
+def get_by_username(db: Session, username: str) -> AdminUser | None:
+    stmt = select(AdminUser).where(AdminUser.username == username)
+    return db.execute(stmt).scalar_one_or_none()
+
+
+def create_admin(
+    db: Session,
+    *,
+    username: str,
+    password: str,
+    role: str = "operator",
+    plain_password: str | None = None,
+) -> AdminUser:
+    """建管理员。plain_password 非空则额外留存明文(后台 UI 建的账号传,供权限管理页复看);
+    脚本/起后台建账号不传(留 None → 前端不显示密码)。"""
+    admin = AdminUser(
+        username=username,
+        password_hash=hash_password(password),
+        role=role,
+        plain_password=plain_password,
+    )
+    db.add(admin)
+    db.commit()
+    db.refresh(admin)
+    return admin
+
+
+def update_last_login(db: Session, admin: AdminUser) -> None:
+    admin.last_login_at = datetime.now(timezone.utc)
+    db.commit()
+
+
+def list_admins(db: Session) -> list[AdminUser]:
+    stmt = select(AdminUser).order_by(AdminUser.id)
+    return list(db.execute(stmt).scalars().all())
+
+
+def set_role(db: Session, admin: AdminUser, *, role: str) -> AdminUser:
+    admin.role = role
+    db.commit()
+    db.refresh(admin)
+    return admin
+
+
+def set_status(db: Session, admin: AdminUser, *, status: str) -> AdminUser:
+    admin.status = status
+    db.commit()
+    db.refresh(admin)
+    return admin
+
+
+def set_password(db: Session, admin: AdminUser, *, password: str) -> AdminUser:
+    admin.password_hash = hash_password(password)
+    db.commit()
+    db.refresh(admin)
+    return admin