db.js
8,075 bytes
| 1 | import { DatabaseSync } from "node:sqlite"; |
|---|---|
| 2 | import { mkdirSync } from "node:fs"; |
| 3 | import { dirname } from "node:path"; |
| 4 | import { createCipheriv, createDecipheriv, createHash, randomBytes } from "node:crypto"; |
| 5 | |
| 6 | export function createStore(path = ":memory:", secret = "test-only-secret") { |
| 7 | const key = createHash("sha256").update(secret).digest(); |
| 8 | function protect(value) { |
| 9 | if (!value) return null; |
| 10 | const iv = randomBytes(12); |
| 11 | const cipher = createCipheriv("aes-256-gcm", key, iv); |
| 12 | const encrypted = Buffer.concat([cipher.update(value, "utf8"), cipher.final()]); |
| 13 | return ["v1", iv.toString("base64url"), cipher.getAuthTag().toString("base64url"), encrypted.toString("base64url")].join("."); |
| 14 | } |
| 15 | function reveal(value) { |
| 16 | if (!value?.startsWith("v1.")) return value; |
| 17 | const [, iv, tag, encrypted] = value.split("."); |
| 18 | const decipher = createDecipheriv("aes-256-gcm", key, Buffer.from(iv, "base64url")); |
| 19 | decipher.setAuthTag(Buffer.from(tag, "base64url")); |
| 20 | return Buffer.concat([ |
| 21 | decipher.update(Buffer.from(encrypted, "base64url")), |
| 22 | decipher.final(), |
| 23 | ]).toString("utf8"); |
| 24 | } |
| 25 | if (path !== ":memory:") mkdirSync(dirname(path), { recursive: true }); |
| 26 | const db = new DatabaseSync(path); |
| 27 | db.exec(` |
| 28 | PRAGMA journal_mode = WAL; |
| 29 | CREATE TABLE IF NOT EXISTS owners ( |
| 30 | id INTEGER PRIMARY KEY, |
| 31 | login TEXT NOT NULL, |
| 32 | name TEXT, |
| 33 | avatar_url TEXT, |
| 34 | bio TEXT, |
| 35 | github_token TEXT NOT NULL, |
| 36 | installation_id INTEGER |
| 37 | ); |
| 38 | CREATE TABLE IF NOT EXISTS sessions ( |
| 39 | id TEXT PRIMARY KEY, |
| 40 | owner_id INTEGER, |
| 41 | oauth_state TEXT, |
| 42 | created_at TEXT NOT NULL |
| 43 | ); |
| 44 | CREATE TABLE IF NOT EXISTS shares ( |
| 45 | id TEXT PRIMARY KEY, |
| 46 | owner_id INTEGER NOT NULL, |
| 47 | created_at TEXT NOT NULL, |
| 48 | expires_at TEXT NOT NULL, |
| 49 | snapshot TEXT NOT NULL, |
| 50 | summary TEXT, |
| 51 | revoked_at TEXT |
| 52 | ); |
| 53 | `); |
| 54 | const sessionColumns = db.prepare("PRAGMA table_info(sessions)").all().map((column) => column.name); |
| 55 | if (!sessionColumns.includes("installation_state")) { |
| 56 | db.exec("ALTER TABLE sessions ADD COLUMN installation_state TEXT"); |
| 57 | } |
| 58 | const ownerColumns = db.prepare("PRAGMA table_info(owners)").all().map((column) => column.name); |
| 59 | if (!ownerColumns.includes("refresh_token")) { |
| 60 | db.exec("ALTER TABLE owners ADD COLUMN refresh_token TEXT"); |
| 61 | } |
| 62 | if (!ownerColumns.includes("token_expires_at")) { |
| 63 | db.exec("ALTER TABLE owners ADD COLUMN token_expires_at TEXT"); |
| 64 | } |
| 65 | const shareColumns = db.prepare("PRAGMA table_info(shares)").all().map((column) => column.name); |
| 66 | if (!shareColumns.includes("revoked_at")) { |
| 67 | db.exec("ALTER TABLE shares ADD COLUMN revoked_at TEXT"); |
| 68 | } |
| 69 | if (!shareColumns.includes("summary")) { |
| 70 | db.exec("ALTER TABLE shares ADD COLUMN summary TEXT"); |
| 71 | } |
| 72 | |
| 73 | return { |
| 74 | createSession(id, now = new Date()) { |
| 75 | db.prepare("INSERT INTO sessions (id, created_at) VALUES (?, ?)").run(id, now.toISOString()); |
| 76 | }, |
| 77 | pruneSessions(currentTime = new Date()) { |
| 78 | const abandonedBefore = new Date(currentTime.getTime() - 60 * 60 * 1000).toISOString(); |
| 79 | const expiredBefore = new Date(currentTime.getTime() - 30 * 24 * 60 * 60 * 1000).toISOString(); |
| 80 | db.prepare(` |
| 81 | DELETE FROM sessions |
| 82 | WHERE (owner_id IS NULL AND created_at < ?) OR created_at < ? |
| 83 | `).run(abandonedBefore, expiredBefore); |
| 84 | }, |
| 85 | getSession(id) { |
| 86 | return db.prepare("SELECT * FROM sessions WHERE id = ?").get(id); |
| 87 | }, |
| 88 | deleteSession(id) { |
| 89 | db.prepare("DELETE FROM sessions WHERE id = ?").run(id); |
| 90 | }, |
| 91 | setSessionState(id, state) { |
| 92 | db.prepare("UPDATE sessions SET oauth_state = ? WHERE id = ?").run(state, id); |
| 93 | }, |
| 94 | setInstallationState(id, state) { |
| 95 | db.prepare("UPDATE sessions SET installation_state = ? WHERE id = ?").run(state, id); |
| 96 | }, |
| 97 | clearInstallationState(id) { |
| 98 | db.prepare("UPDATE sessions SET installation_state = NULL WHERE id = ?").run(id); |
| 99 | }, |
| 100 | attachOwner(id, ownerId) { |
| 101 | db.prepare("UPDATE sessions SET owner_id = ? WHERE id = ?").run(ownerId, id); |
| 102 | }, |
| 103 | upsertOwner(owner, credentials) { |
| 104 | db.prepare(` |
| 105 | INSERT INTO owners (id, login, name, avatar_url, bio, github_token, refresh_token, token_expires_at) |
| 106 | VALUES (?, ?, ?, ?, ?, ?, ?, ?) |
| 107 | ON CONFLICT(id) DO UPDATE SET |
| 108 | login = excluded.login, name = excluded.name, avatar_url = excluded.avatar_url, |
| 109 | bio = excluded.bio, github_token = excluded.github_token, |
| 110 | refresh_token = excluded.refresh_token, token_expires_at = excluded.token_expires_at |
| 111 | `).run( |
| 112 | owner.id, |
| 113 | owner.login, |
| 114 | owner.name, |
| 115 | owner.avatar_url, |
| 116 | owner.bio, |
| 117 | protect(credentials.accessToken), |
| 118 | protect(credentials.refreshToken), |
| 119 | credentials.expiresAt, |
| 120 | ); |
| 121 | return this.getOwner(owner.id); |
| 122 | }, |
| 123 | updateOwnerTokens(ownerId, credentials) { |
| 124 | db.prepare(` |
| 125 | UPDATE owners |
| 126 | SET github_token = ?, refresh_token = ?, token_expires_at = ? |
| 127 | WHERE id = ? |
| 128 | `).run( |
| 129 | protect(credentials.accessToken), |
| 130 | protect(credentials.refreshToken), |
| 131 | credentials.expiresAt, |
| 132 | ownerId, |
| 133 | ); |
| 134 | return this.getOwner(ownerId); |
| 135 | }, |
| 136 | updateOwnerProfile(owner) { |
| 137 | db.prepare(` |
| 138 | UPDATE owners |
| 139 | SET login = ?, name = ?, avatar_url = ?, bio = ? |
| 140 | WHERE id = ? |
| 141 | `).run(owner.login, owner.name, owner.avatar_url, owner.bio, owner.id); |
| 142 | return this.getOwner(owner.id); |
| 143 | }, |
| 144 | getOwner(id) { |
| 145 | const owner = db.prepare("SELECT * FROM owners WHERE id = ?").get(id); |
| 146 | return owner ? { |
| 147 | ...owner, |
| 148 | github_token: reveal(owner.github_token), |
| 149 | refresh_token: reveal(owner.refresh_token), |
| 150 | } : undefined; |
| 151 | }, |
| 152 | setInstallation(ownerId, installationId) { |
| 153 | db.prepare("UPDATE owners SET installation_id = ? WHERE id = ?").run(installationId, ownerId); |
| 154 | }, |
| 155 | createShare(share) { |
| 156 | const summary = { |
| 157 | repositories: (share.snapshot?.repositories || []).map((repository) => ({ name: repository.name })), |
| 158 | }; |
| 159 | db.prepare(` |
| 160 | INSERT INTO shares (id, owner_id, created_at, expires_at, snapshot, summary) |
| 161 | VALUES (?, ?, ?, ?, ?, ?) |
| 162 | `).run( |
| 163 | share.id, |
| 164 | share.ownerId, |
| 165 | share.createdAt, |
| 166 | share.expiresAt, |
| 167 | JSON.stringify(share.snapshot), |
| 168 | JSON.stringify(summary), |
| 169 | ); |
| 170 | }, |
| 171 | getShare(id) { |
| 172 | const row = db.prepare("SELECT * FROM shares WHERE id = ?").get(id); |
| 173 | return row ? { ...row, snapshot: JSON.parse(row.snapshot) } : undefined; |
| 174 | }, |
| 175 | listSharesForOwner(ownerId) { |
| 176 | return db.prepare(` |
| 177 | SELECT id, owner_id, created_at, expires_at, revoked_at, summary, |
| 178 | CASE WHEN summary IS NULL THEN snapshot ELSE NULL END AS legacy_snapshot |
| 179 | FROM shares |
| 180 | WHERE owner_id = ? |
| 181 | ORDER BY created_at DESC |
| 182 | `).all(ownerId).map((row) => { |
| 183 | const legacySnapshot = row.legacy_snapshot ? JSON.parse(row.legacy_snapshot) : null; |
| 184 | return { |
| 185 | id: row.id, |
| 186 | owner_id: row.owner_id, |
| 187 | created_at: row.created_at, |
| 188 | expires_at: row.expires_at, |
| 189 | revoked_at: row.revoked_at, |
| 190 | summary: row.summary |
| 191 | ? JSON.parse(row.summary) |
| 192 | : { |
| 193 | repositories: (legacySnapshot?.repositories || []).map((repository) => ({ |
| 194 | name: repository.name, |
| 195 | })), |
| 196 | }, |
| 197 | }; |
| 198 | }); |
| 199 | }, |
| 200 | revokeShare(id, ownerId, revokedAt) { |
| 201 | const result = db.prepare(` |
| 202 | UPDATE shares |
| 203 | SET snapshot = 'null', summary = NULL, revoked_at = ? |
| 204 | WHERE id = ? AND owner_id = ? AND revoked_at IS NULL |
| 205 | `).run(revokedAt, id, ownerId); |
| 206 | return result.changes > 0; |
| 207 | }, |
| 208 | purgeShareSnapshot(id) { |
| 209 | db.prepare("UPDATE shares SET snapshot = 'null' WHERE id = ?").run(id); |
| 210 | }, |
| 211 | close() { |
| 212 | db.close(); |
| 213 | }, |
| 214 | sessionCount() { |
| 215 | return db.prepare("SELECT COUNT(*) AS count FROM sessions").get().count; |
| 216 | }, |
| 217 | }; |
| 218 | } |
| 219 | |