g1t/services/webhooks/migrations/0001_init.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Webhooks: every event, to your own addresses, signed and retried | 1 | -- Webhooks, and every delivery made to them. Every timestamp is RFC 3339 UTC. |
| 2 | ||
| 3 | CREATE TABLE hooks ( | |
| 4 | id TEXT PRIMARY KEY, | |
| 5 | -- repo or workspace. | |
| 6 | scope TEXT NOT NULL, | |
| 7 | -- The workspace's slug. | |
| 8 | workspace TEXT NOT NULL, | |
| 9 | -- For a repository's webhook: its id, and owner/name. | |
| 10 | repo_id TEXT, | |
| 11 | repo TEXT, | |
| 12 | url TEXT NOT NULL, | |
| 13 | -- The event types it is sent, as a JSON array; ["*"] for all. | |
| 14 | events TEXT NOT NULL, | |
| 15 | active INTEGER NOT NULL DEFAULT 1, | |
| 16 | -- The signing secret, sealed with AES-256-GCM under the service's key | |
| 17 | -- and bound to the hook's id. | |
| 18 | secret TEXT NOT NULL, | |
| 19 | secret_hint TEXT NOT NULL, | |
| 20 | created_by TEXT NOT NULL, | |
| 21 | created_at TEXT NOT NULL, | |
| 22 | last_status TEXT, | |
| 23 | last_delivered_at TEXT | |
| 24 | ); | |
| 25 | CREATE INDEX hooks_by_repo ON hooks (repo_id); | |
| 26 | CREATE INDEX hooks_by_workspace ON hooks (workspace, scope); | |
| 27 | ||
| 28 | CREATE TABLE deliveries ( | |
| 29 | id TEXT PRIMARY KEY, | |
| 30 | hook_id TEXT NOT NULL, | |
| 31 | -- The event delivered, or empty for a ping or a redelivery. | |
| 32 | event_id TEXT NOT NULL, | |
| 33 | event TEXT NOT NULL, | |
| 34 | -- The JSON sent. | |
| 35 | payload TEXT NOT NULL, | |
| 36 | -- pending, delivered or failed. | |
| 37 | status TEXT NOT NULL, | |
| 38 | attempts INTEGER NOT NULL DEFAULT 0, | |
| 39 | response_status INTEGER, | |
| 40 | response_body TEXT, | |
| 41 | error TEXT, | |
| 42 | duration_ms INTEGER, | |
| 43 | created_at TEXT NOT NULL, | |
| 44 | delivered_at TEXT, | |
| 45 | next_attempt_at TEXT | |
| 46 | ); | |
| 47 | CREATE INDEX deliveries_by_hook ON deliveries (hook_id, id); | |
| 48 | CREATE INDEX deliveries_due ON deliveries (status, next_attempt_at); | |
| 49 | -- An event is delivered to a webhook once, however often the bus repeats it. | |
| 50 | CREATE UNIQUE INDEX deliveries_once ON deliveries (hook_id, event_id) WHERE event_id <> ''; | |
| 51 | ||
| 52 | -- Which workspace a repository is in, so a workspace's webhooks find its | |
| 53 | -- repositories' events. Filled from repo.created, and on demand. | |
| 54 | CREATE TABLE repo_names ( | |
| 55 | repo_id TEXT PRIMARY KEY, | |
| 56 | namespace TEXT NOT NULL, | |
| 57 | name TEXT NOT NULL | |
| 58 | ); |