SMS Overage Billing Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task. Use superpowers:test-driven-development for every task. Use the error-handling skill (no native try/catch — use @power-rent/try-catch/nextjs). For the UI tasks use the react-relay skill.

Goal: Let internal staff see and collect what each rental company owes for SMS sent above its plan quota, computed forward-only from real Twilio cost + 50%.

Architecture: A new Neon Postgres table sms_overage (data access SmsOverageRepository extends BaseService, business logic SmsOverageService). Overage is computed live on staff demand from billable_events via the root/sudo client (cross-tenant); persisted only when staff Bill/Waive/Void a closed month. Twilio cost gaps are reconciled at Bill time. A root-admin-gated GraphQL surface backs a staff dashboard.

Tech Stack: TypeScript, Prisma 7 + Neon Postgres, Vitest, GraphQL (SDL + graphql-codegen), Relay, Luxon, @power-rent/try-catch/nextjs.

Branch: feat/sms-overage-billing-design (already checked out). Spec: docs/superpowers/specs/2026-06-17-sms-overage-billing-design.md.

Conventions used throughout:

Run one test file: pnpm exec vitest run <path> (add -t "<name>" for a single test).
Typecheck: pnpm typecheck. Lint a file: pnpm exec eslint <path>.
Tests are co-located *.test.ts next to the unit.
Money math uses Prisma.Decimal. Never Number() on costs.
Errors: wrap fallible async in new Try(...) from @power-rent/try-catch/nextjs; log with console.error + Sentry; never swallow.
sms_overage is cross-tenant → BaseService, never TenantBaseService.

Task 1: `sms_overage` Neon table + `billable_events` index

Files:

Modify: packages/prisma-client/prisma/schema.prisma
Create (generated): packages/prisma-client/prisma/migrations/<timestamp>_add_sms_overage/migration.sql

Step 1: Add the model + enum to schema.prisma

Add near the other root models:

enum SmsOverageStatus {
  unpaid
  paid
  waived
  voided
}

model sms_overage {
  id            String           @id @default(dbgenerated("snowflake_id()"))
  period        String
  companyId     String
  tenantId      String
  smsSent       Int
  quota         Int
  overageCount  Int
  realCostSum   Decimal          @db.Decimal(10, 4)
  fallbackCount Int
  markup        Decimal          @db.Decimal(10, 4)
  fallbackFlat  Decimal          @db.Decimal(10, 4)
  amountDue     Decimal          @db.Decimal(10, 4)
  currency      String           @default("USD")
  windowFrom    DateTime         @db.Timestamptz(6)
  windowTo      DateTime         @db.Timestamptz(6)
  messageBasis  Json
  status        SmsOverageStatus
  raisedBy      String
  raisedAt      DateTime         @db.Timestamptz(6)
  paidBy        String?
  paidAt        DateTime?        @db.Timestamptz(6)
  paymentRef    String?
  waivedBy      String?
  waivedAt      DateTime?        @db.Timestamptz(6)
  waiveReason   String?
  voidedBy      String?
  voidedAt      DateTime?        @db.Timestamptz(6)
  voidReason    String?
  createdAt     DateTime         @default(now()) @db.Timestamptz(6)
  updatedAt     DateTime         @updatedAt @db.Timestamptz(6)

  @@unique([companyId, period])
  @@index([tenantId])
}

Add the cross-tenant scan index to the existing billable_events model:

  @@index([event, date, companyId])

Step 2: Validate the schema

Run: pnpm --filter @power-rent/prisma-client run prisma:validate Expected: “The schema at prisma/schema.prisma is valid 🚀”

Step 3: Create + apply the migration

Run: pnpm --filter @power-rent/prisma-client exec prisma migrate dev --name add_sms_overage Expected: a new timestamped folder under prisma/migrations/ and “Your database is now in sync”.

If migrate dev errors on a shadow database (Neon), fall back to prisma migrate dev --create-only --name add_sms_overage then pnpm --filter @power-rent/prisma-client exec prisma migrate deploy. See memory project_prisma_migrations. Identifiers are camelCase — confirm the generated SQL quotes them (memory feedback_postgres_quoting).

Step 4: Regenerate the Prisma client

Run: pnpm --filter @power-rent/prisma-client run prisma:generate Then: pnpm --filter @power-rent/prisma-client run prisma:build Expected: no errors; sms_overage type now exported from @power-rent/prisma-client.

Step 5: Commit

git add packages/prisma-client/prisma/schema.prisma packages/prisma-client/prisma/migrations
git commit -m "feat(sms-overage): add sms_overage Neon table + billable_events index"

Task 2: Fix quota-count column to `date` (M7)

Quota count and overage selection must read the same column. Both getSmsSentByTenantId methods currently filter createdAt; switch to date.

Files:

Modify: services/global/RootPlanService.ts (method getSmsSentByTenantId)
Modify: services/global/PlanService.ts (method getSmsSentByTenantId)
Test: services/global/RootPlanService.test.ts (create)

Step 1: Write the failing test

import { describe, it, expect, vi, beforeEach } from 'vitest';
import { RootPlanService } from './RootPlanService';

describe('RootPlanService.getSmsSentByTenantId', () => {
  it('counts billable sms events by `date`, not `createdAt`', async () => {
    const count = vi.fn().mockResolvedValue(7);
    const svc = new RootPlanService();
    // @ts-expect-error test override of the prisma delegate
    svc.prisma = { billable_events: { count } };

    const from = new Date('2026-07-01T00:00:00Z');
    const to = new Date('2026-07-31T23:59:59Z');
    const result = await svc.getSmsSentByTenantId({ tenantId: 'c1', startOfMonth: from, endOfMonth: to });

    expect(result).toBe(7);
    expect(count).toHaveBeenCalledWith({
      where: { tenantId: 'c1', event: 'sms', date: { gte: from, lte: to } },
    });
  });
});

Step 2: Run test to verify it fails

Run: pnpm exec vitest run services/global/RootPlanService.test.ts Expected: FAIL — count called with createdAt, not date.

Step 3: Implement — rename createdAt → date in both files

In RootPlanService.ts and PlanService.ts, inside getSmsSentByTenantId, change the where key createdAt to date (keep the gte/lte).

