修复：广告收益用户详情加载失败

用户奖励统计改为仅查询用户主键，避免完整用户模型依赖未同步字段；统一金币记录跨数据源的 UTC 排序键，修复 PostgreSQL 下 naive 与 aware 时间混排异常；补充旧表结构与混合时区回归测试。

app/admin/repositories/queries.py

@@ -9,7 +9,7 @@ from datetime import date, datetime, time, timedelta, timezone
 from decimal import ROUND_HALF_UP, Decimal
 from zoneinfo import ZoneInfo
 
-from sqlalchemy import Select, and_, asc, case, desc, func, or_, select
+from sqlalchemy import Select, asc, case, desc, func, or_, select
 from sqlalchemy.orm import Session
 
 from app.core import rewards
@@ -45,72 +45,6 @@ _FEED_SCENE_LABEL = {
 }
 
 
-_DEVICE_MARKETING_NAMES = {
-    "23078RKD5C": "Redmi K60 至尊版",
-    "M2012K11AC": "Redmi K40",
-    "PJA110": "一加 Ace 2 Pro",
-    "PPG-AN00": "荣耀 GT Pro",
-    "V2166BA": "vivo Y77e",
-    "V2309A": "vivo X100",
-}
-
-
-def _device_marketing_name(model: str | None) -> str | None:
-    """把线上已知 Build.MODEL 编码转成用户可识别的商品名。"""
-    if not model:
-        return None
-    return _DEVICE_MARKETING_NAMES.get(model.strip().upper())
-
-
-def _attach_feedback_device_details(db: Session, feedbacks: list[Feedback]) -> None:
-    """按同一用户、同一设备编码及提交时间补齐厂商和 ROM 大版本。"""
-    candidates = [
-        item for item in feedbacks if item.device_model and item.device_model.strip()
-    ]
-    for item in candidates:
-        item.device_model_name = _device_marketing_name(item.device_model)
-        item.device_manufacturer = None
-        item.rom_version = None
-    if not candidates:
-        return
-
-    ranked = (
-        select(
-            Feedback.id.label("feedback_id"),
-            ComparisonRecord.device_manufacturer.label("device_manufacturer"),
-            ComparisonRecord.rom_version.label("rom_version"),
-            func.row_number()
-            .over(
-                partition_by=Feedback.id,
-                order_by=(
-                    ComparisonRecord.created_at.desc(),
-                    ComparisonRecord.id.desc(),
-                ),
-            )
-            .label("row_num"),
-        )
-        .join(
-            ComparisonRecord,
-            and_(
-                ComparisonRecord.user_id == Feedback.user_id,
-                ComparisonRecord.device_model == Feedback.device_model,
-                ComparisonRecord.created_at <= Feedback.created_at,
-            ),
-        )
-        .where(Feedback.id.in_([item.id for item in candidates]))
-        .subquery()
-    )
-    details = {
-        row.feedback_id: row
-        for row in db.execute(select(ranked).where(ranked.c.row_num == 1)).all()
-    }
-
-    for item in candidates:
-        detail = details.get(item.id)
-        item.device_manufacturer = detail.device_manufacturer if detail else None
-        item.rom_version = detail.rom_version if detail else None
-
-
 def cursor_paginate(
     db: Session, stmt: Select, id_col, *, limit: int, cursor: int | None
 ) -> tuple[list, int | None]:
@@ -883,7 +817,6 @@ def list_feedbacks(
         db, stmt, (order_fn(sort_col), id_order), limit=limit, cursor=cursor
     )
     _attach_user_info(db, items)  # 列表展示完整手机号(点手机号查该用户全部反馈)
-    _attach_feedback_device_details(db, items)
     return items, next_cursor, total
 
 
@@ -1295,6 +1228,11 @@ def _cn_wall_to_utc(dt: datetime) -> datetime:
     return dt.replace(tzinfo=rewards.CN_TZ).astimezone(timezone.utc).replace(tzinfo=None)
 
 
+def _coin_record_sort_key(row: dict) -> datetime:
+    """金币明细跨数据源排序键：兼容 SQLite naive 与 PostgreSQL aware 时间。"""
+    return _as_utc(row["created_at"])
+
+
 def user_coin_records(
     db: Session,
     user_id: int,
@@ -1386,7 +1324,10 @@ def user_coin_records(
             "coin": rec.amount,
         })
 
-    rows.sort(key=lambda r: r["created_at"], reverse=True)
+    # SQLite 常返回 naive datetime，PostgreSQL timestamptz 返回 aware datetime；
+    # 统一成 aware UTC 排序，避免线上合并广告记录与签到记录时抛
+    # “can't compare offset-naive and offset-aware datetimes”。
+    rows.sort(key=_coin_record_sort_key, reverse=True)
     has_more = len(rows) > offset + limit
 
     # 总数 = 三源在窗口内 granted 计数之和(供前端页码分页渲染页码/共 N 条)

