The job that checks the meter

messages (through channels)          19,012
usage_periods.messages_sent          18,962
                                     0.2630%

// FR-ANL-06's comparison, as arithmetic over two numbers.
//
// *"Metered totals shall agree with counts derived from operational data to within 0.1%,
// verified by a daily reconciliation job that raises an alert on breach."*
//
// THIS FILE IS THE HALF THAT RUNS WITHOUT A STORE, AND `docs/12` §2.3 IS WHY. The lane's
// largest membership set is five channels, and *"0.1% of a small number is an assertion that
// cannot fail for its own reason"* — so the milestone splits into a planted drift the lane
// checks every run, and the 0.1% figure measured once at a volume where it means something.
// Separating the verdict from the gathering is what lets the first half run with no corpus,
// no database and no broker.
 
/** The threshold FR-ANL-06 names. A constant with its clause beside it, because a bare
 *  `0.001` in an expression is a number somebody later adjusts to make a test pass. */
export const RECONCILE_THRESHOLD = 0.001; // 0.1%, SRS FR-ANL-06
 
export type Verdict = "pass" | "breach" | "not-comparable" | "no-data";
 
export interface Comparison {
  /** Rollup total, or null when the analytical side holds nothing for this tenant-period. */
  analytical: number | null;
  /** Operational total, or null when Postgres holds nothing for it. */
  operational: number | null;
  /** Does this quantity have an operational counterpart AT ALL?
   *
   * DISTINCT FROM `operational === null`, and the difference is the point. A tenant with no
   * `usage_periods` row has a null total and a source that exists; **the stored message count
   * has no operational source anywhere in this platform** and never will until somebody adds
   * one. Collapsing the two would blame the analytical path for a column Postgres has never
   * had. */
  hasOperationalSource: boolean;
}
 
/** The verdict, decided from PRESENCE before any arithmetic runs.
 *
 * That ordering is deliberate: only a comparison with both sides present reaches a division,
 * which is what stops a zero denominator from being a case anyone has to think about. */
export function verdictFor(c: Comparison, threshold = RECONCILE_THRESHOLD): Verdict {
  if (!c.hasOperationalSource) return "not-comparable";
  if (c.analytical === null && c.operational === null) return "no-data";
  // ONE-SIDED IS A BREACH, IN EITHER DIRECTION.
  //
  // Measured at this chapter's opening: 675 environments have operational usage rows and
  // ZERO real ones have analytical rollup data, so every tenant in this platform is in the
  // first state. Calling that "missing data" would let the defect this movement exists to
  // expose read as an absence of evidence.
  //
  // And the other direction is not hypothetical either: the analytical store held four
  // environment ids that exist in no Postgres row, because it is fed by a stream and nothing
  // carries a foreign key across the boundary.
  if (c.analytical === null || c.operational === null) return "breach";
  const pct = differencePct(c);
  return pct !== null && pct <= threshold ? "pass" : "breach";
}
 
/** The difference as a fraction, or null when either side is absent.
 *
 * NULL RATHER THAN ZERO WHEN A SIDE IS MISSING. A number computed against an absent
 * counterpart reads as a measurement and is not one.
 *
 * `max(a, o)` AND NOT THE OPERATIONAL SIDE as the denominator: a tenant can hold analytical
 * rows for a quantity whose operational total is zero, and dividing by that is a crash where
 * a verdict belongs.
 *
 * AND BOTH-ZERO IS AGREEMENT, NOT A DIVISION. Two present sides that both read zero make the
 * denominator zero too. This case is reachable — `usage_periods` holds 288 rows for 2026-08
 * with `messages_sent = 0` — and it was not in the design: `data-model.md` specified the
 * formula and the verdict ordering and never said what `0 / 0` means. It means they agree. */
export function differencePct(c: Comparison): number | null {
  if (c.analytical === null || c.operational === null) return null;
  const denominator = Math.max(c.analytical, c.operational);
  if (denominator === 0) return 0;
  return Math.abs(c.analytical - c.operational) / denominator;
}
 
