g1t/services/webhooks/migrations/0001_init.sql
| 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 | ); |