Step 4: Run test to verify it passes

Run: pnpm exec vitest run services/global/RootPlanService.test.ts Expected: PASS.

Step 5: Commit

git add services/global/RootPlanService.ts services/global/PlanService.ts services/global/RootPlanService.test.ts
git commit -m "fix(sms-overage): count monthly SMS by date column, not createdAt (M7)"

Task 3: Constants

Files:

Create: constants/smsOverage.ts
Modify: constants/index.* (re-export — match the existing barrel; check constants/index.ts/.js)

Step 1: Create the constants

export const SMS_MARKUP = 1.5;            // +50% on actual Twilio cost
export const FALLBACK_FLAT_USD = 0.5;     // flat charge for an overage SMS with no recoverable cost
export const SMS_OVERAGE_GO_LIVE = '2026-07'; // YYYY-MM, Europe/Rome; SMS before this are never billed
export const SMS_OVERAGE_TZ = 'Europe/Rome';
export const SMS_RECONCILE_MAX_CALLS = 200;   // cap Twilio price lookups per Bill
export const SMS_RECONCILE_CONCURRENCY = 5;

Step 2: Re-export from the barrel

Open constants/index.*, add export * from './smsOverage'; following the file’s existing style.

Step 3: Typecheck

Run: pnpm typecheck Expected: no new errors.

Step 4: Commit

git add constants/smsOverage.ts constants/index.*
git commit -m "feat(sms-overage): add markup, fallback, go-live and reconcile constants"

Task 4: Period bounds helper (Europe/Rome) — M6

A pure function turning 'YYYY-MM' into UTC [from, to) bounds in Europe/Rome, plus validation and a “closed month” check.

Files:

Create: services/global/smsOverage/period.ts
Test: services/global/smsOverage/period.test.ts

Step 1: Write the failing test

import { describe, it, expect } from 'vitest';
import { parsePeriod, periodBounds, isValidPeriod, isClosedPeriod } from './period';

describe('smsOverage/period', () => {
  it('rejects malformed periods', () => {
    expect(isValidPeriod('2026-13')).toBe(false);
    expect(isValidPeriod('26-07')).toBe(false);
    expect(isValidPeriod('2026-07')).toBe(true);
  });

  it('computes Europe/Rome month bounds as UTC', () => {
    const { from, to } = periodBounds('2026-07');
    // July 2026 in Rome is +02:00 → 1 Jul 00:00 local == 30 Jun 22:00 UTC
    expect(from.toISOString()).toBe('2026-06-30T22:00:00.000Z');
    expect(to.toISOString()).toBe('2026-07-31T22:00:00.000Z');
  });

  it('treats a Rome-midnight message as belonging to the new month', () => {
    const { from } = periodBounds('2026-07');
    const romeMidnight = new Date('2026-07-01T00:30:00+02:00'); // 2026-06-30T22:30Z
    expect(romeMidnight >= from).toBe(true); // counted in July, not June
  });

  it('detects closed vs open months relative to a reference now', () => {
    const now = new Date('2026-08-10T12:00:00Z');
    expect(isClosedPeriod('2026-07', now)).toBe(true);
    expect(isClosedPeriod('2026-08', now)).toBe(false);
    expect(isClosedPeriod('2026-09', now)).toBe(false);
  });
});

Step 2: Run test to verify it fails

Run: pnpm exec vitest run services/global/smsOverage/period.test.ts Expected: FAIL — module not found.

Step 3: Implement

import { DateTime } from 'luxon';
import { SMS_OVERAGE_TZ } from '@power-rent/constants';

const PERIOD_RE = /^\d{4}-(0[1-9]|1[0-2])$/;

export function isValidPeriod(period: string): boolean {
  return PERIOD_RE.test(period);
}

export function parsePeriod(period: string): { year: number; month: number } {
  if (!isValidPeriod(period)) throw new Error(`Invalid period: ${period}`);
  const [year, month] = period.split('-').map(Number);
  return { year, month };
}

export function periodBounds(period: string): { from: Date; to: Date } {
  const { year, month } = parsePeriod(period);
  const start = DateTime.fromObject({ year, month, day: 1 }, { zone: SMS_OVERAGE_TZ }).startOf('month');
  const end = start.plus({ months: 1 });
  return { from: start.toUTC().toJSDate(), to: end.toUTC().toJSDate() };
}

// A period is closed once the current Rome month is strictly after it.
export function isClosedPeriod(period: string, now: Date = new Date()): boolean {
  const { year, month } = parsePeriod(period);
  const periodStart = DateTime.fromObject({ year, month, day: 1 }, { zone: SMS_OVERAGE_TZ }).startOf('month');
  const currentStart = DateTime.fromJSDate(now).setZone(SMS_OVERAGE_TZ).startOf('month');
  return periodStart < currentStart;
}

Step 4: Run test to verify it passes

Run: pnpm exec vitest run services/global/smsOverage/period.test.ts Expected: PASS.

Step 5: Commit

git add services/global/smsOverage/period.ts services/global/smsOverage/period.test.ts
git commit -m "feat(sms-overage): Europe/Rome period bounds + closed-month helper (M6)"

Task 5: `quotaFor` + billing-start resolution (M1, billing window)

Decides whether a company is billable for a period and the included quota.

Files:

Create: services/global/smsOverage/quota.ts
Test: services/global/smsOverage/quota.test.ts

Verify the plan shape first: read services/global/shared/getPlanFeatures usage in server/graphql/resolvers/shared/plan.js and constants/features.js so the SMS-feature check matches. The function below takes an already-loaded plan object so it stays pure and testable.

Step 1: Write the failing test

import { describe, it, expect } from 'vitest';
import { quotaFor } from './quota';

const base = { id: 'p1', startMonth: '2026-07', features: { sms: true }, limits: { sms: 500 } };