// ---------------------------------------------------------------------------
// THE GATHERING (chapter 4.7, phase 3).
//
// Everything above is arithmetic and runs with no store. Everything below reads both of
// them, which is the thing FR-ANL-06 asks for and constitution III's first sentence appears
// to forbid — see the chapter, and `gaps.md`.
// ---------------------------------------------------------------------------
 
import type { Db } from "../db/client";
import { operationalUsageFor } from "../db/usage-reads";
import { nextPeriod } from "../quotas/period";
import type { AnalyticalStore } from "./clickhouse";
 
/** The four quantities FR-ANL-05 names. */
export type Quantity =
  | "messages"
  | "activeUsers"
  | "connectionMinutes"
  | "storedMessages";
 
export interface ReconcileRow {
  environmentId: string;
  period: string;
  quantity: Quantity;
  analytical: number | null;
  operational: number | null;
  /** WHICH TABLE, NAMED IN THE REPORT. Two operational candidates exist for messages and the
   *  clause chooses neither, so a report that does not say which one it read is asserting the
   *  other does not exist. */
  operationalSource: string | null;
  differencePct: number | null;
  verdict: Verdict;
}
 
const DB_ANALYTICS = "relay_analytics";
 
/** FR-ANL-06's comparison, for ONE tenant and ONE period.
 *
 * ONE TENANT PER CALL, AND THAT IS A CONSTRAINT RATHER THAN A CONVENIENCE. Measured at this
 * chapter's opening: aggregated across tenants the two operational counters differ by
 * 0.2694% — a number nobody would question — while 19 tenants breach and one is wrong by
 * everything it has. A sweep is a loop in the caller, and the caller is where a summary
 * belongs.
 *
 * IT WRITES NOTHING. Two invocations with the same arguments return the same report. */
