Data layer: undeletable PI users, cascade performance, missing indexes, pool settings (5 PRs)

[ahueb]

Five verified items across the models, the delete path, and the DB engine config. D1 is HIGH and live-reproduced; the rest are trivial-to-small quick wins that belong in the same migration and the same review.

Line numbers current as of origin/main @ b7edcbc (2026-07-30).

Suggested order: D1 + P2 together (same cascade semantics), then P1 (fold its indexes into the same migration), then P3, then D2. P1's index list and P2's passive_deletes targets overlap on the same FK columns — one migration should cover both.

PR D1 — Make PI users deletable (HIGH, small — live-reproduced)

private_channel_members has CHECK (agent_id IS NULL) != (user_id IS NULL) (agent_activity.py:250-253) while user_id is ondelete="SET NULL" (:277-281); PI member rows are user_id-only (private_channels.py:278-280). Deleting such a user via admin_delete_user (admin.py:192) or self-delete POST /profile/delete-account (profile.py:247) nulls both sides → the CHECK aborts the DELETE. Live-reproduced: constraint violation on the admin path; uncaught HTTP 500 on the self-delete path. Any PI who was ever a private-channel member is undeletable. Fix: delete PCM rows on user delete (ORM cascade), or relax the constraint.

PR P2 — passive_deletes=True on cascaded children (small)

Zero passive_deletes anywhere in src/ (re-confirmed 2026-07-30); ~10 relationships use cascade="all, delete-orphan" (user.py:52-64, agent_registry.py:51/56, agent_activity.py:55/58/61/165) with no DB-side delete, so a user delete SELECTs every child row into memory first. Fix: passive_deletes=True + confirm DB ondelete on the children. Do this with D1 — it is the same delete path.

PR D2 — Reconcile stale running simulation runs on startup (LOW, trivial)

status="stopped" is written only in the finally (agent/main.py:270). (Premise corrected 2026-07-30: the original write-up blamed the documented docker rm -f stop, but CLAUDE.md/README.md were changed on 2026-07-27 to docker stop -t 30 + docker rm, and docker-compose.yml adds stop_grace_period: 30s, so the documented path is now graceful and does write the status.) What remains: a real crash / OOM-kill / docker kill still leaves the row running, and the --fresh path creates a new run without reconciling prior ones — only the resume path repairs, and only the run it resumes (agent/main.py:192). Cosmetic. Fix: reconcile stale running runs at startup.

PR P1 — Composite + FK indexes and a /static middleware guard (trivial — measured)

Confirmed missing against a live head-0020 DB and re-confirmed 2026-07-30 at head 0021: no index migration references outcome, and the one migration added since (0021_inbox_cursor_created_at_indexes) adds only ix_agent_messages_run_created + ix_pi_dm_run_direction_created — neither serves this item.

• thread_decisions has no __table_args__ at all (agent_activity.py:206-237); its only index is ix_thread_decisions_run_id (0003_add_thread_decisions…:50). The badge middleware runs a per-request, per-agent COUNT on outcome + (agent_a|agent_b) (main.py:80-92) and needs (agent_a, outcome) + (agent_b, outcome) — measured ~129× speedup. AgentBadgeMiddleware.dispatch (main.py:28) also has no /static path guard, so it fires on every asset request carrying a session cookie.
• 15 unindexed ondelete FK targets, enumerated by a live pg_constraint/pg_index join against a scratch DB at head 0021:
  access_allowlist.added_by_user_id, agent_delegates.invitation_id, agent_delegates.user_id (~209×), agents.approved_by, delegate_invitations.accepted_by_user_id, delegate_invitations.invited_by_user_id, email_notifications.agent_registry_id, email_notifications.thread_decision_id, private_channel_members.added_by_user_id, private_channel_members.user_id, profile_revisions.changed_by_user_id, proposal_reviews.delegate_user_id, proposal_reviews.reviewed_by_user_id, proposal_reviews.user_id, slack_app_provisions.agent_registry_id.

Fix: one migration adding the composites + all 15 FK indexes; a /static short-circuit in the middleware. Do not add llm_call_logs(channel,phase) — measured, no benefit.

PR P3 — Add pool_pre_ping / pool_recycle (trivial)

database.py:15-22 sets pool_size=5, max_overflow=10 with no pool_pre_ping/pool_recycle/pool_timeout → stale-connection 500s after a DB restart, and a 16th concurrent checkout blocks 30 s. Also reduces the trigger for the flush-loss path fixed by PR #19's H1. Fix: add pool_pre_ping=True + a pool_recycle.

Definition of done: D1 ships a test that deletes a PI who is a private-channel member (currently a constraint violation). P1 ships an alembic upgrade head check in CI.