| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Canonical event log. The S26 spec called for a generic `events` |
| 4 | -- table; the S00-S25 audit's forward-plan finding flagged that S29 |
| 5 | -- (notifications) and S33 (webhooks) would also want their own log |
| 6 | -- table. Per the audit's recommendation we land the unified shape |
| 7 | -- here from day 1 — `domain_events` — so S29 and S33 don't have to |
| 8 | -- migrate the schema later. |
| 9 | -- |
| 10 | -- Schema columns: |
| 11 | -- actor_user_id — who did it (NULL for system events) |
| 12 | -- kind — short string identifying the event type |
| 13 | -- ("star", "unstar", "issue_comment_created", …) |
| 14 | -- repo_id — the repo the event is scoped to (NULL for |
| 15 | -- user-scoped events; reserved for org-scoped |
| 16 | -- events when S30 lands) |
| 17 | -- source_kind — what kind of object is the source/target |
| 18 | -- ("repo", "issue", "pull", "user", …) |
| 19 | -- source_id — the source object's id |
| 20 | -- public — whether this event is visible in public feeds. |
| 21 | -- Public-repo events default true; private-repo |
| 22 | -- events default false; user-scoped events follow |
| 23 | -- the user's profile-visibility setting. |
| 24 | -- payload — event-specific JSON. Keep small (<4 KiB); |
| 25 | -- bigger payloads belong in the source object |
| 26 | -- (referenced via source_kind/id). |
| 27 | -- |
| 28 | -- Read patterns: |
| 29 | -- * notifications fan-out (S29) consumes by polling on |
| 30 | -- created_at >= last_processed. |
| 31 | -- * webhooks (S33) the same. |
| 32 | -- * activity feeds (post-MVP) read public events sliced by repo |
| 33 | -- or by actor. |
| 34 | |
| 35 | -- +goose Up |
| 36 | CREATE TABLE domain_events ( |
| 37 | id bigserial PRIMARY KEY, |
| 38 | actor_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 39 | kind text NOT NULL, |
| 40 | repo_id bigint REFERENCES repos(id) ON DELETE CASCADE, |
| 41 | source_kind text NOT NULL, |
| 42 | source_id bigint NOT NULL, |
| 43 | public boolean NOT NULL DEFAULT false, |
| 44 | payload jsonb NOT NULL DEFAULT '{}'::jsonb, |
| 45 | created_at timestamptz NOT NULL DEFAULT now(), |
| 46 | |
| 47 | CONSTRAINT domain_events_kind_length CHECK (char_length(kind) BETWEEN 1 AND 64), |
| 48 | CONSTRAINT domain_events_source_kind_length CHECK (char_length(source_kind) BETWEEN 1 AND 32) |
| 49 | ); |
| 50 | |
| 51 | -- Notifications/webhooks consumers poll by created_at; partial-on |
| 52 | -- created_at would gain little since both consumers process every row. |
| 53 | CREATE INDEX domain_events_created_at_idx ON domain_events (created_at); |
| 54 | |
| 55 | -- Activity feed: events for a repo, recency-sorted. |
| 56 | CREATE INDEX domain_events_repo_created_idx |
| 57 | ON domain_events (repo_id, created_at DESC) |
| 58 | WHERE repo_id IS NOT NULL; |
| 59 | |
| 60 | -- Activity feed: events by an actor, recency-sorted. |
| 61 | CREATE INDEX domain_events_actor_created_idx |
| 62 | ON domain_events (actor_user_id, created_at DESC) |
| 63 | WHERE actor_user_id IS NOT NULL; |
| 64 | |
| 65 | -- Public-feed slice: only public rows, recency-sorted. |
| 66 | CREATE INDEX domain_events_public_created_idx |
| 67 | ON domain_events (created_at DESC) |
| 68 | WHERE public = true; |
| 69 | |
| 70 | -- +goose Down |
| 71 | DROP TABLE IF EXISTS domain_events; |
| 72 |