export async function reconcile(
  db: Db,
  store: AnalyticalStore,
  { environmentId, period }: { environmentId: string; period: string },
): Promise<ReconcileRow[]> {
  // THE DAY RANGE IS HALF-OPEN, and the other spelling is wrong by one day. The caller passes
  // a month — `periodOf`'s `YYYY-MM-01` — and the rollup is keyed by day, so
  // `day >= period AND day < nextPeriod(period)`. `BETWEEN period AND nextPeriod(period)`
  // puts 1 September into August, and **a reconciler's off-by-one does not crash: it reports
  // drift.**
  const until = nextPeriod(period);
 
  // `count()` FIRST, AND IT IS NOT DECORATION. **A bare aggregate with no GROUP BY always
  // returns exactly one row** — chapter 4.6 established that against the server and used it
  // to delete a guard, and here the same fact means an empty result set is not reachable: a
  // tenant with no rollup rows comes back as `0`, not as nothing. Without the count, "holds
  // nothing" and "holds zero" are the same answer, and this report's whole point is that they
  // are not.
  const rollup = await store.query(
    `SELECT count(), sum(messages), uniqMerge(active_users_state), sum(connection_minutes)
       FROM ${DB_ANALYTICS}.daily_usage_billing
      WHERE environment_id = toUUID('${environmentId}')
        AND day >= toDate('${period}') AND day < toDate('${until}')
      FORMAT TSV`,
  );
  // THE STORED COUNT IS A BALANCE, so it sums every delta up to the period's end rather than
  // within it. A `BETWEEN` here reports the period's CHANGE in stored messages, which is a
  // different question that reads as a plausible wrong answer.
  const stored = await store.query(
    `SELECT count(), sum(stored_delta) FROM ${DB_ANALYTICS}.daily_usage_billing
      WHERE environment_id = toUUID('${environmentId}') AND day < toDate('${until}')
      FORMAT TSV`,
  );
 
  // THE OPERATIONAL READ GOES THROUGH `db/usage-reads`, NOT THROUGH SQL HERE.
  // `eslint.config.mjs` restricts `drizzle-orm` to `services/api/src/db/**` — "the query
  // engine lives inside the repository layer only (constitution I, ADR-16)" — and the first
  // version of this file failed lint on exactly that import.
  const op = await operationalUsageFor(db, environmentId, period);
 
  const rollupRow = rollup[0];
 
  /** An analytical cell, absent when the tenant-period has no rollup rows at all.
   *
   *  THE COUNT DECIDES, NOT THE SUM. A first version read `rollup[0] === undefined` and never
   *  fired: the server answers a bare aggregate with one row whatever the filter matches, so
   *  every empty tenant reported `0` and every `no-data` verdict came back `breach`. */
  const rows0 = rollupRow === undefined ? 0 : Number(rollupRow[0]);
  const analytical = (i: number): number | null =>
    rows0 === 0 || rollupRow === undefined ? null : Number(rollupRow[i + 1]);
 
  const rows: Array<[Quantity, number | null, number | null, string | null, boolean]> = [
    ["messages", analytical(0), op.messagesSent, "usage_periods", true],
    // `activeUsers` is null when the tenant has no period row at all, and 0 when it has one
    // with no users — the same distinction the rest of the report keeps.
    [
      "activeUsers",
      analytical(1),
      op.messagesSent === null ? null : op.activeUsers,
      "usage_active_users",
      true,
    ],
    ["connectionMinutes", analytical(2), op.connectionMinutes, "usage_periods", true],
    // NO OPERATIONAL SOURCE ANYWHERE, and that is a fact about the platform rather than about
    // this tenant. `usage_periods` carries messages and connection-minutes,
    // `usage_active_users` carries the third, and nothing carries a stored total.
    [
      "storedMessages",
      stored[0] === undefined || Number(stored[0][0]) === 0 ? null : Number(stored[0][1]),
      null,
      null,
      false,
    ],
  ];
 
  return rows.map(([quantity, a, o, source, hasSource]) => {
    const c: Comparison = { analytical: a, operational: o, hasOperationalSource: hasSource };
    return {
      environmentId,
      period,
      quantity,
      analytical: a,
      operational: o,
      operationalSource: source,
      differencePct: differencePct(c),
      verdict: verdictFor(c),
    };
  });
}
 
/** FR-ANL-06's *"raises an alert"*, as the only thing this platform can currently mean by it.
 *
 * THE LINE THAT DECIDES WHETHER THE ALERT FIRES LIVED IN A FILE NOTHING TESTS.
 * `scripts/reconcile-usage.mjs` closed with `process.exit(breached.length > 0 ? 1 : 0)` — one
 * expression, no test, and the whole of FR-008's observable behaviour. Moved here so a test can
 * ask it directly instead of a human reading output.
 *
 * `not-comparable` and `no-data` DO NOT RAISE. A quantity with no operational counterpart is a
 * gap in the platform and a tenant with nothing on either side is a tenant with nothing; a job
 * that exits 1 for either would exit 1 every day, and a check that always fires stops being
 * read. The gap is the chapter's subject and not this exit code's. */
export function exitCodeFor(rows: readonly ReconcileRow[]): 0 | 1 {
  return rows.some((r) => r.verdict === "breach") ? 1 : 0;
}

'drizzle-orm' import is restricted from being used. The query engine lives inside the
repository layer only (constitution I, ADR-16)

import { sql } from "drizzle-orm";
 
import type { Db } from "./client";
 
