konnosDashboard
db.ts264 lines · main
1import { Hono, type Context } from 'hono';
2import { z } from 'zod';
3
4import { rateLimit } from '../middleware/rate-limit.js';
5import { requireProjectAuth, requireProjectRole } from '../middleware/project-auth.js';
6import { requireRecentMfa } from '../middleware/step-up.js';
7import type { ProjectAppEnv as AppEnv } from '../types/app-env.js';
8import { audit, hashIp } from '../services/audit.js';
9import { issueShellToken } from '../services/db-shell.js';
10import { getProjectInfo } from '../services/projects.js';
11import { createSnapshot, listSnapshots, restoreSnapshot } from '../services/snapshots.js';
12import {
13 checkProjectDbHealth,
14 dropProjectDatabase,
15 evictProjectPool,
16 provisionProjectDatabase,
17} from '../db/data-plane.js';
18
19function ipHash(c: Context<AppEnv>): string | null {
20 const fwd = c.req.raw.headers.get('x-forwarded-for');
21 const ip = fwd ? fwd.split(',')[0]!.trim() : null;
22 return hashIp(ip);
23}
24
25export const dbRouter = new Hono<AppEnv>();
26
27// `db/shell-token` rotates a privileged DSN — admin-tier.
28dbRouter.use('/v1/projects/:id/db/*', requireProjectAuth(), requireProjectRole('admin'));
29
30// why: 5/min per project is enough for a human-driven `briven db shell`
31// loop and restrictive enough that a leaked api key can't silently
32// harvest fresh DSNs.
33dbRouter.post(
34 '/v1/projects/:id/db/shell-token',
35 rateLimit({
36 scope: 'db-shell-token',
37 limit: 5,
38 windowMs: 60_000,
39 key: (c) => c.req.param('id') ?? null,
40 }),
41 async (c) => {
42 const projectId = c.req.param('id');
43 const user = c.get('user');
44 const apiKeyId = c.get('apiKeyId');
45
46 const { dsn, role, expiresAt } = await issueShellToken(projectId);
47
48 await audit({
49 actorId: user?.id ?? null,
50 projectId,
51 action: 'db.shell_token',
52 ipHash: ipHash(c),
53 userAgent: c.req.header('user-agent') ?? null,
54 // why: record expiry only; DSN + password are never audit-logged.
55 metadata: { expiresAt: expiresAt.toISOString(), via: apiKeyId ? 'api_key' : 'session' },
56 });
57
58 return c.json({ dsn, role, expiresAt: expiresAt.toISOString() });
59 },
60);
61
62/* ─── customer: per-project database lifecycle ──────────────────────── */
63//
64// Same capability as the admin database card, scoped to the caller's own
65// project. Router-level gate above already requires project role 'admin';
66// reprovision additionally requires 'owner'. The three MUTATIONS carry the
67// same recent-step-up rule as admin mutations (requireRecentMfa(10)) — the
68// dashboard surfaces an inline password prompt on 403 step_up_required.
69// That makes them session-only in practice: api keys / CLI JWTs can't
70// attest step-up, so agents use the MCP db_* tools instead.
71const dbMfa = requireRecentMfa(10);
72
73/**
74 * Health probe — reachability, latency, user-table count, HEAD commit.
75 * Fail-soft in the service (never throws), so this always answers 200 for
76 * an authorised caller. Also returns the caller's effective project role
77 * so the dashboard card can hide owner-only controls without a second
78 * round-trip.
79 */
80dbRouter.get('/v1/projects/:id/db/health', async (c) => {
81 const projectId = c.req.param('id');
82 const user = c.get('user');
83 const apiKeyId = c.get('apiKeyId');
84 const health = await checkProjectDbHealth(projectId);
85 await audit({
86 actorId: user?.id ?? null,
87 projectId,
88 action: 'project.database.health',
89 ipHash: ipHash(c),
90 userAgent: c.req.header('user-agent') ?? null,
91 metadata: { reachable: health.reachable, via: apiKeyId ? 'api_key' : 'session' },
92 });
93 return c.json({ health, role: c.get('projectRole') });
94});
95
96/** List the project's snapshots (recovery points), newest first. */
97dbRouter.get('/v1/projects/:id/db/snapshots', async (c) => {
98 const projectId = c.req.param('id');
99 const user = c.get('user');
100 const apiKeyId = c.get('apiKeyId');
101 const snapshots = await listSnapshots(projectId);
102 await audit({
103 actorId: user?.id ?? null,
104 projectId,
105 action: 'project.database.snapshots',
106 ipHash: ipHash(c),
107 userAgent: c.req.header('user-agent') ?? null,
108 metadata: { count: snapshots.length, via: apiKeyId ? 'api_key' : 'session' },
109 });
110 return c.json({ snapshots });
111});
112
113/**
114 * Restart the project's database connections: evict the cached pool so the
115 * very next query opens fresh with a fresh auth handshake. Clears the
116 * stuck-connection / stale-auth class of incidents without touching any
117 * data. Returns the post-restart health so the UI confirms in one trip.
118 */
119dbRouter.post(
120 '/v1/projects/:id/db/restart',
121 rateLimit({
122 scope: 'db-restart',
123 limit: 5,
124 windowMs: 60_000,
125 key: (c) => c.req.param('id') ?? null,
126 }),
127 dbMfa,
128 async (c) => {
129 const projectId = c.req.param('id');
130 const user = c.get('user');
131 await evictProjectPool(projectId);
132 const health = await checkProjectDbHealth(projectId);
133 await audit({
134 actorId: user?.id ?? null,
135 projectId,
136 action: 'project.database.restart',
137 ipHash: ipHash(c),
138 userAgent: c.req.header('user-agent') ?? null,
139 metadata: { reachable: health.reachable },
140 });
141 return c.json({ restarted: true, health });
142 },
143);
144
145const dbRecoverBody = z.object({
146 snapshotId: z.string().min(1),
147 confirm: z.string(),
148});
149
150/**
151 * Recover the project's database to a snapshot. Requires the literal
152 * confirm word "RECOVER" (same rule as the MCP db_recover tool). Always
153 * takes a fresh manual safety snapshot FIRST — so the recover itself is
154 * reversible — then hard-resets to the target and evicts the pool so no
155 * connection keeps serving pre-recover state. Audited with both ids.
156 */
157dbRouter.post(
158 '/v1/projects/:id/db/recover',
159 rateLimit({
160 scope: 'db-recover',
161 limit: 3,
162 windowMs: 300_000,
163 key: (c) => c.req.param('id') ?? null,
164 }),
165 dbMfa,
166 async (c) => {
167 const projectId = c.req.param('id');
168 const user = c.get('user');
169 const parsed = dbRecoverBody.safeParse(await c.req.json().catch(() => null));
170 if (!parsed.success) {
171 return c.json({ code: 'validation_failed', issues: parsed.error.issues }, 400);
172 }
173 if (parsed.data.confirm !== 'RECOVER') {
174 return c.json(
175 { code: 'confirm_mismatch', message: 'type RECOVER to confirm this recovery' },
176 400,
177 );
178 }
179 const pre = await createSnapshot(projectId, `pre-recover ${parsed.data.snapshotId}`, {
180 auto: false,
181 });
182 const { restored } = await restoreSnapshot(projectId, parsed.data.snapshotId);
183 await evictProjectPool(projectId);
184 await audit({
185 actorId: user?.id ?? null,
186 projectId,
187 action: 'project.database.recover',
188 ipHash: ipHash(c),
189 userAgent: c.req.header('user-agent') ?? null,
190 metadata: { snapshotId: parsed.data.snapshotId, preRecoverySnapshotId: pre.id },
191 });
192 return c.json({
193 recovered: true,
194 preRecoverySnapshotId: pre.id,
195 tablesAfterRecover: restored,
196 });
197 },
198);
199
200const dbReprovisionBody = z.object({
201 confirmName: z.string().min(1),
202 force: z.boolean().optional(),
203});
204
205/**
206 * Nuke-and-rebuild the project's database: drop it (data AND snapshots
207 * gone permanently) and provision a fresh empty one. Owner-only — api
208 * keys can never be minted at 'owner', so this is session-only by
209 * construction. Guarded by a typed confirmation (the project's slug or
210 * name, same as admin) and, like the MCP db_reprovision tool, refuses a
211 * healthy non-empty database unless `force` is set — a working database
212 * should be recovered, not razed.
213 */
214dbRouter.post(
215 '/v1/projects/:id/db/reprovision',
216 rateLimit({
217 scope: 'db-reprovision',
218 limit: 2,
219 windowMs: 3_600_000,
220 key: (c) => c.req.param('id') ?? null,
221 }),
222 requireProjectRole('owner'),
223 dbMfa,
224 async (c) => {
225 const projectId = c.req.param('id');
226 const user = c.get('user');
227 const parsed = dbReprovisionBody.safeParse(await c.req.json().catch(() => null));
228 if (!parsed.success) {
229 return c.json({ code: 'validation_failed', issues: parsed.error.issues }, 400);
230 }
231 const project = await getProjectInfo(projectId);
232 if (parsed.data.confirmName !== project.slug && parsed.data.confirmName !== project.name) {
233 return c.json(
234 {
235 code: 'confirm_mismatch',
236 message: 'confirmation does not match the project slug or name',
237 },
238 400,
239 );
240 }
241 const prior = await checkProjectDbHealth(projectId);
242 if (prior.reachable && (prior.tableCount ?? 0) > 0 && parsed.data.force !== true) {
243 return c.json(
244 {
245 code: 'healthy_database',
246 message: `the database is healthy with ${prior.tableCount} table(s) — recover it instead, or pass force to destroy everything`,
247 },
248 409,
249 );
250 }
251 await evictProjectPool(projectId);
252 await dropProjectDatabase(projectId);
253 await provisionProjectDatabase(projectId);
254 await audit({
255 actorId: user?.id ?? null,
256 projectId,
257 action: 'project.database.reprovision',
258 ipHash: ipHash(c),
259 userAgent: c.req.header('user-agent') ?? null,
260 metadata: { slug: project.slug, forced: parsed.data.force === true },
261 });
262 return c.json({ reprovisioned: true, health: await checkProjectDbHealth(projectId) });
263 },
264);