Events model

Planned

The events, event_authors and related tables are not yet shipped. This is the design we'll migrate to once the events module lands.

Source schema will live at src/infra/db/schema/event.schema.ts, following the same Drizzle conventions as the shipped profile.schema.ts.

events

Column	Type	Notes
id	UUID PK	defaultRandom()
creator_id	UUID	FK → auth.users.id
title	text	NOT NULL
description	text	nullable
image_url	text	Supabase Storage path
type	text	FK → event_types.code (lookup)
visibility	enum event_visibility	public, private
start_at	timestamptz	NOT NULL
end_at	timestamptz	nullable
location	geography(Point, 4326)	PostGIS — required
status	enum event_status	draft, published, started, finished, cancelled
capacity	integer	Default 200; CHECK ≥ 1
created_at	timestamptz	Default now()
updated_at	timestamptz	Default now()
deleted_at	timestamptz	Soft delete

Postgres enums

CREATE TYPE event_visibility AS ENUM ('public', 'private');
CREATE TYPE event_status AS ENUM (
  'draft', 'published', 'started', 'finished', 'cancelled'
);

Indexes

CREATE INDEX events_creator_idx ON events (creator_id);
CREATE INDEX events_status_idx  ON events (status);
CREATE INDEX events_type_idx    ON events (type);

-- PostGIS for radius queries
CREATE INDEX events_location_gix ON events USING GIST (location);

-- Feed composite, partial-deleted
CREATE INDEX events_feed_idx
  ON events (status, visibility, start_at)
  WHERE deleted_at IS NULL;

Status transitions

State transitions are documented in detail in Event Lifecycle. The DB enforces the values; the service layer enforces the transitions.

event_authors

Junction between events and their co-authors.

Column	Type
event_id	UUID FK → events.id ON DELETE CASCADE
user_id	UUID FK → auth.users.id
created_at	timestamptz

CREATE UNIQUE INDEX event_authors_uniq ON event_authors (event_id, user_id);
CREATE INDEX event_authors_user_idx ON event_authors (user_id);

The event creator is not stored here — they're already on events.creator_id. Authors are friends invited via POST /events/:id/authors.

RLS

Following the conventions in api/docs/RLS-NOTES.md:

• events: SELECT for authenticated is filtered to published rows that match the visibility rule (public OR friend-of-creator) via a SECURITY DEFINER helper function on the friendship graph. Cross-cutting feed queries still go through the API as service_role for performance.
• event_authors: SELECT for participants of the event; INSERT/DELETE via API only (creator authorization checked in code).

Relationship overview