konnosDashboard
storage-admin.ts561 lines · main
1import { ValidationError } from '@briven/shared';
2import { and, eq, inArray, isNull, sql } from 'drizzle-orm';
3
4import { getDb } from '../db/client.js';
5import { runInProjectDatabase } from '../db/data-plane.js';
6import { projectFiles, projects, storageKeys, tierStorageCaps, type ProjectTier } from '../db/schema.js';
7import { log } from '../lib/logger.js';
8import { TIERS } from './tiers.js';
9// NOTE: `listProjectsForUser` (from ./projects.js) is imported LAZILY inside
10// listMyStorageUsage — NOT statically here. projects.js pulls the full
11// data-plane surface (dropProjectDatabase/provisionProjectDatabase), which the
12// enforcement unit test stubs out with a minimal mock that only exposes
13// runInProjectDatabase. A static import would make loading this module fail
14// under that mock; a dynamic import keeps the module load clean while the
15// runtime behaviour is identical.
16
17/**
18 * Storage admin (sprint plan Sprint 4).
19 *
20 * DoltGres can't report byte sizes (pg_total_relation_size returns 0 — see
21 * services/usage.ts), so the operator dashboard measures what DoltGres CAN
22 * count: the number of user tables and the total row count across them.
23 *
24 * Phase 1 = read-only usage truth. Per-project / per-tier limits + flagging
25 * arrive in Phase 2; the admin web page in Phase 3; enforcement (blocking) is
26 * a deferred Phase 4. v1 NEVER blocks a customer — it only surfaces usage.
27 */
28
29export interface ProjectRowCount {
30 /** Total rows summed across every user table (excludes _briven_* bookkeeping). */
31 readonly rowCount: number;
32 /** Number of user tables (excludes _briven_*). */
33 readonly tableCount: number;
34}
35
36/**
37 * Count tables + total rows in a project's OWN DoltGres database.
38 *
39 * One connection, one transaction: list the user tables from the catalog (the
40 * same pg_class filter getStorageUsage uses — proven on DoltGres), then run a
41 * plain COUNT(*) per table and sum in JS. COUNT(*) and the pg_class listing are
42 * both DoltGres-safe; no pg_total_relation_size / generate_series.
43 *
44 * Returns zeros (never throws) when the project DB isn't provisioned yet or a
45 * count fails — the dashboard renders that as "—" naturally, exactly like
46 * getStorageUsage.
47 *
48 * N+1 COUNT queries per project is acceptable at current scale (few projects,
49 * few tables). If table counts grow large, move this behind the hourly usage
50 * aggregator (a periodic snapshot) — flagged in the plan's Notes.
51 */
52export async function getProjectRowCount(projectId: string): Promise<ProjectRowCount> {
53 try {
54 return await runInProjectDatabase(projectId, async (tx) => {
55 const tables = (await tx.unsafe(`
56 SELECT c.relname AS name
57 FROM pg_class c
58 JOIN pg_namespace n ON n.oid = c.relnamespace
59 WHERE n.nspname = 'public'
60 AND c.relkind IN ('r', 'p') -- ordinary + partitioned tables
61 AND left(c.relname, 8) <> '_briven_'
62 `)) as Array<{ name: string }>;
63
64 let rowCount = 0;
65 for (const t of tables) {
66 // t.name comes from the system catalog (not user input); quote it so
67 // mixed-case / reserved names still resolve.
68 const counted = (await tx.unsafe(
69 `SELECT COUNT(*)::bigint AS n FROM "${t.name}"`,
70 )) as Array<{ n: string }>;
71 rowCount += counted[0] ? Number.parseInt(counted[0].n, 10) : 0;
72 }
73 return { rowCount, tableCount: tables.length };
74 });
75 } catch (err) {
76 log.warn('project_row_count_failed', {
77 projectId,
78 message: err instanceof Error ? err.message : String(err),
79 });
80 return { rowCount: 0, tableCount: 0 };
81 }
82}
83
84/* ── Tier storage caps (DB-backed, admin-editable — Phase 2) ─────────────── */
85
86export interface StorageCap {
87 readonly maxRows: number;
88 readonly maxTables: number;
89}
90
91/**
92 * The Free/Pro/Team caps from `tier_storage_caps`. Read fresh each call (only
93 * the admin page + the per-project flag math hit it; not a hot path) so an
94 * edit takes effect immediately.
95 */
96export async function getTierStorageCaps(): Promise<Record<ProjectTier, StorageCap>> {
97 const db = getDb();
98 const rows = await db.select().from(tierStorageCaps);
99 const out = {} as Record<ProjectTier, StorageCap>;
100 for (const r of rows) {
101 out[r.tier] = { maxRows: Number(r.maxRows), maxTables: Number(r.maxTables) };
102 }
103 return out;
104}
105
106function assertNonNegInt(value: number, field: string): void {
107 if (!Number.isInteger(value) || value < 0) {
108 throw new ValidationError(`${field} must be a non-negative whole number`, { field, value });
109 }
110}
111
112/** Update one tier's caps. Takes effect immediately — no redeploy. */
113export async function updateTierStorageCap(
114 tier: ProjectTier,
115 caps: StorageCap,
116 actorId: string | null,
117): Promise<void> {
118 assertNonNegInt(caps.maxRows, 'maxRows');
119 assertNonNegInt(caps.maxTables, 'maxTables');
120 const db = getDb();
121 await db
122 .update(tierStorageCaps)
123 .set({ maxRows: caps.maxRows, maxTables: caps.maxTables, updatedAt: new Date(), updatedBy: actorId })
124 .where(eq(tierStorageCaps.tier, tier));
125}
126
127/**
128 * Set (or clear) a single project's storage override. Pass null for a field to
129 * clear it — the project then inherits its tier cap for that field.
130 */
131export async function setProjectStorageLimit(
132 projectId: string,
133 limit: { maxRows: number | null; maxTables: number | null },
134 _actorId: string | null,
135): Promise<void> {
136 if (limit.maxRows != null) assertNonNegInt(limit.maxRows, 'maxRows');
137 if (limit.maxTables != null) assertNonNegInt(limit.maxTables, 'maxTables');
138 const db = getDb();
139 await db
140 .update(projects)
141 .set({ storageMaxRows: limit.maxRows, storageMaxTables: limit.maxTables, updatedAt: new Date() })
142 .where(eq(projects.id, projectId));
143}
144
145/* ── Over-limit flag math (pure — unit tested) ───────────────────────────── */
146
147export interface LimitFlags {
148 readonly overRows: boolean;
149 readonly overTables: boolean;
150 readonly overLimit: boolean;
151}
152
153/**
154 * Pure over-limit decision. "At the cap" is NOT over (strictly greater than).
155 * Extracted so the flag math is unit-tested without a database.
156 */
157export function evaluateStorageLimit(input: {
158 rowCount: number;
159 tableCount: number;
160 maxRows: number;
161 maxTables: number;
162}): LimitFlags {
163 const overRows = input.rowCount > input.maxRows;
164 const overTables = input.tableCount > input.maxTables;
165 return { overRows, overTables, overLimit: overRows || overTables };
166}
167
168/* ── Usage list with over-limit flagging (Phase 2) ───────────────────────── */
169
170export interface ProjectStorageUsage {
171 readonly id: string;
172 readonly name: string;
173 readonly tier: ProjectTier;
174 readonly tableCount: number;
175 readonly rowCount: number;
176 /** Effective caps = per-project override ?? tier cap. */
177 readonly maxRows: number;
178 readonly maxTables: number;
179 /** True when the per-project override is set (vs inheriting the tier cap). */
180 readonly hasOverride: boolean;
181 /** Enforcement mode: 'flag' (measure-only) or 'block' (refuse over-cap writes). */
182 readonly enforcement: EnforcementMode;
183 readonly overRows: boolean;
184 readonly overTables: boolean;
185 readonly overLimit: boolean;
186}
187
188/**
189 * Usage for every live project, with effective caps + over-limit flags — drives
190 * the admin storage page. Counts run in parallel across projects (each hits a
191 * different project DB pool). v1 only FLAGS (overLimit); enforcement/blocking is
192 * the deferred Phase 4.
193 */
194export async function listStorageUsage(): Promise<readonly ProjectStorageUsage[]> {
195 const db = getDb();
196 const caps = await getTierStorageCaps();
197 const rows = await db
198 .select({
199 id: projects.id,
200 name: projects.name,
201 tier: projects.tier,
202 storageMaxRows: projects.storageMaxRows,
203 storageMaxTables: projects.storageMaxTables,
204 storageEnforcementMode: projects.storageEnforcementMode,
205 })
206 .from(projects)
207 .where(isNull(projects.deletedAt));
208
209 return Promise.all(
210 rows.map(async (p) => {
211 const { rowCount, tableCount } = await getProjectRowCount(p.id);
212 // Missing tier cap (shouldn't happen post-seed) → Infinity = never over.
213 const tierCap = caps[p.tier] ?? { maxRows: Infinity, maxTables: Infinity };
214 const maxRows = p.storageMaxRows ?? tierCap.maxRows;
215 const maxTables = p.storageMaxTables ?? tierCap.maxTables;
216 const flags = evaluateStorageLimit({ rowCount, tableCount, maxRows, maxTables });
217 return {
218 id: p.id,
219 name: p.name,
220 tier: p.tier,
221 tableCount,
222 rowCount,
223 maxRows,
224 maxTables,
225 hasOverride: p.storageMaxRows != null || p.storageMaxTables != null,
226 enforcement: p.storageEnforcementMode === 'block' ? 'block' : 'flag',
227 ...flags,
228 };
229 }),
230 );
231}
232
233/* ── Object-storage (S3/file) usage mirror (read-only) ───────────────────── */
234
235export interface ObjectStorageUsage {
236 readonly id: string;
237 readonly name: string;
238 readonly tier: ProjectTier;
239 /** SUM of non-deleted project_files.size_bytes (TEXT → number in SQL). */
240 readonly usedBytes: number;
241 /** Tier storage byte cap (TIERS[tier].storageBytes). */
242 readonly capBytes: number;
243 /** File-recovery window in days (TIERS[tier].storageRecoveryDays). */
244 readonly recoveryDays: number;
245 /** Count of active (not-revoked) storage_keys for the project. */
246 readonly keyCount: number;
247 readonly over: boolean;
248}
249
250/**
251 * Read-only object-storage mirror for the admin storage page. Additive to the
252 * DoltGres row/table view above: this reports each live project's S3 file usage
253 * (sum of non-deleted project_files bytes) against its tier byte cap, its
254 * recovery window, and its active storage-key count.
255 *
256 * Uses the SAME project source as listStorageUsage (so the same projects, with
257 * their tier, appear) and TWO grouped aggregate queries — one over project_files,
258 * one over storage_keys — joined to the project list in JS via Maps. No per-
259 * project queries. size_bytes is TEXT, so it's cast to numeric in SQL and read
260 * back as a number (missing project → 0).
261 */
262export async function listObjectStorageUsage(): Promise<readonly ObjectStorageUsage[]> {
263 const db = getDb();
264
265 const rows = await db
266 .select({ id: projects.id, name: projects.name, tier: projects.tier })
267 .from(projects)
268 .where(isNull(projects.deletedAt));
269
270 // Grouped SUM of non-deleted file bytes per project (size_bytes is TEXT).
271 const byteRows = await db
272 .select({
273 projectId: projectFiles.projectId,
274 usedBytes: sql<number>`coalesce(sum(cast(${projectFiles.sizeBytes} as bigint)), 0)::bigint`,
275 })
276 .from(projectFiles)
277 .where(isNull(projectFiles.deletedAt))
278 .groupBy(projectFiles.projectId);
279 const bytesByProject = new Map<string, number>();
280 for (const r of byteRows) bytesByProject.set(r.projectId, Number(r.usedBytes) || 0);
281
282 // Grouped count of active (not-revoked) storage keys per project.
283 const keyRows = await db
284 .select({
285 projectId: storageKeys.projectId,
286 keyCount: sql<number>`count(*)::int`,
287 })
288 .from(storageKeys)
289 .where(isNull(storageKeys.revokedAt))
290 .groupBy(storageKeys.projectId);
291 const keysByProject = new Map<string, number>();
292 for (const r of keyRows) keysByProject.set(r.projectId, Number(r.keyCount) || 0);
293
294 return rows.map((p) => {
295 const usedBytes = bytesByProject.get(p.id) ?? 0;
296 const capBytes = TIERS[p.tier].storageBytes;
297 return {
298 id: p.id,
299 name: p.name,
300 tier: p.tier,
301 usedBytes,
302 capBytes,
303 recoveryDays: TIERS[p.tier].storageRecoveryDays,
304 keyCount: keysByProject.get(p.id) ?? 0,
305 over: usedBytes > capBytes,
306 };
307 });
308}
309
310/* ── Per-user object-storage usage (dashboard "S3 bucket" home) ──────────── */
311
312export interface MyStorageUsage {
313 readonly id: string;
314 readonly name: string;
315 readonly tier: ProjectTier;
316 /** SUM of non-deleted project_files.size_bytes (TEXT → number in SQL). */
317 readonly usedBytes: number;
318 /** Tier storage byte cap (TIERS[tier].storageBytes). */
319 readonly capBytes: number;
320 /** Count of active (not-revoked) storage_keys for the project. */
321 readonly keyCount: number;
322 readonly over: boolean;
323}
324
325/**
326 * The signed-in user's object-storage usage across every project they belong
327 * to — the cross-project "S3 bucket" dashboard home. The user-scoped mirror of
328 * listObjectStorageUsage: same two grouped aggregate queries (file bytes +
329 * active key count), but restricted to the caller's own project ids via
330 * listProjectsForUser (org- OR project-scoped membership). Never leaks another
331 * user's project. Returns [] when the user has no projects.
332 */
333export async function listMyStorageUsage(
334 userId: string,
335): Promise<readonly MyStorageUsage[]> {
336 const { listProjectsForUser } = await import('./projects.js');
337 const mine = await listProjectsForUser(userId);
338 if (mine.length === 0) return [];
339 const ids = mine.map((p) => p.id);
340 const db = getDb();
341
342 // Grouped SUM of non-deleted file bytes per project, restricted to the
343 // user's project ids (size_bytes is TEXT → cast to bigint in SQL).
344 const byteRows = await db
345 .select({
346 projectId: projectFiles.projectId,
347 usedBytes: sql<number>`coalesce(sum(cast(${projectFiles.sizeBytes} as bigint)), 0)::bigint`,
348 })
349 .from(projectFiles)
350 .where(and(isNull(projectFiles.deletedAt), inArray(projectFiles.projectId, ids)))
351 .groupBy(projectFiles.projectId);
352 const bytesByProject = new Map<string, number>();
353 for (const r of byteRows) bytesByProject.set(r.projectId, Number(r.usedBytes) || 0);
354
355 // Grouped count of active (not-revoked) storage keys per project.
356 const keyRows = await db
357 .select({
358 projectId: storageKeys.projectId,
359 keyCount: sql<number>`count(*)::int`,
360 })
361 .from(storageKeys)
362 .where(and(isNull(storageKeys.revokedAt), inArray(storageKeys.projectId, ids)))
363 .groupBy(storageKeys.projectId);
364 const keysByProject = new Map<string, number>();
365 for (const r of keyRows) keysByProject.set(r.projectId, Number(r.keyCount) || 0);
366
367 return mine.map((p) => {
368 const usedBytes = bytesByProject.get(p.id) ?? 0;
369 const capBytes = TIERS[p.tier].storageBytes;
370 return {
371 id: p.id,
372 name: p.name,
373 tier: p.tier,
374 usedBytes,
375 capBytes,
376 keyCount: keysByProject.get(p.id) ?? 0,
377 over: usedBytes > capBytes,
378 };
379 });
380}
381
382/* ── Enforcement ("flag" → "block") — Sprint 4 Phase 4 ───────────────────── */
383
384/**
385 * Per-project enforcement mode.
386 * - 'flag' (default, safe): measure-only. Over-limit is surfaced in the admin
387 * dashboard but NEVER blocks a customer write.
388 * - 'block' (opt-in): once the project is over its effective cap, a new row /
389 * table / byte write is refused. An admin flips a single project in.
390 * The enforcement lever fails OPEN throughout: any lookup that can't run is
391 * treated as "don't block".
392 */
393export type EnforcementMode = 'flag' | 'block';
394
395/**
396 * In-memory cache of the enforcement mode per project. This is read on the hot
397 * write path (every studio INSERT / CREATE TABLE / upload), so a fresh control-
398 * DB round-trip per write would be wasteful — modes change rarely (an admin
399 * toggle). setProjectEnforcement invalidates the entry so a flip takes effect
400 * immediately; _resetEnforcementCache clears it wholesale (test hook).
401 */
402const enforcementCache = new Map<string, EnforcementMode>();
403
404/** Test hook — clears the whole enforcement-mode cache. */
405export function _resetEnforcementCache(): void {
406 enforcementCache.clear();
407}
408
409/**
410 * The project's enforcement mode, cached in-memory. Defaults to 'flag' for an
411 * unknown/absent project (fail-open). Reads the projects row's mode; a second
412 * call for the same id is served from cache (no re-query).
413 */
414export async function getProjectEnforcement(projectId: string): Promise<EnforcementMode> {
415 const cached = enforcementCache.get(projectId);
416 if (cached !== undefined) return cached;
417
418 const db = getDb();
419 const rows = await db
420 .select({ mode: projects.storageEnforcementMode })
421 .from(projects)
422 .where(eq(projects.id, projectId))
423 .limit(1);
424 const mode: EnforcementMode = rows[0]?.mode === 'block' ? 'block' : 'flag';
425 enforcementCache.set(projectId, mode);
426 return mode;
427}
428
429/**
430 * Set a project's enforcement mode. Rejects an invalid mode with a
431 * ValidationError, writes the column, and invalidates the cache entry so the
432 * next read re-queries.
433 */
434export async function setProjectEnforcement(
435 projectId: string,
436 mode: EnforcementMode,
437 _actorId: string | null,
438): Promise<void> {
439 if (mode !== 'flag' && mode !== 'block') {
440 throw new ValidationError("enforcement mode must be 'flag' or 'block'", { field: 'mode', value: mode });
441 }
442 const db = getDb();
443 await db
444 .update(projects)
445 .set({ storageEnforcementMode: mode, updatedAt: new Date() })
446 .where(eq(projects.id, projectId));
447 // Invalidate so the next getProjectEnforcement re-reads the new value.
448 enforcementCache.delete(projectId);
449}
450
451/**
452 * Resolve a project's effective row/table caps: per-project override ?? tier
453 * cap. Returns null when the project row can't be found — the caller treats
454 * that as fail-open (never block a write we can't reason about).
455 */
456async function resolveEffectiveCaps(
457 projectId: string,
458): Promise<{ maxRows: number; maxTables: number } | null> {
459 const db = getDb();
460 const rows = await db
461 .select({
462 tier: projects.tier,
463 storageMaxRows: projects.storageMaxRows,
464 storageMaxTables: projects.storageMaxTables,
465 })
466 .from(projects)
467 .where(eq(projects.id, projectId))
468 .limit(1);
469 const p = rows[0];
470 if (!p) return null; // fail-open — unknown project
471 const caps = await getTierStorageCaps();
472 const tierCap = caps[p.tier as ProjectTier] ?? { maxRows: Infinity, maxTables: Infinity };
473 return {
474 maxRows: p.storageMaxRows ?? tierCap.maxRows,
475 maxTables: p.storageMaxTables ?? tierCap.maxTables,
476 };
477}
478
479/**
480 * Guard a row / table write against the project's storage cap.
481 *
482 * 'flag' mode is a pure no-op — it never throws and never counts rows (so the
483 * project DB is never touched on the hot path when enforcement is off). In
484 * 'block' mode it resolves the effective cap (override ?? tier), reads the
485 * current counts, and throws a ValidationError if adding `adding` more would
486 * exceed the cap (strictly greater than). Fails OPEN — no throw, no count — if
487 * the project can't be resolved even in block mode.
488 *
489 * @param adding how many rows/tables this write adds (default 1; the bulk
490 * insert path passes the batch length).
491 */
492export async function assertWithinStorageLimit(
493 projectId: string,
494 kind: 'row' | 'table',
495 adding = 1,
496): Promise<void> {
497 if ((await getProjectEnforcement(projectId)) !== 'block') return; // flag → no-op
498
499 const caps = await resolveEffectiveCaps(projectId);
500 if (!caps) return; // fail-open — unknown project
501
502 const { rowCount, tableCount } = await getProjectRowCount(projectId);
503 if (kind === 'row') {
504 if (rowCount + adding > caps.maxRows) {
505 throw new ValidationError(
506 `storage limit reached: adding ${adding} row(s) would exceed the ${caps.maxRows}-row cap (currently ${rowCount})`,
507 { field: 'rows', current: rowCount, adding, cap: caps.maxRows },
508 );
509 }
510 } else {
511 if (tableCount + adding > caps.maxTables) {
512 throw new ValidationError(
513 `storage limit reached: adding ${adding} table(s) would exceed the ${caps.maxTables}-table cap (currently ${tableCount})`,
514 { field: 'tables', current: tableCount, adding, cap: caps.maxTables },
515 );
516 }
517 }
518}
519
520/**
521 * Guard an S3/object upload against the project's tier BYTE cap. 'flag' → no-op.
522 * 'block' → throw a ValidationError when current object bytes + additionalBytes
523 * exceeds the tier byte cap (TIERS[tier].storageBytes — the SAME cap
524 * listObjectStorageUsage flags against). Fails OPEN if it can't resolve.
525 *
526 * Reuses the current-bytes computation from listObjectStorageUsage: SUM of
527 * non-deleted project_files.size_bytes for the one project.
528 */
529export async function assertWithinByteLimit(
530 projectId: string,
531 additionalBytes: number,
532): Promise<void> {
533 if ((await getProjectEnforcement(projectId)) !== 'block') return; // flag → no-op
534
535 const db = getDb();
536 const rows = await db
537 .select({ tier: projects.tier })
538 .from(projects)
539 .where(eq(projects.id, projectId))
540 .limit(1);
541 const p = rows[0];
542 if (!p) return; // fail-open — unknown project
543 const capBytes = TIERS[p.tier as ProjectTier]?.storageBytes;
544 if (capBytes == null) return; // fail-open — no cap resolvable
545
546 // Same grouped SUM listObjectStorageUsage uses, scoped to this one project.
547 const byteRows = await db
548 .select({
549 usedBytes: sql<number>`coalesce(sum(cast(${projectFiles.sizeBytes} as bigint)), 0)::bigint`,
550 })
551 .from(projectFiles)
552 .where(and(isNull(projectFiles.deletedAt), eq(projectFiles.projectId, projectId)));
553 const usedBytes = Number(byteRows[0]?.usedBytes) || 0;
554
555 if (usedBytes + additionalBytes > capBytes) {
556 throw new ValidationError(
557 `storage limit reached: this ${additionalBytes}-byte upload would exceed the project's ${capBytes}-byte cap (currently ${usedBytes})`,
558 { field: 'bytes', current: usedBytes, adding: additionalBytes, cap: capBytes },
559 );
560 }
561}