// THE OPERATIONAL HALF OF FR-ANL-06's COMPARISON, AND IT LIVES HERE BECAUSE OF A LINT RULE
// THAT IS A CONSTITUTION CLAUSE.
//
// `eslint.config.mjs` restricts `drizzle-orm` to `services/api/src/db/**` — *"the query engine
// lives inside the repository layer only (constitution I, ADR-16)"*. Chapter 4.7's reconciler
// was written in `services/api/src/metering/` with its Postgres read inline and failed lint on
// the import, which is the rule doing exactly its job: the plan had placed the job in the api
// *because* the api owns the repository, and never noticed the wall between them.
//
// A NEW FILE RATHER THAN A METHOD ON `Repository`. That class is tenant-scoped around
// `this.environmentId` and this read takes the tenant as an argument; and `repository.ts`
// carries 24 titled fences in English and 23 in Vietnamese, both already stale, so every edit
// to it adds to a debt no gate reports. This file carries none.
//
// READ-ONLY. The reconciler compares and writes nothing.
 
export interface OperationalUsage {
  /** Absent when the tenant has no `usage_periods` row for the period at all — which is not
   *  the same as a row holding zero, and the reconciler reports the two differently. */
  messagesSent: number | null;
  connectionMinutes: number | null;
  /** A COUNT OF ROWS, not a stored total: `usage_active_users` is
   *  `(environment_id, period, user_id, first_seen_at)`, one row per user per period. So this
   *  is the only quantity whose comparison puts an EXACT count against `uniqMerge`'s
   *  approximate sketch, which is where 047-1's 0.51% lives. */
  activeUsers: number;
}
 
export async function operationalUsageFor(
  db: Db,
  environmentId: string,
  period: string,
): Promise<OperationalUsage> {
  // BOTH STATEMENTS NAME THE ENVIRONMENT. `usage_periods` and `usage_active_users` both carry
  // feature 030's sentinel guard, and the api's integration lane sets
  // `RELAY_HARNESS_BAIT: "on"` — unlike the gateway's, which carries none. A scoped statement
  // never reaches a sentinel row and never raises; an unscoped one does both.
  const periods = await db.execute(sql`
    select messages_sent, connection_minutes
      from usage_periods
     where environment_id = ${environmentId}::uuid and period = ${period}::date`);
  const active = await db.execute(sql`
    select count(*)::int as n
      from usage_active_users
     where environment_id = ${environmentId}::uuid and period = ${period}::date`);
 
  const row = periods.rows[0] as
    | { messages_sent: string | number; connection_minutes: string | number }
    | undefined;
  return {
    messagesSent: row === undefined ? null : Number(row.messages_sent),
    connectionMinutes: row === undefined ? null : Number(row.connection_minutes),
    activeUsers: Number((active.rows[0] as { n: number } | undefined)?.n ?? 0),
  };
}

expected 'breach' to be 'no-data'
expected +0 to be null

SELECT 1 WHERE 0        ->  []          an empty result set
SELECT sum(1) WHERE 0   ->  [["0"]]     one row, always

#!/usr/bin/env node
// FR-ANL-06's daily job, as a thin caller. The comparison lives in
// `services/api/src/metering/reconcile.ts`; this constructs the two handles and prints.
//
// ONE TENANT PER INVOCATION, because the function takes one. A sweep is a loop here, and it
// would have to know two things a single call cannot meet: the analytical store holds
// environment ids that exist in no Postgres row (four today, all test fixtures, because a
// stream carries no foreign key), and `usage_periods` holds a `1999-01-01` period belonging
// to `__sentinel__` applications planted by the lane's own guard.
//
// EXIT NON-ZERO ON ANY BREACH. That is the whole of what "raises an alert" can mean here:
// this platform has no alerting integration, and the one notification path that exists is
// `quotas/quota-email.ts`, whose failure mode is already visible in the lane as
// `quotas.unaddressable: no member has an email address`. A notification with no recipient is
// not an alert, and an exit code is not one either — the chapter says what a real one costs.
import { createDb, createPool } from "../services/api/dist/db/client.js";
import { createAnalyticalStore } from "../services/api/dist/metering/clickhouse.js";
import { exitCodeFor, reconcile } from "../services/api/dist/metering/reconcile.js";
 
