Summary
External tools (today: the Instagram collector in tools/instagram_post_creator) push
collected events into Flow through a scoped API key. Events land in a staging table,
never directly in public.events.
Why a staging table, not events.status='draft'
draft already means “a user is still writing it”. Machine output needs its own bucket,
and more importantly an unreviewed import must be structurally unable to reach the feed —
a separate table makes that a schema guarantee instead of a WHERE clause someone can
forget. Publishing is an explicit admin action.
Pieces
| Object | Role |
|---|---|
api_keys | sha256 hash only; the secret is shown once at creation. Scopes, expiry, revoke, last_used_at, use_count |
event_imports | staging rows: provenance (source/handle/post id/url), event fields, ticket_types, lineup, venue_key, recurrence, confidence, review workflow |
create_api_key(name, scopes, expires_at) | admin-only; returns the plaintext secret once |
revoke_api_key(id) | admin-only |
verify_api_key(key, scope) | used by the edge function; bumps usage counters |
publish_event_import(id, organizer_id) | copies a reviewed row into events with status='imported'. Idempotent |
approve_imported_event(event_id) | flips imported → active (goes live) |
Edge Function import-events | verify_jwt=false, authenticates via x-api-key, validates and de-duplicates |
New event status: imported
events.status now accepts imported. The public SELECT policy excludes it, so imported
events are invisible to users until an admin approves them.
Related visibility fix
Events are viewable by everyone was USING (true): 24 of 77 events were user drafts
readable by anyone, and imported would have leaked the same way. Replaced with
status in ('active','published') and deleted_at is null, plus events_owner_read_own
so organizers still see their own drafts. Verified: user 53, organizer +24 own drafts,
admin 77.
Guarantees verified end-to-end
- no key / forged key → 401
- valid key → row staged, 0 rows visible in the feed
- same post re-sent → counted as duplicate, not an error
venue_name"Italghisa Reggio Emilia!"→venue_keyitalghisa-reggio-emilia- publish → event with
status='imported'