audit-unused-infrastructure

Why this exists

The category problem was not unique: a table existed (event_types), was empty, and the app stored a free-text label instead. Rather than assume it was the only case, every table was counted. It was not.

Measured state (2026-07-25)

Thing	Reality	What it costs
event_types	0 rows, 0 events reference it	Dead table, superseded by event_categories
venues	10 rows, but only 27 of 82 events set venue_id	The other 55 keep the venue inside location jsonb as text — the same imprecision categories had. No dedupe, no venue page, no “other events here”
tag_affinity	0 rows despite 135 tags and a daily cron	The recommendation graph the wizard reads is empty, so tag ordering silently falls back to usage_count
kpi_events	0 rows	No analytics at all. Nothing measures whether anything works
recommendations_cache	0 rows	compute-recommendations has never produced output
badges / user_badges	20 defined / 0 awarded	Gamification is decorative
device_tokens	0 rows	Push notifications cannot be delivered to anyone
notifications	0 rows	Nothing has ever notified a user
events.image_url	36 of 82 null	Feed cards render without a picture
Table statistics	reltuples = -1 on every table	ANALYZE has never run, so the query planner picks plans blind

Pattern behind all of it

Two failure modes repeat:

1. Structure built, never wired. A table + RLS + an Edge Function exist, but nothing calls them (kpi_events, recommendations_cache, tag_affinity).
2. A FK exists, but the value is written as free text next to it. events.category beside category_id; location->>'name' beside venue_id. The text always wins because it is easier to write, and the relation rots.

Plan, ordered by cost of leaving it broken

Now — cheap and high impact

• ANALYZE the database, and confirm autovacuum is on. Planner quality affects every query.
• Populate venue_id from the existing location data using the same normalised-key trick that worked for categories (venue_key), then treat venue text as derived — exactly as events.category now is.

Next — turns the lights on

• Emit kpi_events from the app for a handful of events (open, event_view, rsvp). Without this there is no way to know if any feature is used. This is the single most valuable missing piece; see roadmap.
• Register device_tokens at login so push has somewhere to go.

Then — only once there are users

• Schedule compute-tag-affinity and compute-recommendations for real. Both need behavioural data to produce anything meaningful, so doing this before users exist would only produce noise.
• Award badges from process-rsvp, or drop the 20 definitions.

Cleanup

• Drop event_types once nothing references it.

Guards added at the same time

Constraints now reject, on write, what previously slipped in silently: blank titles, end_date before start_date, negative prices, non-URL values in image and link fields, out-of-range confidence, malformed category slugs, and categories with no name in any language. All NOT VALID, so they apply going forward without retro-failing existing rows.

Related

database-schema · feat-event-ingest-api · roadmap