function arg(name) {
  const i = process.argv.indexOf(`--${name}`);
  if (i === -1 || !process.argv[i + 1]) {
    throw new Error(`--${name} is required`);
  }
  return process.argv[i + 1];
}
 
const environmentId = arg("environment");
const period = arg("period");
 
const db = createDb(createPool());
const store = createAnalyticalStore();
 
const rows = await reconcile(db, store, { environmentId, period });
 
for (const r of rows) {
  const pct = r.differencePct === null ? "     —" : `${(r.differencePct * 100).toFixed(4)}%`;
  const a = r.analytical === null ? "absent" : String(r.analytical);
  const o = r.operational === null ? "absent" : String(r.operational);
  console.log(
    `${r.quantity.padEnd(18)} analytical ${a.padStart(10)}  operational ${o.padStart(10)}` +
      `  ${pct.padStart(9)}  ${r.verdict}` +
      (r.operationalSource ? `  (${r.operationalSource})` : ""),
  );
}
 
const breached = rows.filter((r) => r.verdict === "breach");
if (breached.length > 0) {
  console.error(
    `reconcile: ${breached.length} breach(es) for ${environmentId} in ${period}: ` +
      breached.map((r) => r.quantity).join(", "),
  );
}
await db.$client.end?.();
// THE VERDICT-TO-EXIT-CODE RULE IS NOT DECIDED HERE. It was, in one expression this lane never
// ran; `exitCodeFor` is the same rule where a test can reach it.
process.exit(exitCodeFor(rows));

analytical  99,000 vs operational 100,000   connectionMinutes  1.0000%  breach   exit 1
analytical 100,000 vs operational 100,000   connectionMinutes  0.0000%  pass     exit 0

tenant volume   smallest breaching shortfall    that drift, as a %
            9                              1            11.111111%
          100                              1             1.000000%
        1,000                              2             0.200000%
       10,000                             11             0.110000%
      100,000                            101             0.101000%

cardinality       uniq    uniqExact      error
     65,000      65,000      65,000     0.0000%
     65,536      65,536      65,536     0.0000%
     65,537      65,909      65,537     0.5676%
     70,000      70,449      70,000     0.6414%

@@ -144,12 +144,21 @@ services:
       # Development values. Both are secrets in anything that is not a laptop,
       # and the api refuses to start in production without the first.
       RELAY_WEBHOOK_SECRET_KEY: ${RELAY_WEBHOOK_SECRET_KEY:-}
       RELAY_INTERNAL_CREDENTIAL: ${RELAY_INTERNAL_CREDENTIAL:-rk_svc_local_development_credential_0000}
       # The api verifies both service credentials, so it holds both.
       RELAY_INTERNAL_CREDENTIAL_GATEWAY: ${RELAY_INTERNAL_CREDENTIAL_GATEWAY:-rk_svc_local_development_gateway_00000}
+      # Chapter 4.7. THE FIRST OPERATIONAL SERVICE TO READ THE ANALYTICAL STORE, and that
+      # is the subject rather than the plumbing: constitution III says billing, metering and
+      # dashboard analytics read only from ClickHouse, and FR-ANL-06 requires comparing them
+      # against Postgres. The reconciler is none of those three roles — it audits the
+      # boundary, and an auditor confined to one side of a fence cannot check the fence.
+      RELAY_CLICKHOUSE_HOST: clickhouse
+      RELAY_CLICKHOUSE_HTTP_PORT: "8123"
+      RELAY_CLICKHOUSE_USER: relay
+      RELAY_CLICKHOUSE_PASSWORD: relay
       PORT: "4000"
     ports:
       - "${RELAY_API_PORT:-4000}:4000"
     depends_on:
       postgres: { condition: service_healthy }
       nats: { condition: service_healthy }

exitCodeFor([... "pass", "breach" ...])                 -> 1
exitCodeFor([... "not-comparable", "no-data" ...])      -> 0