Deprecate dead pre-event-sourcing tables (rename first, drop later) + delete dead code referencing them

[github-actions]

Context

The VahterBanBot database migrated from per-feature tables to a single append-only
event-sourced event table (Flyway V23__events.sql, cut over 2026-04-02). That
migration left a cluster of pre-event-sourcing tables behind: nothing writes to them
any more, and application code no longer reads from them either — except for one dead
method, GetVahterStats (src/VahterBanBot/DB.fs:936), which still queries two of
them and has zero callers itself.

This issue started as "delete GetVahterStats" and is being broadened to cover the
whole orphaned-table cluster it's part of, plus the coupon bot's DB (which has its own,
smaller, event-sourcing migration — coupon_event).

Evidence

Methodology: for every table, (a) grepped src/VahterBanBot / src/CouponHubBot and
scripts/queries/ for the table name, distinguishing "referenced only by dead code /
migrations / one-off ops scripts" from "referenced by a live code path", and (b)
checked pg_stat_user_tables write counters (never reset on either DB — pg_stat_database.stats_reset
is NULL) plus max() of each table's own timestamp column. A table is only called
DEAD below when code-side and data-side evidence agree.

VahterBanBot DB — DEAD (candidates for this issue)

Table	Last write (max timestamp)	Live code references	Verdict
banned	2026-04-02 20:18 UTC	None outside GetVahterStats (DB.fs:947)	DEAD
banned_by_bot	2026-04-02 22:40 UTC	None outside GetVahterStats (DB.fs:955)	DEAD
vahter_actions	2026-04-02 19:19 UTC	None in app code; superseded by event (VahterActed)	DEAD
message	2026-04-02 22:42 UTC	None in app code; superseded by event (MessageReceived)	DEAD
"user"	2026-04-02 22:36 UTC	None outside GetVahterStats (DB.fs:948, JOIN "user")	DEAD
callback	2026-04-02 22:40 UTC	None; all callback flows (RecordCallback/ResolveCallback/ExpireOrphanedCallbacks etc. in DB.fs) now read/write event (CallbackCreated/CallbackMessagePosted/CallbackResolved/CallbackExpired)	DEAD
llm_triage	2026-04-02 19:14 UTC	None; LlmTriage.fs writes LlmClassified events to event, not this table	DEAD
false_positive_messages	no timestamp column; 0 pg_stat writes ever; static row count (2381)	None in app code; only read by the one-time V23__events.sql backfill and replicated in tests/VahterBanBot.Tests/test_seed.sql (seed helper, not app code)	DEAD
false_negative_messages	no timestamp column; 0 pg_stat writes ever; static row count (25)	Same as above	DEAD
false_positive_users	no timestamp column; 0 pg_stat writes ever; static row count (14)	Same as above	DEAD

Cross-checked FK dependents of the tables above via information_schema — every FK
pointing at message/"user"/banned originates from another table in this same DEAD
set (false_negative_messages, banned, banned_by_bot, false_positive_users,
vahter_actions); no live table has a foreign key into any of these 10 tables, so
renaming them is safe.

Note: tests/VahterBanBot.Tests/Program.fs:12 has a stale doc comment claiming
DB.getUserStatsByLastNMessages "joins against" false_positive_messages /
false_negative_messages — the current implementation of that method (DB.fs:453-511)
reads only from event. Not fixing that comment here (out of scope for this issue),
flagging so it doesn't cause confusion later.

scripts/queries/vahter/01-daily-messages.sql, 02-daily-bans-actions.sql and
03-llm-ml-verdicts.sql already carry comments documenting the 2026-04-02 freeze for
message/user, banned/banned_by_bot/vahter_actions, and llm_triage
respectively — this issue is the first place tying that into an actual cleanup plan.

VahterBanBot DB — LIVE (not in scope)

bot_setting, event, llm_verdict_cache, ocr_cache, ml_trained_model,
scheduled_job, snapshot_message, snapshot_user, user_msg_text_index,
user_profile_cache, flyway_schema_history — all have recent pg_stat_user_tables
write activity and/or live code references outside GetVahterStats.

CouponHubBot DB — reviewed, no dead tables found

CouponHubBot has its own, separate event-sourcing table (coupon_event). Every other
table in that database (bot_setting, chat_message, coupon, pending_add,
pending_add_batch, pending_add_batch_item, pending_feedback, user,
user_feedback, flyway_schema_history) is referenced live in
src/CouponHubBot/Services/DbService.fs. The pending_*/pending_feedback tables
show n_live_tup = 0 in pg_stat_user_tables but that's expected — they're
transient in-flight queue tables (rows inserted then deleted once a pending
add/batch/feedback resolves), not orphans. No action proposed for the coupon DB in
this issue.

Staged plan (binding, per owner decision)

Data-changing steps below are not applied by an agent — DB access here is
read-only, and per repo convention (schema/data changes aren't done via Flyway seeds
by agents) the owner runs these by hand.

1. Rename now — the 10 DEAD VahterBanBot tables get a deprecated_ prefix and
   stay in place (data untouched, nothing drops). Idempotent SQL below.
