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.

Outcome (2026-07-29)

Thing	Before	After	How
venues	10 rows, 27/82 events linked	36 rows, 57 events linked, 22 with coordinates	owner_id made nullable (the root cause — a venue could not exist until someone owned it), then backfilled from location jsonb via a normalised key
Venue identity	name as free text in jsonb	venue_key + two unique indexes	Same physical place can no longer exist twice: one key on (source, external_id), one on (venue_key, city)
Venue enrichment	none	enrich-venues Edge Function, 8/9 correct on the first batch	OpenStreetMap Nominatim — free, keyless, ODbL. See below
Venue ownership	owner_id with nothing governing it	venue_claims + approve_venue_claim()	Admin approval is the only path to ownership; never self-assigned
kpi_events	0 rows	130 rows, real timeline from 2026-01-10	Triggers on event_attendees, events, profiles + track_kpi() for client signals; backfilled preserving original created_at
badges	20 defined, 0 awarded	founder created and granted	badges_category_check gained special; criteria left empty so the evaluator skips it
Table statistics	reltuples = -1 everywhere	ANALYZE run, 0 tables without stats	Stats were missing because the pause/restore reset them, not because autovacuum was off (it is on)
Da definire	a venue row with 23 events	deleted, events set to venue_id = NULL	Plus a trigger so the import pipeline cannot recreate it

Deliberately not done, with reasons

• tag_affinity — scheduling compute-tag-affinity would produce nothing: 0 profiles have interests. The wizard does write them (three call sites), so the 25 existing profiles are seed data. Wire it when real users register.
• recommendations_cache — same shape of problem: it needs real users and real RSVPs before its output means anything.
• device_tokens — inspected and found correctly implemented (notification_service.dart: Firebase guard, upsert on conflict, refresh listener). Empty only because the app has never run on a real device. Nothing to fix; do not delete.
• pg_cron / pg_net are not installed. This is the single root cause behind three of the empty tables above: the Edge Functions are deployed but nothing ever invokes them. Installing them is the prerequisite for the two items deferred here.

Venue enrichment: why OpenStreetMap, and why it is guarded

Google Places requires a billing account (the free credit still wants a card) and its terms restrict storing results. Nominatim is free, keyless, and ODbL — we may keep the data as long as we attribute it.

The catch is that a geocoder always answers. Measured on real rows:

Query	Nominatim returned	Why it is wrong
Fabrique	a hamlet in Valle d’Aosta	right shape, wrong kind of thing
Club Neon	a restaurant in New York	right kind of thing, wrong country

Both came back with a valid osm_id. A wrong address is worse than a missing one: it looks correct, so nobody notices until a user drives there. Two guards fix both cases:

• countrycodes=it — a hard filter. Putting “Italia” in the query string is only a hint and does not stop a New York match from winning.
• PLACE_CLASSES — the hit must be amenity/leisure/tourism/shop/… at a street address, not a settlement or an administrative boundary.

Anything that fails is parked (enriched_at stamped, external_data.reason recorded) rather than written, so a human can supply a city and re-run just that venue. Result on the first nine: 8 correct, 1 parked, 0 wrong — and the parked one does not exist in Italy at all.

Nominatim's usage policy is strict

One request per second, and an identifying User-Agent. Exceeding it blocks the whole project. The loop is sequential by design — never parallelise it.

Related

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