describe('quotaFor', () => {
  it('returns the plan included quota for a paid SMS plan', () => {
    expect(quotaFor(base as any)).toEqual({ billable: true, quota: 500 });
  });
  it('treats quota 0 as all-overage (billable)', () => {
    expect(quotaFor({ ...base, limits: { sms: 0 } } as any)).toEqual({ billable: true, quota: 0 });
  });
  it('excludes no/default plan', () => {
    expect(quotaFor(null as any).billable).toBe(false);
    expect(quotaFor({ ...base, id: 'default' } as any).billable).toBe(false);
  });
  it('excludes when SMS feature disabled', () => {
    expect(quotaFor({ ...base, features: { sms: false } } as any).billable).toBe(false);
  });
  it('excludes + flags when SMS enabled but quota missing', () => {
    const res = quotaFor({ ...base, limits: {} } as any);
    expect(res).toEqual({ billable: false, reason: 'missing-quota' });
  });
});

Step 2: Run — verify it fails

Run: pnpm exec vitest run services/global/smsOverage/quota.test.ts Expected: FAIL — module not found.

Step 3: Implement

import * as Sentry from '@sentry/nextjs';

type PlanLike = {
  id: string;
  features?: { sms?: boolean } | null;
  limits?: { sms?: number | null } | null;
} | null;

export type QuotaResult =
  | { billable: true; quota: number }
  | { billable: false; reason?: 'missing-quota' };

export function quotaFor(plan: PlanLike): QuotaResult {
  if (!plan || plan.id === 'default') return { billable: false };
  if (!plan.features?.sms) return { billable: false };

  const sms = plan.limits?.sms;
  if (sms == null) {
    Sentry.captureException(new Error(`SMS-enabled plan ${plan.id} has no limits.sms`));
    return { billable: false, reason: 'missing-quota' };
  }
  return { billable: true, quota: sms };
}

Step 4: Run — verify it passes

Run: pnpm exec vitest run services/global/smsOverage/quota.test.ts Expected: PASS.

Step 5: Commit

git add services/global/smsOverage/quota.ts services/global/smsOverage/quota.test.ts
git commit -m "feat(sms-overage): quotaFor billable/quota resolution (M1)"

Task 6: Overage charge math (per-message floor) — M9, S10

Pure function: given ordered overage messages, compute the charge. Heaviest money tests live here.

Files:

Create: services/global/smsOverage/charge.ts
Test: services/global/smsOverage/charge.test.ts

Step 1: Write the failing test

import { describe, it, expect } from 'vitest';
import { Prisma } from '@power-rent/prisma-client';
import { computeCharge } from './charge';

const D = (n: string) => new Prisma.Decimal(n);

describe('computeCharge', () => {
  it('floors cost*1.5 per real message; flat 0.50 for no-cost', () => {
    const res = computeCharge([
      { messageId: 'a', cost: D('0.35') },
      { messageId: 'b', cost: D('0.35') },
      { messageId: 'c', cost: D('0.35') },
      { messageId: 'd', cost: null },
    ]);
    expect(res.amountDue.toString()).toBe('2.06'); // 3*0.52 + 0.50
    expect(res.fallbackCount).toBe(1);
    expect(res.realCostSum.toString()).toBe('1.05');
    expect(res.overageCount).toBe(4);
    expect(res.messageBasis).toEqual([
      { messageId: 'a', cost: '0.35', charge: '0.52', usedFallback: false },
      { messageId: 'b', cost: '0.35', charge: '0.52', usedFallback: false },
      { messageId: 'c', cost: '0.35', charge: '0.52', usedFallback: false },
      { messageId: 'd', cost: '0', charge: '0.50', usedFallback: true },
    ]);
  });

  it('a single 35c overage SMS = 0.52 (floored from 0.525)', () => {
    expect(computeCharge([{ messageId: 'x', cost: D('0.35') }]).amountDue.toString()).toBe('0.52');
  });

  it('treats cost 0 as fallback', () => {
    expect(computeCharge([{ messageId: 'x', cost: D('0') }]).amountDue.toString()).toBe('0.50');
  });

  it('empty overage = 0', () => {
    expect(computeCharge([]).amountDue.toString()).toBe('0');
  });
});

Step 2: Run — verify it fails

Run: pnpm exec vitest run services/global/smsOverage/charge.test.ts Expected: FAIL — module not found.

Step 3: Implement

import { Prisma } from '@power-rent/prisma-client';
import { SMS_MARKUP, FALLBACK_FLAT_USD } from '@power-rent/constants';

export type OverageMessage = { messageId: string; cost: Prisma.Decimal | null };

export type MessageBasis = { messageId: string; cost: string; charge: string; usedFallback: boolean };

export type ChargeResult = {
  overageCount: number;
  realCostSum: Prisma.Decimal;
  fallbackCount: number;
  amountDue: Prisma.Decimal;
  messageBasis: MessageBasis[];
};

const MARKUP = new Prisma.Decimal(SMS_MARKUP);
const FLAT = new Prisma.Decimal(FALLBACK_FLAT_USD);

function isReal(cost: Prisma.Decimal | null): cost is Prisma.Decimal {
  return cost != null && cost.greaterThan(0);
}

export function computeCharge(messages: OverageMessage[]): ChargeResult {
  let realCostSum = new Prisma.Decimal(0);
  let fallbackCount = 0;
  let amountDue = new Prisma.Decimal(0);
  const messageBasis: MessageBasis[] = [];

  for (const m of messages) {
    if (isReal(m.cost)) {
      const charge = m.cost.times(MARKUP).toDecimalPlaces(2, Prisma.Decimal.ROUND_DOWN);
      realCostSum = realCostSum.plus(m.cost);
      amountDue = amountDue.plus(charge);
      messageBasis.push({ messageId: m.messageId, cost: m.cost.toString(), charge: charge.toString(), usedFallback: false });
    } else {
      fallbackCount += 1;
      amountDue = amountDue.plus(FLAT);
      messageBasis.push({ messageId: m.messageId, cost: '0', charge: FLAT.toFixed(2), usedFallback: true });
    }
  }

  return { overageCount: messages.length, realCostSum, fallbackCount, amountDue, messageBasis };
}

Step 4: Run — verify it passes

Run: pnpm exec vitest run services/global/smsOverage/charge.test.ts Expected: PASS (incl. the $2.06 and $0.52 assertions).

Step 5: Commit

git add services/global/smsOverage/charge.ts services/global/smsOverage/charge.test.ts
git commit -m "feat(sms-overage): per-message floored charge math, x1.5 + 0.50 flat (M9)"