2. Delete the dead code that references them — GetVahterStats
   (src/VahterBanBot/DB.fs:936-963) and its return type VahterStats
   (src/VahterBanBot/Types.fs:469), which have no other callers/uses. This is
   ordinary code deletion, tracked as a follow-up PR against this issue.
3. Observe — leave the renamed tables in place for a while (no fixed deadline set
   here) in case something unexpected still depends on the old names.
4. Drop later — only after step 3 confirms nothing broke, the owner drops the
   deprecated_* tables by hand.

Idempotent rename SQL (VahterBanBot DB, step 1 — for the owner to run by hand)

ALTER TABLE ... RENAME TO deprecated_... (10 tables)

-- Idempotent: ALTER TABLE IF EXISTS is a no-op if already renamed / already absent.
ALTER TABLE IF EXISTS banned                    RENAME TO deprecated_banned;
ALTER TABLE IF EXISTS banned_by_bot              RENAME TO deprecated_banned_by_bot;
ALTER TABLE IF EXISTS vahter_actions             RENAME TO deprecated_vahter_actions;
ALTER TABLE IF EXISTS message                    RENAME TO deprecated_message;
ALTER TABLE IF EXISTS "user"                     RENAME TO deprecated_user;
ALTER TABLE IF EXISTS callback                   RENAME TO deprecated_callback;
ALTER TABLE IF EXISTS llm_triage                 RENAME TO deprecated_llm_triage;
ALTER TABLE IF EXISTS false_positive_messages    RENAME TO deprecated_false_positive_messages;
ALTER TABLE IF EXISTS false_negative_messages    RENAME TO deprecated_false_negative_messages;
ALTER TABLE IF EXISTS false_positive_users       RENAME TO deprecated_false_positive_users;

Suggested approach for step 2 (code deletion)

• Remove GetVahterStats (src/VahterBanBot/DB.fs:936-963) and the VahterStats /
  VahterStat types it uses (src/VahterBanBot/Types.fs:469 and neighbors) if they
  have no other use.
• Grep for GetVahterStats( / VahterStats across the solution first to confirm
  zero remaining callers (matches the original triage for this issue).
• No other application code references the 10 DEAD tables, so step 2 is scoped to
  this one method + type; it does not itself require any table rename to land first,
  but should ship as its own PR separate from the rename (rename is a DB-owner action,
  code deletion is a normal PR).

[github-actions]

Project assessment bump (2026-08-01) Still relevant.

Evidence

• src/VahterBanBot/DB.fs:936 defines member _.GetVahterStats(banInterval: TimeSpan option) : Task<VahterStats> = but a repository-wide search shows no callers to GetVahterStats(; only its definition is present. See file snippet at that location.
• src/VahterBanBot/Cleanup.fs uses db.GetVahterActionStats(...) (the active stats path), confirming the live code uses GetVahterActionStats instead of GetVahterStats.

Suggested Approach

• Remove the dead GetVahterStats member from src/VahterBanBot/DB.fs (or mark it obsolete with a comment and add a unit test if it must be preserved for historic reasons).
• Run dotnet test -c Release to confirm no caller remains and CI stays green.

(Reported by automated project-agent scan.)

[github-actions]

Project assessment bump (2026-08-02)

Still relevant.

Evidence:

• GetVahterStats is defined at src/VahterBanBot/DB.fs:936 but has no callers (code search returned no references).
• The live cleanup path uses GetVahterActionStats (see src/VahterBanBot/DB.fs:966 and src/VahterBanBot/Cleanup.fs:77), which suggests GetVahterStats is dead/unused.

Suggested approach:

• Remove GetVahterStats or mark it [<Obsolete>] with an explanation and a TODO linking to this issue, and prefer GetVahterActionStats in callers.
• Add a small unit/integration test if there was intended runtime use to prevent accidental future drift.

This is low-risk, repository-local cleanup; priority-low is appropriate.

[github-actions]

Project assessment bump (2026-08-03) Still relevant. Evidence: references in source (see src/VahterBanBot/DB.fs members on lines around 936/966) and open project issue list.

[github-actions]

Project assessment bump (2026-08-04) Still relevant.

Evidence:

• src/VahterBanBot/DB.fs:936 defines member _.GetVahterStats(...).
• src/VahterBanBot/DB.fs:966 defines member _.GetVahterActionStats(...).
• src/VahterBanBot/Cleanup.fs:77 calls db.GetVahterActionStats(...), indicating GetVahterActionStats is used while GetVahterStats appears unused.

Context:

• This matches the open project issue about a dead method; the code scan today still shows GetVahterStats in the codebase.

Suggested approach:

• Remove or deprecate GetVahterStats, or add a comment linking it to the newer GetVahterActionStats and mark it as intentionally retained.

[github-actions]

Project assessment bump (2026-08-05)

Still relevant.

Quick checks performed:

• Confirmed declaration at src/VahterBanBot/DB.fs:936 (member _.GetVahterStats(banInterval: TimeSpan option) : Task<VahterStats> =).
• Repository search shows no callers for GetVahterStats(; the live cleanup flow calls GetVahterActionStats (src/VahterBanBot/Cleanup.fs:77).

Suggested next step: either remove the unused member or mark it explicitly (comment/[<Obsolete>]) with rationale. See this assessment for reproduction evidence.