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