app/admin/routers/users.py

@@ -86,7 +86,7 @@ def get_user_reward_stats(
     date_to: Annotated[datetime | None, Query()] = None,
 ) -> UserRewardStats:
     """提现详情抽屉「用户统计区」。date_from/date_to 都不传 = 注册至今(全量)。"""
-    if user_repo.get_user_by_id(db, user_id) is None:
+    if not user_repo.user_exists(db, user_id):
         raise HTTPException(status_code=404, detail="用户不存在")
     return UserRewardStats(
         **queries.user_reward_stats(db, user_id, date_from=date_from, date_to=date_to)

app/admin/schemas/feedback.py

@@ -32,10 +32,7 @@ class FeedbackOut(BaseModel):
     # 提交端环境快照(feedback 表列):提交版本号 / 机型OS版本;改版前的历史反馈为 None
     app_version: str | None = None
     device_model: str | None = None
-    device_model_name: str | None = None
-    device_manufacturer: str | None = None
     rom_name: str | None = None
-    rom_version: int | None = None
     android_version: str | None = None
     # 联表瞬态字段(queries._attach_user_info 挂):列表展示完整手机号,点手机号查该用户全部反馈
     phone: str | None = None

app/repositories/user.py

@@ -93,6 +93,11 @@ def get_user_by_id(db: Session, user_id: int) -> User | None:
     return db.get(User, user_id)
 
 
+def user_exists(db: Session, user_id: int) -> bool:
+    """只查主键判断用户是否存在，避免只读统计接口依赖完整用户表结构。"""
+    return db.scalar(select(User.id).where(User.id == user_id)) is not None
+
+
 def get_user_by_phone(db: Session, phone: str) -> User | None:
     stmt = select(User).where(User.phone == phone)
     return db.execute(stmt).scalar_one_or_none()

scripts/seed_mock_feedback.py

@@ -175,22 +175,22 @@ def seed(db) -> list[Feedback]:
         # 普通反馈 · 新端(带环境快照)· 无图
         fb("13255550001", "签到金币到账有时候会延迟一两分钟,能不能做成实时到账?",
            source="profile",
-           app_version="2.3.1", device_model="PJA110", rom_name="ColorOS 14", android_version="14",
+           app_version="2.3.1", device_model="PJA110", rom_name="ColorOS", android_version="14",
            created=ago(minutes=6)),
         # 比价反馈 · scene=优惠不对 · 新端 · 2 图
         fb("13255550002", "这家店京东外卖的到手价比你们算出来的最低价还低,截图为证,麻烦核实。",
            source="comparison", scene="优惠不对", images=[imgs[0], imgs[1]],
-           app_version="2.3.1", device_model="M2012K11AC", rom_name="MIUI 14", android_version="13",
+           app_version="2.3.1", device_model="M2012K11AC", rom_name="MIUI", android_version="13",
            created=ago(minutes=22)),
         # 比价反馈 · scene=找错商品 · 新端 · 无图
         fb("13255550002", "比价结果里的商品跟我搜的不是同一个规格,数量对不上。",
            source="comparison", scene="找错商品",
-           app_version="2.3.0", device_model="V2309A", rom_name="OriginOS 4", android_version="14",
+           app_version="2.3.0", device_model="V2309A", rom_name="OriginOS", android_version="14",
            created=ago(hours=1)),
         # 普通反馈 · 新端 · 1 图(表扬 + 小问题)
         fb("13255550003", "提现秒到账,好评!顺手反馈个小 bug:金币记录页偶尔白屏,要退出去重进。",
            source="profile", images=[imgs[2]],
-           app_version="2.3.1", device_model="23078RKD5C", rom_name="MIUI 14", android_version="14",
+           app_version="2.3.1", device_model="23078RKD5C", rom_name="MIUI", android_version="14",
            created=ago(hours=3)),
         # 比价反馈 · scene=比价太慢 · 历史数据(env 全 NULL、contact 有值)
         fb("13255550004", "比价转圈太久了,经常要等十几秒才出结果,体验不太好。",
@@ -206,7 +206,7 @@ def seed(db) -> list[Feedback]:
            source="profile", images=[imgs[3]],
            status="adopted", reward_coins=2000,
            review_note="有效产品建议,已排期到 2.4.0", admin_reply="感谢反馈!该功能已在规划中,金币奖励已发放~",
-           app_version="2.2.8", device_model="PJA110", rom_name="ColorOS 13", android_version="13",
+           app_version="2.2.8", device_model="PJA110", rom_name="ColorOS", android_version="13",
            created=ago(days=2)),
 
         # ===== 未采纳 rejected(带原因 + 回复)=====
@@ -214,7 +214,7 @@ def seed(db) -> list[Feedback]:
            source="comparison", scene="价格不准",
            status="rejected", reject_reason="截图价格为限时活动价且已过期,不满足「长期可复现更低价」条件,暂不采纳。",
            admin_reply="感谢参与,本次未通过,欢迎继续上报有效更低价~",
-           app_version="2.3.0", device_model="M2012K11AC", rom_name="MIUI 14", android_version="13",
+           app_version="2.3.0", device_model="M2012K11AC", rom_name="MIUI", android_version="13",
            created=ago(days=3)),
     ]
     db.add_all(feedbacks)