Task 7: `SmsOverageRepository`

Data access for the new table — extends BaseService<'sms_overage'> (root/cross-tenant, like TenantsRepository).

Files:

Create: repositories/sms-overage/SmsOverageRepository.ts
Test: repositories/sms-overage/SmsOverageRepository.test.ts

Step 1: Write the failing test

import { describe, it, expect, vi } from 'vitest';
import { SmsOverageRepository } from './SmsOverageRepository';

describe('SmsOverageRepository', () => {
  it('finds a row by company + period', async () => {
    const findUnique = vi.fn().mockResolvedValue({ id: '1' });
    const repo = new SmsOverageRepository();
    // @ts-expect-error inject delegate
    repo.model = { findUnique };
    const row = await repo.findByCompanyPeriod('c1', '2026-07');
    expect(row).toEqual({ id: '1' });
    expect(findUnique).toHaveBeenCalledWith({ where: { companyId_period: { companyId: 'c1', period: '2026-07' } } });
  });
});

Step 2: Run — verify it fails

Run: pnpm exec vitest run repositories/sms-overage/SmsOverageRepository.test.ts Expected: FAIL — module not found.

Step 3: Implement

import prisma, { sms_overage } from '@power-rent/prisma-client';
import { BaseService } from '@power-rent/services/base/BaseService';

/** Data access for the root `sms_overage` table (cross-tenant; BaseService, not TenantBaseService). */
export class SmsOverageRepository extends BaseService<'sms_overage'> {
  constructor() {
    super(prisma, 'sms_overage');
  }

  async findByCompanyPeriod(companyId: string, period: string): Promise<sms_overage | null> {
    return this.model.findUnique({ where: { companyId_period: { companyId, period } } });
  }
}

Step 4: Run — verify it passes

Run: pnpm exec vitest run repositories/sms-overage/SmsOverageRepository.test.ts Expected: PASS.

If the generated unique accessor is not companyId_period, copy the exact name Prisma generated for @@unique([companyId, period]) from the generated client and update the test + code together.

Step 5: Commit

git add repositories/sms-overage/SmsOverageRepository.ts repositories/sms-overage/SmsOverageRepository.test.ts
git commit -m "feat(sms-overage): SmsOverageRepository (BaseService root table)"

Task 8: `SmsOverageService` — live worklist (expected payments)

Cross-tenant aggregate → per over-quota company → preview charge (fallback $0.50 for cost 0/null, no Twilio call on read).

Files:

Create: services/global/SmsOverageService.ts
Test: services/global/SmsOverageService.test.ts
Modify: services/index.ts (register smsOverageService)

Step 1: Write the failing test — worklist excludes under-quota and already-handled periods, applies billing-start floor, uses fallback for cost 0/null without calling Twilio.

import { describe, it, expect, vi } from 'vitest';
import { Prisma } from '@power-rent/prisma-client';
import { SmsOverageService } from './SmsOverageService';

const D = (n: string) => new Prisma.Decimal(n);

function makeService(overrides: Partial<any> = {}) {
  const deps = {
    billableEvents: { groupBy: vi.fn(), findMany: vi.fn() },
    plans: { planByCompanyId: vi.fn() },
    repo: { findManyByPeriod: vi.fn().mockResolvedValue([]) },
    twilio: { getSmsPrice: vi.fn() },
    ...overrides,
  };
  return { svc: new SmsOverageService(deps as any), deps };
}

describe('SmsOverageService.worklist', () => {
  it('returns expected payment for an over-quota company, fallback for cost 0, no Twilio call', async () => {
    const { svc, deps } = makeService();
    deps.billableEvents.groupBy.mockResolvedValue([{ companyId: 'c1', _count: { _all: 3 } }]);
    deps.plans.planByCompanyId.mockResolvedValue({ id: 'p1', startMonth: '2026-07', features: { sms: true }, limits: { sms: 1 } });
    deps.billableEvents.findMany.mockResolvedValue([
      { id: '1', messageId: 'a', cost: D('0.35'), date: new Date('2026-07-02T00:00:00Z') },
      { id: '2', messageId: 'b', cost: D('0.35'), date: new Date('2026-07-03T00:00:00Z') },
      { id: '3', messageId: 'c', cost: null, date: new Date('2026-07-04T00:00:00Z') },
    ]);

    const rows = await svc.worklist('2026-07');

    expect(rows).toHaveLength(1);
    expect(rows[0]).toMatchObject({ companyId: 'c1', overageCount: 2, amountDue: '1.02' }); // 0.52 + 0.50
    expect(deps.twilio.getSmsPrice).not.toHaveBeenCalled();
  });
});

Step 2: Run — verify it fails

Run: pnpm exec vitest run services/global/SmsOverageService.test.ts Expected: FAIL — module not found.

Step 3: Implement the worklist (constructor takes injected deps for testability; the real wiring is in services/index.ts).

import prisma, { Prisma } from '@power-rent/prisma-client';
import { SMS_OVERAGE_GO_LIVE } from '@power-rent/constants';
import { rootPlanService } from '@power-rent/services';
import { Twilio } from '@power-rent/server/graphql/helpers';
import { SmsOverageRepository } from '@power-rent/repositories/sms-overage/SmsOverageRepository';
import { periodBounds, isValidPeriod } from './smsOverage/period';
import { quotaFor } from './smsOverage/quota';
import { computeCharge, type OverageMessage } from './smsOverage/charge';

type Deps = {
  prisma?: typeof prisma;
  plans?: { planByCompanyId: (id: string) => Promise<any> };
  repo?: SmsOverageRepository;
  twilio?: { getSmsPrice: (sid: string) => Promise<number> };
};

export type WorklistRow = {
  companyId: string;
  period: string;
  smsSent: number;
  quota: number;
  overageCount: number;
  amountDue: string; // serialized Decimal
  currency: 'USD';
};

export class SmsOverageService {
  private prisma: typeof prisma;
  private plans: NonNullable<Deps['plans']>;
  private repo: SmsOverageRepository;
  private twilio: NonNullable<Deps['twilio']>;

