mirror your GitHub repos to tangled.org automatically
1import { sql } from 'drizzle-orm'
2import {
3 bigint,
4 bigserial,
5 check,
6 customType,
7 index,
8 integer,
9 jsonb,
10 pgTable,
11 text,
12 timestamp,
13 unique,
14 uuid,
15} from 'drizzle-orm/pg-core'
16
17const bytea = customType<{ data: Buffer, notNull: false, default: false }>({
18 dataType() {
19 return 'bytea'
20 },
21})
22
23export const installation = pgTable('installation', {
24 id: bigint('id', { mode: 'number' }).primaryKey(),
25 // Cache of GitHub's account.login at last webhook; can drift if the user/org
26 // is renamed. Refresh on every webhook. Never use as a join key — join on
27 // accountId, which is stable.
28 accountLogin: text('account_login').notNull(),
29 accountId: bigint('account_id', { mode: 'number' }).notNull(),
30 accountType: text('account_type').notNull(),
31 // Set when GitHub fires installation.suspend; cleared on installation.unsuspend.
32 // The worker refuses to process jobs for suspended installs.
33 suspendedAt: timestamp('suspended_at', { withTimezone: true }),
34 createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
35}, table => [
36 check('installation_account_type_chk', sql`${table.accountType} in ('User','Organization')`),
37])
38
39export const userIdentity = pgTable('user_identity', {
40 did: text('did').primaryKey(),
41 handle: text('handle'),
42 installationId: bigint('installation_id', { mode: 'number' })
43 .references(() => installation.id, { onDelete: 'cascade' }),
44 createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
45 updatedAt: timestamp('updated_at', { withTimezone: true }),
46}, table => [
47 index('user_identity_installation_idx').on(table.installationId),
48])
49
50export const sshKey = pgTable('ssh_key', {
51 id: bigserial('id', { mode: 'number' }).primaryKey(),
52 installationId: bigint('installation_id', { mode: 'number' }).notNull()
53 .references(() => installation.id, { onDelete: 'cascade' }),
54 did: text('did').notNull(),
55 publicKey: text('public_key').notNull(),
56 privateKeyCiphertext: bytea('private_key_ciphertext').notNull(),
57 privateKeyNonce: bytea('private_key_nonce').notNull(),
58 tangledKeyRkey: text('tangled_key_rkey'),
59 createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
60 rotatedAt: timestamp('rotated_at', { withTimezone: true }),
61}, table => [
62 unique('ssh_key_installation_did_unq').on(table.installationId, table.did),
63 index('ssh_key_installation_idx').on(table.installationId),
64])
65
66export const repoMapping = pgTable('repo_mapping', {
67 id: bigserial('id', { mode: 'number' }).primaryKey(),
68 installationId: bigint('installation_id', { mode: 'number' }).notNull()
69 .references(() => installation.id, { onDelete: 'cascade' }),
70 githubRepoId: bigint('github_repo_id', { mode: 'number' }).notNull(),
71 // Cache of `<owner>/<name>` at last webhook; renames update this. Display only;
72 // never join on it. Stable identity is githubRepoId.
73 githubFullName: text('github_full_name').notNull(),
74 tangledRepoDid: text('tangled_repo_did'),
75 tangledFullName: text('tangled_full_name'),
76 knot: text('knot'),
77 primarySide: text('primary_side').notNull().default('github'),
78 // Loop-avoidance state: { 'refs/heads/main': '<sha>', ... }. Updated after a
79 // successful push to the replica side. See PLAN.md "Loop avoidance".
80 lastSyncedRefs: jsonb('last_synced_refs').notNull().default(sql`'{}'::jsonb`),
81 status: text('status').notNull().default('pending'),
82 lastError: text('last_error'),
83 // User-initiated stop. Worker skips jobs for repos with disabledAt set.
84 // Preserves the row (and its status) so we can resume on re-enable.
85 disabledAt: timestamp('disabled_at', { withTimezone: true }),
86 createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
87 updatedAt: timestamp('updated_at', { withTimezone: true }),
88}, table => [
89 unique('repo_mapping_installation_repo_unq').on(table.installationId, table.githubRepoId),
90 index('repo_mapping_installation_idx').on(table.installationId),
91 check('repo_mapping_primary_side_chk', sql`${table.primarySide} in ('github','tangled')`),
92 check('repo_mapping_status_chk', sql`${table.status} in ('pending','enrolling','active','error')`),
93])
94
95// Hand-rolled queue. Worker claim pattern: SELECT ... FOR UPDATE SKIP LOCKED.
96//
97// IMPORTANT: payload is a *minimal envelope* like
98// { deliveryId, installationId, githubRepoId, ref, before, after, kind }.
99// Do NOT store full webhook bodies here — they contain unbounded user-controlled
100// input (commit messages, descriptions, branch names). Re-fetch from the GitHub
101// API at job run time using the installation token; that gives us fresh data on
102// retry too.
103export const job = pgTable('job', {
104 id: bigserial('id', { mode: 'number' }).primaryKey(),
105 kind: text('kind').notNull(),
106 payload: jsonb('payload').notNull(),
107 attempts: integer('attempts').notNull().default(0),
108 runAfter: timestamp('run_after', { withTimezone: true }).defaultNow().notNull(),
109 lockedBy: text('locked_by'),
110 lockedUntil: timestamp('locked_until', { withTimezone: true }),
111 status: text('status').notNull().default('queued'),
112 lastError: text('last_error'),
113 createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
114 updatedAt: timestamp('updated_at', { withTimezone: true }),
115}, table => [
116 index('job_status_run_after_idx').on(table.status, table.runAfter),
117 check('job_status_chk', sql`${table.status} in ('queued','running','done','failed')`),
118])
119
120// Idempotency + audit trail for incoming webhooks. Keyed by X-GitHub-Delivery so
121// redeliveries are free-on-conflict. Intentionally has no `payload` column — see
122// the note on `job` above; we do not persist webhook bodies.
123export const webhookEvent = pgTable('webhook_event', {
124 deliveryId: uuid('delivery_id').primaryKey(),
125 source: text('source').notNull(),
126 event: text('event').notNull(),
127 receivedAt: timestamp('received_at', { withTimezone: true }).defaultNow().notNull(),
128 processedAt: timestamp('processed_at', { withTimezone: true }),
129}, table => [
130 check('webhook_event_source_chk', sql`${table.source} in ('github','tangled')`),
131])
132
133// AT Protocol OAuth stores. The values are encrypted at rest because they
134// contain access tokens, refresh tokens, and the DPoP private key for the
135// user's PDS. The encryption layer wraps the OAuth library's store interface
136// (encrypt in set, decrypt in get).
137export const atprotoState = pgTable('atproto_state', {
138 key: text('key').primaryKey(),
139 valueCiphertext: bytea('value_ciphertext').notNull(),
140 valueNonce: bytea('value_nonce').notNull(),
141 createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
142})
143
144export const atprotoSession = pgTable('atproto_session', {
145 sub: text('sub').primaryKey(),
146 valueCiphertext: bytea('value_ciphertext').notNull(),
147 valueNonce: bytea('value_nonce').notNull(),
148 createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
149 updatedAt: timestamp('updated_at', { withTimezone: true }),
150})