tests/test_admin_feedback_device_details.py

@@ -1,71 +0,0 @@
-from datetime import datetime, timedelta, timezone
-
-from app.admin.repositories import queries
-from app.db.session import SessionLocal
-from app.models.comparison import ComparisonRecord
-from app.models.feedback import Feedback
-from app.repositories import user as user_repo
-
-
-def test_feedback_device_details_use_same_user_and_model() -> None:
-    with SessionLocal() as db:
-        submitted_at = datetime.now(timezone.utc)
-        user = user_repo.upsert_user_for_login(
-            db,
-            phone="13800009876",
-            register_channel="sms",
-        )
-        db.add_all(
-            [
-                ComparisonRecord(
-                    user_id=user.id,
-                    trace_id="feedback-device-v2166ba",
-                    device_model="V2166BA",
-                    device_manufacturer="vivo",
-                    rom_name="OriginOS",
-                    rom_version=13,
-                    created_at=submitted_at - timedelta(minutes=5),
-                ),
-                ComparisonRecord(
-                    user_id=user.id,
-                    trace_id="feedback-device-other",
-                    device_model="OTHER-CODE",
-                    device_manufacturer="Other",
-                    rom_name="OtherOS",
-                    rom_version=99,
-                    created_at=submitted_at - timedelta(minutes=5),
-                ),
-                ComparisonRecord(
-                    user_id=user.id,
-                    trace_id="feedback-device-future-upgrade",
-                    device_model="V2166BA",
-                    device_manufacturer="vivo-new",
-                    rom_name="OriginOS",
-                    rom_version=99,
-                    created_at=submitted_at + timedelta(minutes=5),
-                ),
-                Feedback(
-                    user_id=user.id,
-                    content="设备信息补全测试",
-                    contact="",
-                    status="pending",
-                    device_model="V2166BA",
-                    rom_name="OriginOS",
-                    android_version="13",
-                    created_at=submitted_at,
-                ),
-            ]
-        )
-        db.commit()
-
-        items, _next_cursor, total = queries.list_feedbacks(
-            db,
-            user_id=user.id,
-            limit=20,
-        )
-
-        assert total == 1
-        assert items[0].device_model == "V2166BA"
-        assert items[0].device_model_name == "vivo Y77e"
-        assert items[0].device_manufacturer == "vivo"
-        assert items[0].rom_version == 13

tests/test_admin_read.py

@@ -1,7 +1,7 @@
 """Admin M2 读接口测试:大盘聚合 + 用户/流水/提现/反馈列表 + 鉴权拦截。"""
 from __future__ import annotations
 
-from datetime import datetime
+from datetime import UTC, datetime
 
 import pytest
 from fastapi.testclient import TestClient
@@ -9,6 +9,7 @@ from sqlalchemy import event
 
 from app.admin.main import admin_app
 from app.admin.repositories import admin_user as admin_repo
+from app.admin.repositories import queries
 from app.db.session import SessionLocal, engine
 from app.models.comparison import ComparisonRecord
 from app.models.feedback import Feedback
@@ -144,6 +145,8 @@ def test_user_reward_detail_does_not_select_unrelated_new_ad_columns(
     ) -> None:
         if "ad_reward_record.boost_round_id" in statement:
             raise AssertionError("提现详情不应查询未使用的 boost_round_id")
+        if "FROM user" in statement and "user.phone" in statement:
+            raise AssertionError("奖励统计的用户存在性检查不应展开完整 user 表")
 
     event.listen(engine, "before_cursor_execute", reject_full_ad_reward_projection)
     try:
@@ -162,6 +165,17 @@ def test_user_reward_detail_does_not_select_unrelated_new_ad_columns(
     assert records.status_code == 200, records.text
 
 
+def test_user_coin_record_sort_accepts_mixed_timezone_datetimes() -> None:
+    """线上 PostgreSQL 返回 aware，SQLite/历史转换可能返回 naive，二者必须可混排。"""
+    naive = datetime(2038, 1, 1, 8, 0)
+    aware = datetime(2038, 1, 1, 7, 0, tzinfo=UTC)
+
+    rows = [{"created_at": aware}, {"created_at": naive}]
+    rows.sort(key=queries._coin_record_sort_key, reverse=True)
+
+    assert rows == [{"created_at": naive}, {"created_at": aware}]
+
+
 def test_user_filter_by_status(admin_client: TestClient, admin_token: str) -> None:
     _seed_user_with_data("13800000003")
     r = admin_client.get("/admin/api/users", params={"status": "active"}, headers=_auth(admin_token))