  constructor(deps: Deps = {}) {
    this.prisma = deps.prisma ?? prisma;
    this.plans = deps.plans ?? rootPlanService;
    this.repo = deps.repo ?? new SmsOverageRepository();
    this.twilio = deps.twilio ?? new Twilio();
  }

  async worklist(period: string): Promise<WorklistRow[]> {
    if (!isValidPeriod(period)) throw new Error(`Invalid period: ${period}`);
    const { from, to } = periodBounds(period);

    const grouped = await this.prisma.billable_events.groupBy({
      by: ['companyId'],
      where: { event: 'sms', date: { gte: from, lt: to } },
      _count: { _all: true },
    });

    const handled = new Set(
      (await this.repo.model.findMany({
        where: { period, status: { in: ['unpaid', 'paid', 'waived'] } },
        select: { companyId: true },
      })).map((r) => r.companyId),
    );

    const rows: WorklistRow[] = [];
    for (const g of grouped) {
      if (handled.has(g.companyId)) continue;
      const plan = await this.plans.planByCompanyId(g.companyId);
      const q = quotaFor(plan);
      if (!q.billable) continue;
      const smsSent = g._count._all;
      if (smsSent <= q.quota) continue;

      const messages = await this.overageMessages(g.companyId, plan, from, to, q.quota);
      const charge = computeCharge(messages.map((m) => ({ messageId: m.messageId, cost: m.cost })));
      rows.push({
        companyId: g.companyId,
        period,
        smsSent,
        quota: q.quota,
        overageCount: charge.overageCount,
        amountDue: charge.amountDue.toString(),
        currency: 'USD',
      });
    }
    return rows;
  }

  // overage = period SMS on/after billing start, ordered date asc, id asc, after the first `quota`
  private async overageMessages(companyId: string, plan: any, from: Date, to: Date, quota: number) {
    const billingFloor = this.billingStart(plan, from);
    const all = await this.prisma.billable_events.findMany({
      where: { companyId, event: 'sms', date: { gte: billingFloor, lt: to } },
      orderBy: [{ date: 'asc' }, { id: 'asc' }],
      select: { id: true, messageId: true, cost: true, date: true },
    });
    return all.slice(quota);
  }

  private billingStart(plan: any, periodFrom: Date): Date {
    // Forward-only floor: never bill before go-live, the period start, or the company's plan start.
    const candidates = [periodFrom, periodBounds(SMS_OVERAGE_GO_LIVE).from];
    if (plan?.startMonth && isValidPeriod(plan.startMonth)) {
      candidates.push(periodBounds(plan.startMonth).from);
    }
    return new Date(Math.max(...candidates.map((d) => d.getTime())));
  }
}

Note: billingStart returns the later of the period start, the SMS_OVERAGE_GO_LIVE floor, and the company’s plan.startMonth. A company whose SMS plan starts after the requested period therefore contributes no overage messages (the floor lands at/after to, so the slice is empty). When plan.startMonth falls inside the period, only on/after-start SMS are billable — but the grouped smsSent count still spans the whole month, so add a worklist test asserting a mid-period plan start excludes pre-start SMS from the charge (and, if plan.startMonth is later than the period entirely, the company is skipped). Confirm the real plan exposes startMonth as 'YYYY-MM'; if the plan shape differs, derive the start month from the plan’s effective date in one place.

Step 4: Run — verify it passes

Run: pnpm exec vitest run services/global/SmsOverageService.test.ts Expected: PASS.

Step 5: Register the singleton in services/index.ts (follow the existing billableEventService export pattern):

import { SmsOverageService } from './global/SmsOverageService';
export const smsOverageService = new SmsOverageService();

Step 6: Typecheck + commit

Run: pnpm typecheck

git add services/global/SmsOverageService.ts services/global/SmsOverageService.test.ts services/index.ts
git commit -m "feat(sms-overage): SmsOverageService live worklist (expected payments)"

Task 9: Bill-time cost reconciliation (S4, S7, L4)

Before computing the final charge at Bill, fill cost for overage messages with cost 0/null via getSmsPrice — capped, per-message isolated, never overwrite positive with 0.

Files:

Modify: services/global/SmsOverageService.ts (add reconcileCosts)
Test: services/global/SmsOverageService.reconcile.test.ts

Step 1: Write the failing test

import { describe, it, expect, vi } from 'vitest';
import { Prisma } from '@power-rent/prisma-client';
import { SmsOverageService } from './SmsOverageService';

const D = (n: string) => new Prisma.Decimal(n);

describe('SmsOverageService.reconcileCosts', () => {
  it('fetches price only for cost 0/null, writes positive results, never overwrites positive', async () => {
    const update = vi.fn().mockResolvedValue({});
    const getSmsPrice = vi.fn().mockResolvedValue(0.4);
    const { svc } = (() => {
      const s = new SmsOverageService({
        prisma: { billable_events: { update } } as any,
        twilio: { getSmsPrice },
      });
      return { svc: s };
    })();

    const messages = [
      { id: '1', messageId: 'a', cost: D('0.35') }, // real, untouched
      { id: '2', messageId: 'b', cost: null },       // reconciled to 0.40
    ];
    const out = await (svc as any).reconcileCosts(messages);

    expect(getSmsPrice).toHaveBeenCalledTimes(1);
    expect(getSmsPrice).toHaveBeenCalledWith('b');
    expect(update).toHaveBeenCalledWith({ where: { id: '2' }, data: { cost: 0.4 } });
    expect(out[1].cost?.toString()).toBe('0.4');
  });

  it('leaves cost null and does not write when price still 0', async () => {
    const update = vi.fn();
    const getSmsPrice = vi.fn().mockResolvedValue(0);
    const svc = new SmsOverageService({ prisma: { billable_events: { update } } as any, twilio: { getSmsPrice } });
    const out = await (svc as any).reconcileCosts([{ id: '2', messageId: 'b', cost: null }]);
    expect(update).not.toHaveBeenCalled();
    expect(out[0].cost).toBeNull();
  });
});

Step 2: Run — verify it fails

Run: pnpm exec vitest run services/global/SmsOverageService.reconcile.test.ts Expected: FAIL — reconcileCosts not a function.

