konnosDashboard
usage.ts220 lines · main
1import { and, eq, gte, lt, sql } from 'drizzle-orm';
2
3import { getDb } from '../db/client.js';
4import { dbNameFor, runInProjectDatabase } from '../db/data-plane.js';
5import { functionLogs, usageEvents } from '../db/schema.js';
6import { log } from '../lib/logger.js';
7
8/**
9 * Phase 3 usage metering — invocations slice.
10 *
11 * Reads the meta-DB's `function_logs` table (where `apps/api/src/workers/
12 * log-fanout.ts` durably persists every invocation envelope from the
13 * runtime). Aggregates `count` and `totalDurationMs` for billing /
14 * dashboard surfaces.
15 *
16 * Caveat: `function_logs` is pruned to 7 days on free tier (see
17 * `pruneOldFunctionLogs` in `workers/log-fanout.ts`). Querying outside
18 * the retention window returns under-counted data. Resolving this needs
19 * either tier-aware retention extension, or a periodic `usage_rollups`
20 * snapshot landed before pruning runs — both deferred follow-ups.
21 */
22
23export interface InvocationUsage {
24 /** Total successful + errored invocations in the window. */
25 readonly count: number;
26 /**
27 * Sum of `durationMs` across all invocations in the window. Stored as
28 * varchar in `function_logs`; cast to int in SQL. Caps at 2^31-1 ms
29 * (~24 days of cumulative compute) — beyond that, swap to bigint.
30 */
31 readonly totalDurationMs: number;
32 /** Echoed back so callers can verify the period they asked for. */
33 readonly periodStart: string;
34 readonly periodEnd: string;
35}
36
37export async function getInvocationUsage(
38 projectId: string,
39 periodStart: Date,
40 periodEnd: Date,
41): Promise<InvocationUsage> {
42 if (periodEnd <= periodStart) {
43 throw new Error('periodEnd must be after periodStart');
44 }
45 const db = getDb();
46 const [row] = await db
47 .select({
48 count: sql<number>`count(*)::int`,
49 totalDurationMs: sql<number>`coalesce(sum(${functionLogs.durationMs}::int), 0)::int`,
50 })
51 .from(functionLogs)
52 .where(
53 and(
54 eq(functionLogs.projectId, projectId),
55 gte(functionLogs.createdAt, periodStart),
56 lt(functionLogs.createdAt, periodEnd),
57 ),
58 );
59 return {
60 count: row?.count ?? 0,
61 totalDurationMs: row?.totalDurationMs ?? 0,
62 periodStart: periodStart.toISOString(),
63 periodEnd: periodEnd.toISOString(),
64 };
65}
66
67/**
68 * UTC calendar-month boundaries for a given reference date. Used by the
69 * dashboard "this month so far" widget — invocation counts compare to
70 * `TIERS[tier].invokesPerMonth`. The boundary is the first millisecond
71 * of the next month, not the last of the current — `[start, end)`
72 * matches the SQL `gte` + `lt` aggregation contract above.
73 */
74export function currentMonthBounds(now: Date = new Date()): {
75 periodStart: Date;
76 periodEnd: Date;
77} {
78 const periodStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1, 0, 0, 0, 0));
79 const periodEnd = new Date(
80 Date.UTC(now.getUTCFullYear(), now.getUTCMonth() + 1, 1, 0, 0, 0, 0),
81 );
82 return { periodStart, periodEnd };
83}
84
85export async function getCurrentMonthInvocationUsage(
86 projectId: string,
87 now: Date = new Date(),
88): Promise<InvocationUsage> {
89 const { periodStart, periodEnd } = currentMonthBounds(now);
90 return getInvocationUsage(projectId, periodStart, periodEnd);
91}
92
93export interface StorageUsage {
94 /**
95 * Total bytes on disk for every relation in the project's schema —
96 * sums table data + indexes + toast via pg_total_relation_size.
97 * Excludes the platform's _briven_* tables so the customer's usage
98 * isn't inflated by our bookkeeping.
99 */
100 readonly bytes: number;
101 /** Number of user tables (excludes _briven_*). */
102 readonly tableCount: number;
103 /** Echoed back so callers know which schema was measured. */
104 readonly schema: string;
105 /** Timestamp at which the size was sampled. */
106 readonly sampledAt: string;
107}
108
109export interface ConnectionSecondsUsage {
110 /**
111 * Sum of realtime connection-seconds across the period. Source is
112 * `usage_events` rows (metric='connection_seconds') populated hourly
113 * by the aggregator's /metrics scrape — durable across api/realtime
114 * restarts, unlike the in-memory `briven_realtime_connection_seconds_total`
115 * gauge.
116 */
117 readonly seconds: number;
118 readonly periodStart: string;
119 readonly periodEnd: string;
120}
121
122/**
123 * Sum connection_seconds rows in [periodStart, periodEnd) for one project.
124 * Returns 0 when the aggregator hasn't yet written a row — the dashboard
125 * renders that as "—" naturally.
126 */
127export async function getConnectionSecondsUsage(
128 projectId: string,
129 periodStart: Date,
130 periodEnd: Date,
131): Promise<ConnectionSecondsUsage> {
132 if (periodEnd <= periodStart) {
133 throw new Error('periodEnd must be after periodStart');
134 }
135 const db = getDb();
136 const [row] = await db
137 .select({
138 seconds: sql<number>`coalesce(sum(${usageEvents.value}::bigint), 0)::bigint`,
139 })
140 .from(usageEvents)
141 .where(
142 and(
143 eq(usageEvents.projectId, projectId),
144 eq(usageEvents.metric, 'connection_seconds'),
145 gte(usageEvents.periodStart, periodStart),
146 lt(usageEvents.periodStart, periodEnd),
147 ),
148 );
149 // drizzle returns bigint as string in some setups — coerce to number.
150 const raw = row?.seconds;
151 const seconds = typeof raw === 'string' ? Number.parseInt(raw, 10) : (raw ?? 0);
152 return {
153 seconds: Number.isFinite(seconds) ? seconds : 0,
154 periodStart: periodStart.toISOString(),
155 periodEnd: periodEnd.toISOString(),
156 };
157}
158
159export async function getCurrentMonthConnectionSecondsUsage(
160 projectId: string,
161 now: Date = new Date(),
162): Promise<ConnectionSecondsUsage> {
163 const { periodStart, periodEnd } = currentMonthBounds(now);
164 return getConnectionSecondsUsage(projectId, periodStart, periodEnd);
165}
166
167/**
168 * Query the data-plane for the storage footprint of a project's schema.
169 * Cheap — single round-trip with a pg_total_relation_size aggregate.
170 * Used by the dashboard usage widget and (Phase 3 follow-up) by the
171 * Polar metering push.
172 *
173 * Returns {bytes:0, tableCount:0} when the schema doesn't exist yet
174 * (project was just created but no deploy has landed) instead of
175 * throwing — the dashboard renders this as "—" naturally.
176 */
177export async function getStorageUsage(projectId: string): Promise<StorageUsage> {
178 // db-per-project (sprint S2.2): the previous code used postgres.js + a
179 // schema-per-project name, which queried the WRONG database and silently
180 // returned 0. Run against the project's OWN DoltGres database (pg, bound to
181 // proj_<id>); its tables live in `public`. `left(...)` instead of LIKE
182 // because DoltGres lacks the bare LIKE escape the old filter relied on.
183 //
184 // KNOWN LIMITATION: DoltGres reports pg_total_relation_size/pg_relation_size
185 // as 0 (no on-disk size accounting yet) and has no information_schema.tables
186 // data_length. So `bytes` is best-effort and will read 0 until DoltGres adds
187 // size reporting. `tableCount` IS accurate. Storage-byte billing must not
188 // depend on `bytes` yet (track via row counts / a future size source). The
189 // dolt-compat alarm probes this so we know when it changes.
190 const schema = dbNameFor(projectId);
191 const sampledAt = new Date().toISOString();
192 try {
193 const rows = (await runInProjectDatabase(projectId, async (tx) =>
194 tx.unsafe(`
195 SELECT
196 COALESCE(SUM(pg_total_relation_size(format('%I.%I', n.nspname, c.relname)::regclass)), 0)::bigint AS bytes,
197 COUNT(*)::bigint AS table_count
198 FROM pg_class c
199 JOIN pg_namespace n ON n.oid = c.relnamespace
200 WHERE n.nspname = 'public'
201 AND c.relkind IN ('r', 'p') -- ordinary + partitioned tables
202 AND left(c.relname, 8) <> '_briven_'
203 `),
204 )) as Array<{ bytes: string; table_count: string }>;
205 const row = rows[0];
206 return {
207 bytes: row ? Number.parseInt(row.bytes, 10) : 0,
208 tableCount: row ? Number.parseInt(row.table_count, 10) : 0,
209 schema,
210 sampledAt,
211 };
212 } catch (err) {
213 log.warn('storage_usage_query_failed', {
214 projectId,
215 schema,
216 message: err instanceof Error ? err.message : String(err),
217 });
218 return { bytes: 0, tableCount: 0, schema, sampledAt };
219 }
220}