"""One-time backfill: set owner_user_id on existing support-inbox records. support-inbox v1.1.0 enabled rls_enabled on support_ticket/support_message, but that does not retroactively set owner_user_id on records created under v1.0.0 (before RLS existed). Unlike client-portal/saas-membership, support_ticket never carried an app_user_id (or any other) field identifying its owner -- only requester_email, a display/contact field, not a verified identity link. This script does a best-effort match of requester_email against this project's Cloud auth users (case-insensitive) and BACKFILLS ONLY when exactly one user matches. It explicitly skips (never guesses) a ticket whose email matches zero or more than one user -- a wrong owner assignment on support data is worse than leaving it unowned for staff to reassign by hand. Default is dry-run. Usage (P2HS-BE root, same .env as the API): python -m scripts.backfill_support_inbox_ownership --project-id python -m scripts.backfill_support_inbox_ownership --project-id --apply """ from __future__ import annotations import argparse import asyncio import json from collections import defaultdict from uuid import UUID from src.core.orm_import import import_all_orm_models import_all_orm_models() def _record_data(record: dict) -> dict: raw = record.get("data_json") if not raw: return {} try: parsed = json.loads(raw) return parsed if isinstance(parsed, dict) else {} except (TypeError, ValueError): return {} async def _backfill(project_id: UUID, apply: bool) -> None: from src.core.database import async_session_factory from src.domain.projects.repository import ProjectRepository from src.integrations.cloud_control_client import get_cloud_client_for_workspace async with async_session_factory() as db: project = await ProjectRepository(db).get_by_id(project_id) if not project: print(f"[error] project {project_id} not found") return client = await get_cloud_client_for_workspace(project.workspace_id, db) pid = str(project_id) # Build a case-insensitive email -> [user_id, ...] index once; a matched email # with more than one user is ambiguous and must be skipped, never guessed. users = await client.list_app_users(pid) users_by_email: dict[str, list[str]] = defaultdict(list) for user in users: email = user.get("email") user_id = user.get("id") if isinstance(email, str) and email.strip() and user_id: users_by_email[email.strip().lower()].append(user_id) def resolve_owner(email: str | None, *, context: str) -> str | None: if not email: print(f"[warn] {context} has no requester_email -- cannot backfill, skipping") return None matches = users_by_email.get(email.strip().lower()) if not matches: print(f"[warn] {context}: requester_email {email!r} matches no project user, skipping") return None if len(matches) > 1: print(f"[warn] {context}: requester_email {email!r} matches {len(matches)} users, ambiguous, skipping") return None return matches[0] # Pass 1: support_ticket -- owner resolved from requester_email. ticket_owner_by_id: dict[str, str] = {} tickets_checked = tickets_fixed = tickets_skipped = 0 cursor = None while True: page = await client.query_project_records(pid, "support_ticket", limit=100, cursor=cursor) for record in page.get("records", []): tickets_checked += 1 record_id = record["id"] existing_owner = record.get("owner_user_id") if existing_owner: ticket_owner_by_id[record_id] = existing_owner tickets_skipped += 1 continue email = _record_data(record).get("requester_email") owner = resolve_owner(email, context=f"support_ticket {record_id}") if not owner: tickets_skipped += 1 continue ticket_owner_by_id[record_id] = owner tickets_fixed += 1 if apply: await client.set_record_owner(pid, "support_ticket", record_id, owner) cursor = page.get("next_cursor") if not cursor: break # Pass 2: support_message -- owner mirrors its parent support_ticket's owner # (resolved in pass 1, including tickets that were already correctly owned). messages_checked = messages_fixed = messages_skipped = 0 cursor = None while True: page = await client.query_project_records(pid, "support_message", limit=100, cursor=cursor) for record in page.get("records", []): messages_checked += 1 record_id = record["id"] if record.get("owner_user_id"): messages_skipped += 1 continue parent_id = _record_data(record).get("ticket_record_id") owner = ticket_owner_by_id.get(parent_id) if parent_id else None if not owner: print(f"[warn] support_message {record_id} -> parent {parent_id} has no resolvable owner, skipping") messages_skipped += 1 continue messages_fixed += 1 if apply: await client.set_record_owner(pid, "support_message", record_id, owner) cursor = page.get("next_cursor") if not cursor: break mode = "APPLIED" if apply else "DRY-RUN" print(f"[{mode}] support_ticket: checked={tickets_checked} fixed={tickets_fixed} skipped={tickets_skipped}") print(f"[{mode}] support_message: checked={messages_checked} fixed={messages_fixed} skipped={messages_skipped}") if not apply and (tickets_fixed or messages_fixed): print("Re-run with --apply to write these changes.") if tickets_skipped: print( f"{tickets_skipped} ticket(s) could not be matched to a project user by email and remain unowned " "-- reassign them by hand (owner_user_id) if they need to be visible to a specific customer." ) def main() -> None: parser = argparse.ArgumentParser( description="Backfill owner_user_id on support-inbox records after enabling RLS" ) parser.add_argument("--project-id", required=True, help="Project UUID") parser.add_argument("--apply", action="store_true", help="Write changes (default: dry-run)") args = parser.parse_args() asyncio.run(_backfill(UUID(args.project_id), args.apply)) if __name__ == "__main__": main()