Step 3: Implement (use @power-rent/try-catch/nextjs per message; cap calls; bounded concurrency). Add to the class:

import { Try } from '@power-rent/try-catch/nextjs';
import { SMS_RECONCILE_MAX_CALLS } from '@power-rent/constants';

// inside SmsOverageService:
private async reconcileCosts(
  messages: { id: string; messageId: string; cost: Prisma.Decimal | null }[],
) {
  let calls = 0;
  for (const m of messages) {
    if (m.cost != null && m.cost.greaterThan(0)) continue;
    if (calls >= SMS_RECONCILE_MAX_CALLS) break;
    calls += 1;

    const price = await new Try(() => this.twilio.getSmsPrice(m.messageId))
      .catch((err) => { console.error('reconcileCosts getSmsPrice failed', m.messageId, err); })
      .default(0)
      .value();

    if (price && price > 0) {
      m.cost = new Prisma.Decimal(price);
      await new Try(() => this.prisma.billable_events.update({ where: { id: m.id }, data: { cost: price } }))
        .catch((err) => { console.error('reconcileCosts update failed', m.id, err); })
        .value();
    }
  }
  return messages;
}

Confirm the exact @power-rent/try-catch/nextjs API against the error-handling skill (method names .catch/.default/.value and Try vs new Try). Adjust the calls to match; the contract required here is: run the async, log on error, fall back to 0/skip, never throw.

Step 4: Run — verify it passes

Run: pnpm exec vitest run services/global/SmsOverageService.reconcile.test.ts Expected: PASS.

Step 5: Commit

git add services/global/SmsOverageService.ts services/global/SmsOverageService.reconcile.test.ts
git commit -m "feat(sms-overage): Bill-time Twilio cost reconciliation (capped, isolated)"

Task 10: Bill / markPaid / waive / void (lifecycle, idempotency, closed-month) — M5, S8, S12

Files:

Modify: services/global/SmsOverageService.ts
Test: services/global/SmsOverageService.bill.test.ts

Step 1: Write failing tests covering: closed-month-only; recompute+reconcile then insert; unique-violation → friendly idempotent rejection (not 500); ServiceError checked via error != null; void→re-raise updates the voided row; markPaid/waive set actor fields.

// services/global/SmsOverageService.bill.test.ts  (abridged — write all four)
import { describe, it, expect, vi } from 'vitest';
import { Prisma } from '@power-rent/prisma-client';
import { SmsOverageService } from './SmsOverageService';

const D = (n: string) => new Prisma.Decimal(n);
const NOW = new Date('2026-08-10T00:00:00Z');

describe('SmsOverageService.bill', () => {
  it('rejects billing an open (current) month', async () => {
    const svc = new SmsOverageService({});
    await expect(svc.bill({ companyId: 'c1', period: '2026-08', actor: 'u1', now: NOW }))
      .rejects.toThrow(/closed/i);
  });

  it('inserts an unpaid row with frozen numbers for a closed month', async () => {
    const create = vi.fn().mockResolvedValue({ data: { id: 'o1' }, error: null });
    const repo = { findByCompanyPeriod: vi.fn().mockResolvedValue(null), model: { create } };
    // stub overageMessages + reconcile via prisma/plans/twilio
    const svc = new SmsOverageService({
      prisma: { billable_events: {
        groupBy: vi.fn(),
        findMany: vi.fn().mockResolvedValue([
          { id: '1', messageId: 'a', cost: D('0.35'), date: new Date('2026-07-02T00:00:00Z') },
          { id: '2', messageId: 'b', cost: D('0.35'), date: new Date('2026-07-03T00:00:00Z') },
        ]),
        update: vi.fn(),
      } } as any,
      plans: { planByCompanyId: vi.fn().mockResolvedValue({ id: 'p1', startMonth: '2026-07', features: { sms: true }, limits: { sms: 1 } }) },
      repo: repo as any,
      twilio: { getSmsPrice: vi.fn() },
    });

    const row = await svc.bill({ companyId: 'c1', period: '2026-07', actor: 'u1', now: NOW });
    expect(create).toHaveBeenCalled();
    const data = create.mock.calls[0][0].data;
    expect(data).toMatchObject({ companyId: 'c1', period: '2026-07', status: 'unpaid', raisedBy: 'u1', amountDue: '0.52', overageCount: 1 });
    expect(row.id).toBe('o1');
  });

  it('returns a friendly idempotent rejection on unique violation', async () => {
    const create = vi.fn().mockResolvedValue({ data: null, error: { code: 'P2002' } });
    const repo = { findByCompanyPeriod: vi.fn().mockResolvedValue(null), model: { create } };
    const svc = makeBillableSvc(repo); // helper mirroring the test above
    await expect(svc.bill({ companyId: 'c1', period: '2026-07', actor: 'u1', now: NOW }))
      .rejects.toThrow(/already/i);
  });
});

Step 2: Run — verify it fails

Run: pnpm exec vitest run services/global/SmsOverageService.bill.test.ts Expected: FAIL.

Step 3: Implement the four methods. Key rules:

bill: assert isClosedPeriod(period, now) else throw 'period not closed'. Load plan → quotaFor (throw if not billable). Build overage messages (Task 8 helper) → reconcileCosts → computeCharge. If an existing row is voided, update it back to unpaid with fresh numbers; otherwise create (status unpaid). Check result.error != null (it’s a truthy ServiceError, memory project_service_error_truthy_guard); on P2002 throw a friendly “already billed by {raisedBy}”. Freeze: smsSent, quota, overageCount, realCostSum, fallbackCount, markup(SMS_MARKUP), fallbackFlat(FALLBACK_FLAT_USD), amountDue, currency:'USD', windowFrom, windowTo, messageBasis, raisedBy, raisedAt:now.
markPaid({companyId, period, actor, paymentRef}): update row → status:'paid', paidBy:actor, paidAt:now, paymentRef.
waive({companyId, period, actor, reason}): update → status:'waived', waivedBy:actor, waivedAt:now, waiveReason:reason.
void({companyId, period, actor, reason}): update → status:'voided', voidedBy:actor, voidedAt:now, voidReason:reason.

Use Prisma error introspection for P2002. Wrap DB writes per the error-handling skill. All methods take now defaulting to new Date() (injected in tests).

Step 4: Run — verify it passes

Run: pnpm exec vitest run services/global/SmsOverageService.bill.test.ts Expected: PASS (all four).

Step 5: Add the non-tautological frozen test (S19)

Add a test: bill a period; then mutate the underlying billable_events mock + change SMS_MARKUP (re-import not trivial — instead assert the persisted create payload captured the frozen markup/amountDue independent of later reads). Confirm markPaid does NOT recompute amountDue.

Step 6: Commit

git add services/global/SmsOverageService.ts services/global/SmsOverageService.bill.test.ts
git commit -m "feat(sms-overage): bill/markPaid/waive/void lifecycle (closed-month, idempotent)"

Task 11: `requireRootAdmin` guard (M3)

Files:

Create: server/graphql/helpers/auth/requireRootAdmin.js
Test: server/graphql/helpers/auth/requireRootAdmin.test.js

Step 1: Write the failing test — passes for isAdmin, throws userIsNotAdmin otherwise (mirror banCompany’s dual fauna/postgres path).

import { describe, it, expect, vi } from 'vitest';
import requireRootAdmin from './requireRootAdmin';

const fqlCtx = (rootUser) => ({
  flags: {},
  rootClientX: { query: vi.fn().mockResolvedValue({ data: rootUser }) },
  services: {},
});

describe('requireRootAdmin', () => {
  it('passes for an admin', async () => {
    await expect(requireRootAdmin(fqlCtx({ isAdmin: true, email: 'a@b.c' }))).resolves.toMatchObject({ isAdmin: true });
  });
  it('throws for a non-admin', async () => {
    await expect(requireRootAdmin(fqlCtx({ isAdmin: false, email: 'x@y.z' }))).rejects.toThrow(/access denied/i);
  });
  it('throws when no identity', async () => {
    await expect(requireRootAdmin(fqlCtx(null))).rejects.toThrow(/access denied/i);
  });
});

Step 2: Run — verify it fails

Run: pnpm exec vitest run server/graphql/helpers/auth/requireRootAdmin.test.js Expected: FAIL — module not found.

Step 3: Implement (extract the banCompany pattern):

import * as Sentry from '@sentry/nextjs';
import { fql } from 'fauna';
import { ApiError, codes } from '@power-rent/lib/errorCodes';

export default async function requireRootAdmin(context) {
  const { flags, rootClientX, services } = context;

  let rootUser = null;
  if (flags?.faunaMigrationUserTenants) {
    rootUser = await services.usersService.getMeRoot();
  } else {
    ({ data: rootUser } = await rootClientX.query(fql`
      Query.identity() { email, isAdmin }
    `));
  }

  if (!rootUser || !rootUser.isAdmin) {
    const error = new ApiError(codes.userIsNotAdmin, 'Access denied');
    if (rootUser) Sentry.addBreadcrumb({ message: 'non-admin email', data: { email: rootUser.email } });
    Sentry.captureException(error);
    throw error;
  }
  return rootUser;
}

Step 4: Run — verify it passes

Run: pnpm exec vitest run server/graphql/helpers/auth/requireRootAdmin.test.js Expected: PASS.

Step 5: Commit

git add server/graphql/helpers/auth/requireRootAdmin.js server/graphql/helpers/auth/requireRootAdmin.test.js
git commit -m "feat(sms-overage): requireRootAdmin guard (reuse Billion-admin isAdmin check)"

Task 12: GraphQL surface (queries + mutations)

Files:

Create: server/graphql/schema/sms-overage.graphql
Create: server/graphql/resolvers/query/smsOverageWorklist.resolver.js, .../smsOverages.resolver.js, .../smsOveragePeriodDetail.resolver.js
Create: server/graphql/resolvers/mutation/SmsOverage/billSmsOverage.resolver.js, markSmsOveragePaid..., waiveSmsOverage..., voidSmsOverage...
Modify: server/graphql/resolvers/index.js (register under Query/Mutation)

Step 1: Define SDL (model on server/graphql/schema/root-company.graphql, which already hosts cross-company expenses):

type SmsOverageExpected {
  companyId: String!
  period: String!
  smsSent: Int!
  quota: Int!
  overageCount: Int!
  amountDue: String!
  currency: String!
}

type SmsOverageRecord {
  id: ID!
  companyId: String!
  period: String!
  overageCount: Int!
  amountDue: String!
  currency: String!
  status: String!
  raisedBy: String!
  raisedAt: String!
}

extend type Query {
  smsOverageWorklist(period: String!): [SmsOverageExpected!]!
  smsOverages(period: String, status: String): [SmsOverageRecord!]!
}

extend type Mutation {
  billSmsOverage(companyId: String!, period: String!): SmsOverageRecord!
  markSmsOveragePaid(companyId: String!, period: String!, paymentRef: String): SmsOverageRecord!
  waiveSmsOverage(companyId: String!, period: String!, reason: String!): SmsOverageRecord!
  voidSmsOverage(companyId: String!, period: String!, reason: String!): SmsOverageRecord!
}

(Confirm extend type Query/Mutation matches how other schema files extend the root types; copy the existing idiom.)

Step 2: Run server codegen

Run: pnpm codegen Expected: @power-rent/__graphql__/graphql now exports the new arg/return types.

Step 3: Write a resolver test (worklist resolver enforces root-admin then delegates):

import { describe, it, expect, vi } from 'vitest';
vi.mock('@power-rent/server/graphql/helpers/auth/requireRootAdmin', () => ({ default: vi.fn() }));
import requireRootAdmin from '@power-rent/server/graphql/helpers/auth/requireRootAdmin';
import resolver from './smsOverageWorklist.resolver';

it('requires root admin then returns the worklist', async () => {
  const ctx = { services: { smsOverageService: { worklist: vi.fn().mockResolvedValue([{ companyId: 'c1' }]) } } };
  const rows = await resolver(undefined, { period: '2026-07' }, ctx);
  expect(requireRootAdmin).toHaveBeenCalledWith(ctx);
  expect(rows).toEqual([{ companyId: 'c1' }]);
});

Step 4: Run — verify it fails, implement, verify it passes

Run: pnpm exec vitest run server/graphql/resolvers/query/smsOverageWorklist.resolver.test.js

Resolver shape (mutations derive actor from the verified root user, never from args):

import requireRootAdmin from '@power-rent/server/graphql/helpers/auth/requireRootAdmin';

export default async (_, { period }, context) => {
  await requireRootAdmin(context);
  return context.services.smsOverageService.worklist(period);
};

import requireRootAdmin from '@power-rent/server/graphql/helpers/auth/requireRootAdmin';

export default async (_, { companyId, period }, context) => {
  const admin = await requireRootAdmin(context);
  return context.services.smsOverageService.bill({ companyId, period, actor: admin.email });
};

(Implement the other three mutations + smsOverages/smsOveragePeriodDetail queries analogously. smsOverageService must be exposed on context.services — if context.services is only the tenant bundle, reference the singleton directly: import { smsOverageService } from '@power-rent/services' and call it. Check how expenses.resolver.js accesses global services and match it.)

Step 5: Register resolvers in server/graphql/resolvers/index.js — import each and add to the Query: {} (line ~1030) and Mutation: {} (line ~785) maps.

Step 6: Codegen client types + typecheck

Run: pnpm relay then pnpm typecheck Expected: clean.

Step 7: Commit

git add server/graphql/schema/sms-overage.graphql server/graphql/schema/root-company.graphql server/graphql/resolvers
git commit -m "feat(sms-overage): root-admin GraphQL queries + mutations"

Task 13: Staff dashboard UI (Relay)

Use the react-relay skill. Model the page on the existing Billion/root-admin pages. Keep one React component per file (memory feedback-one-file-one-component). SWR not involved here — this is Relay.

Files:

Create: a page under the root-admin area (find the Billion admin page dir, e.g. pages/billion/* or equivalent) — pages/<admin>/sms-overage.tsx
Create: components/sms-overage/SmsOverageBillingPage.tsx (query: smsOverageWorklist)
Create: components/sms-overage/SmsOverageRow.tsx (actions)
Create: components/sms-overage/BillConfirmationModal.tsx

Step 1: Period selector + worklist query — useLazyLoadQuery/usePreloadedQuery for smsOverageWorklist(period), default to last closed month. Render a table: company, smsSent/quota, overageCount, amountDue (USD), status. Empty state when none.

Step 2: Bill flow — a confirmation modal (S5) showing the recomputed breakdown before committing; on confirm call billSmsOverage. Disable the Bill button while the mutation runs (covers reconciliation latency, S7).

Step 3: Row actions — Mark paid (optional paymentRef), Waive (required reason), Void (required reason). Each calls its mutation and refetches the worklist/history.

Step 4: History view — a second tab/table backed by smsOverages(period, status).

Step 5: PII — never render recipient in the worklist; the per-period drill-down (smsOveragePeriodDetail) masks recipients (S15).

Step 6: Verify in the app — use the run skill / verify skill to load the page as a root-admin user and confirm the worklist, Bill modal, and a waive round-trip. Capture a screenshot.

Step 7: Commit

git add pages components
git commit -m "feat(sms-overage): staff billing dashboard (Relay)"

Final verification

Step 1: Full test suite

Run: pnpm test Expected: all pass; specifically the money tests in Tasks 6, 8, 10.

Step 2: Typecheck + lint

Run: pnpm typecheck && pnpm exec eslint services repositories server constants Expected: clean (no new errors).

Step 3: Verification-before-completion

Use superpowers:verification-before-completion. Confirm against the spec’s Testing section that each intent test exists: quota edges, selection ordering, math ($2.06 / $0.52), timezone (June not July), canonical date column, concurrency double-bill rejection, reconciliation scope, frozen non-tautological, authZ 403, isolation, validation, lifecycle exclusion, production shapes.

Step 4: Open the PR (only when the user asks).

Coverage map (spec → task)

M1 quotaFor → Task 5. M6 timezone → Task 4. M7 column → Task 2. M9 math → Task 6.
M3/M4 authZ + sudo read → Tasks 11, 12 (+ Task 8 root client). M5 idempotent bill → Task 10.
M8 (no Fauna) → Task 8 reads Postgres only (no faunaStopped gate, per latest decision).
S2/S3 messageBasis → Task 6 + persisted in Task 10. S4 no-overwrite reconcile → Task 9.
S5 confirm modal / S7 latency → Task 13 / Task 9 cap. S8 waivedBy / S12 statuses → Tasks 1, 10.
S10 Decimal/floor → Task 6. S13/S16 metrics (fallbackCount surfaced) → Tasks 1, 6, 13. S14 validation → Tasks 4, 12.
S15 PII → Tasks 12, 13. S21 index order → Task 1. L1 tiebreak → Tasks 6, 8.

Out of scope (do NOT build)

Email overage; in-app invoice/fattura/SDI; Stripe push; FX conversion (USD only); customer notifications (sales handle offline); crons; historical billing before billing start. A PDF statement is a possible fast-follow, not v1.

SMS Overage Billing Implementation Plan

Task 1: sms_overage Neon table + billable_events index

Task 2: Fix quota-count column to date (M7)

Task 3: Constants

Task 4: Period bounds helper (Europe/Rome) — M6

Task 5: quotaFor + billing-start resolution (M1, billing window)

Task 6: Overage charge math (per-message floor) — M9, S10

Task 7: SmsOverageRepository

Task 8: SmsOverageService — live worklist (expected payments)

Task 9: Bill-time cost reconciliation (S4, S7, L4)

Task 10: Bill / markPaid / waive / void (lifecycle, idempotency, closed-month) — M5, S8, S12

Task 11: requireRootAdmin guard (M3)

Task 12: GraphQL surface (queries + mutations)

Task 13: Staff dashboard UI (Relay)

Final verification

Coverage map (spec → task)

Out of scope (do NOT build)

Task 1: `sms_overage` Neon table + `billable_events` index

Task 2: Fix quota-count column to `date` (M7)

Task 5: `quotaFor` + billing-start resolution (M1, billing window)

Task 7: `SmsOverageRepository`

Task 8: `SmsOverageService` — live worklist (expected payments)

Task 11: `requireRootAdmin` guard (M3)