# Recurring Invoices

**Date:** 2026-05-31  
**Status:** Draft  
**Depends on:** `foundation-auth-rbac`, `invoices-core`, `customers-module`, `notification-center`, `zync-subscription`  
**Referenced by:** `billing-module`, `invoices-adapters`

---

## Overview

Recurring invoice templates allow tenants to auto-generate invoices on a schedule — weekly, monthly, quarterly, or yearly. A template stores the customer, line items, and schedule. A daily self-rescheduling Durable Object alarm (06:00 UTC) generates due invoices, optionally sends them automatically, and advances the next generation date. This eliminates manual work for clients on retainer or subscription billing.

---

## Tier Availability

| Feature | Free/Starter | Business | Enterprise |
|---------|-------------|----------|------------|
| Recurring invoice templates | No (402 Payment Required) | Yes (max 20 active) | Yes (unlimited) |
| Auto-send on generation | No | Yes | Yes |
| Auto-charge (stored payment) | No | No | Future (v2) |

Free/Starter tenants receive **402 Payment Required** on all recurring-invoice API routes via `requireTier('business')`. (402 Payment Required — matches the platform-wide requireTier convention for billing-tier gates.)

---

## Data Model

```sql
CREATE TABLE recurring_invoice_templates (
  id                   UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id            UUID        NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  customer_id          UUID        NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  title                TEXT        NOT NULL,                 -- e.g. "Monthly Retainer — Acme Ltd"
  description          TEXT,                                 -- optional notes
  line_items           JSONB       NOT NULL DEFAULT '[]',    -- same structure as invoices.line_items
  currency             TEXT        NOT NULL DEFAULT 'ILS',
  vat_rate             NUMERIC(5,4) NOT NULL DEFAULT 0.18,
  frequency            TEXT        NOT NULL CHECK (frequency IN ('weekly', 'monthly', 'quarterly', 'yearly')),
  frequency_day        INT,                                  -- day of month (1–31, clamped to the last valid day per month at generation) for monthly/quarterly/yearly
                                                             -- day of week (0=Sun–6=Sat) for weekly
  payment_terms_days   INT         NOT NULL DEFAULT 30,      -- due date = generation date + N days
  start_date           DATE        NOT NULL,                 -- first generation on/after this date
  end_date             DATE,                                 -- NULL = runs indefinitely
  auto_send            BOOLEAN     NOT NULL DEFAULT FALSE,   -- false=create draft, true=auto-send to customer
  auto_charge          BOOLEAN     NOT NULL DEFAULT FALSE,   -- future: charge saved payment method (v2 only)
  next_generation_date DATE        NOT NULL,                 -- date of next due generation
  last_generated_at    TIMESTAMPTZ,
  status               TEXT        NOT NULL DEFAULT 'active'
                         CHECK (status IN ('active', 'paused', 'completed', 'cancelled')),
  generated_count      INT         NOT NULL DEFAULT 0,
  created_by           UUID        NOT NULL REFERENCES users(id),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_rit_tenant        ON recurring_invoice_templates(tenant_id, status);
CREATE INDEX idx_rit_next_gen      ON recurring_invoice_templates(next_generation_date) WHERE status = 'active';
CREATE INDEX idx_rit_customer      ON recurring_invoice_templates(tenant_id, customer_id);

-- Link generated invoices back to their template
ALTER TABLE invoices ADD COLUMN recurring_template_id UUID REFERENCES recurring_invoice_templates(id) ON DELETE SET NULL;
ALTER TABLE invoices ADD COLUMN recurring_period_start DATE;  -- billing period stamp for generated invoices

CREATE UNIQUE INDEX invoices_recurring_template_period_uniq
  ON invoices (recurring_template_id, recurring_period_start)
  WHERE recurring_template_id IS NOT NULL AND recurring_period_start IS NOT NULL;
-- partial UNIQUE index: double-cron races on the same (template, period) cannot insert twice
```

---

## Generation Mechanism

### Daily trigger — Durable Object alarm

Daily generation (06:00 UTC) is driven by a self-rescheduling Durable Object alarm
(`RecurringInvoiceAlarmDO`), not a Cloudflare Cron Trigger. Rationale: the account is at
the 5-cron-trigger cap, so `[triggers] crons` stays disabled; a singleton SQLite-backed DO
re-arms its own alarm to the next 06:00 UTC in a `finally` block (a thrown run never breaks
the chain) and lazily bootstraps on first request. The CRON_SECRET-gated route
`POST /api/cron/recurring-invoice-generator` remains as the manual/on-demand trigger. Both
the alarm and the route call one shared loop (`runRecurringInvoiceGeneration`).

```typescript
// Pseudocode for generation job (Postgres/Neon via Hono Worker)
async function runRecurringInvoiceJob(db: NeonDB) {
  const today = new Date().toISOString().slice(0, 10)  // YYYY-MM-DD

  const due = await db.query(`
    SELECT * FROM recurring_invoice_templates
    WHERE status = 'active'
      AND next_generation_date <= $1::date
      AND (end_date IS NULL OR end_date >= $1::date)
  `, [today])

  for (const template of due.rows) {
    await generateInvoiceFromTemplate(template, db)
  }
}

async function generateInvoiceFromTemplate(template, db) {
  // 1. Create invoice row (status: draft or sent)
  const invoice = buildInvoice(template)
  await db.query('INSERT INTO invoices ...', [...])

  // 2. If auto_send: trigger send flow (PDF generation + email)
  if (template.auto_send) {
    await sendInvoice(invoice.id, db)
  }

  // 3. Advance next_generation_date
  const next = computeNextDate(template)
  await db.query(`
    UPDATE recurring_invoice_templates
    SET next_generation_date = $1, last_generated_at = NOW(), generated_count = generated_count + 1,
        status = CASE WHEN end_date IS NOT NULL AND $1 > end_date THEN 'completed' ELSE status END
    WHERE id = $2
  `, [next, template.id])
}
```

### Next date calculation

```typescript
function computeNextDate(template: RecurringTemplate): string {  // returns YYYY-MM-DD
  const base = new Date(template.next_generation_date)
  switch (template.frequency) {
    case 'weekly':    return formatDate(addWeeks(base, 1))
    case 'monthly':   return formatDate(setDayOfMonth(addMonths(base, 1), template.frequency_day ?? 1))
    case 'quarterly': return formatDate(setDayOfMonth(addMonths(base, 3), template.frequency_day ?? 1))
    case 'yearly':    return formatDate(setDayOfMonth(addYears(base, 1), template.frequency_day ?? 1))
  }
}
```

**Day clamping:** if `frequency_day = 31` and the target month has 28 days, clamp to last day of month.

### Auto-complete

When `end_date` is set and `next_generation_date > end_date` after advancing, set `status = 'completed'`. A notification is sent to the tenant OWNER.

---

## Features & Screens

### `/invoices/recurring` — Recurring Templates List

**Access:** OWNER, ADMIN, and MEMBERs with `invoices:write` permission.

This is a tab within the invoices module navigation: `Invoices | Recurring`.

**Table columns:** Template name | Customer | Frequency | Next date | Status | Generated | Actions

**Status badge:** Active (green) | Paused (amber) | Completed (grey) | Cancelled (red)

**Row actions:**
- Edit — opens template form
- Pause / Resume toggle
- Cancel (with confirmation dialog)
- "Generate now" — manual trigger (disabled if status ≠ active)
- View generated invoices — filters invoice list to this template

**"New Recurring Template" button** → opens template form.

---

### Template Create/Edit Form

**Fields:**

| Field | Type | Notes |
|-------|------|-------|
| Customer | Searchable select | Required |
| Template title | Text | Required, max 100 chars |
| Description | Textarea | Optional |
| Frequency | Select | Weekly / Monthly / Quarterly / Yearly |
| Day of month/week | Number | Shown for monthly/quarterly/yearly (1–31) or day-of-week picker for weekly |
| Start date | Date picker | Required; defaults to today |
| End date | Date picker | Optional; leave blank for indefinite |
| Line items | Line item editor | Reuses invoices-core line item component |
| Payment terms | Select | 7 / 14 / 30 / 45 / 60 days |
| Currency | Select | ILS / USD / EUR |
| VAT rate | Number | Pre-filled from tenant default |
| Auto-send | Toggle | Business+ only; if off, creates Draft |

**Preview panel:** shows a sample invoice using the current line items (live calculation of totals, VAT, due date).

**"Next generation" preview:** displays the next 3 scheduled dates based on current frequency/start settings.

---

### Generated Invoices View

`/invoices?recurring_template_id={id}` — standard invoice list filtered to this template. Adds a breadcrumb: "Recurring Templates > {Template Title}".

---

## Permissions

| Role | View templates | Create/Edit | Pause/Cancel | Generate Now | Delete |
|------|---------------|-------------|--------------|--------------|--------|
| OWNER | Yes | Yes | Yes | Yes | Yes |
| ADMIN | Yes | Yes | Yes | Yes | Yes |
| MEMBER (invoices:write) | Yes | Yes | No | Yes | No |
| MEMBER (read-only) | Yes | No | No | No | No |
| CONTRACTOR | No | No | No | No | No |
| CLIENT_PORTAL | No | No | No | No | No |

---

## API Endpoints

### `GET /api/recurring-invoices`
Query params: `status`, `customer_id`, `cursor`, `limit` (default 50).

### `POST /api/recurring-invoices`
Create template. `next_generation_date` computed from `start_date` + `frequency` + `frequency_day`.

### `GET /api/recurring-invoices/:id`
Returns template with generated count and next dates preview.

### `PATCH /api/recurring-invoices/:id`
Update template. Recalculates `next_generation_date` if frequency or start_date changed.

### `DELETE /api/recurring-invoices/:id`
Soft-cancels (sets `status = 'cancelled'`). Generated invoices remain intact.

### `POST /api/recurring-invoices/:id/generate-now`
Manual trigger. Generates one invoice immediately (same logic as cron job). Advances `next_generation_date`. Rate-limited: max 1 call per template per hour. Returns the created invoice ID.

### `GET /api/recurring-invoices/:id/invoices`
Lists all invoices generated from this template. Standard invoice list response shape.

---

## Architecture Decisions

### Template vs. Invoice ownership
Templates are never deleted on cancel — `status = 'cancelled'` is used. This preserves the link between generated invoices and their template for audit purposes.

### `frequency_day` clamping
Days > 28 are supported in the UI but clamped to the last valid day of the target month at generation time. The stored `frequency_day` value is preserved (e.g. 31) — clamping happens only at generation.

### Active template limit (Business)
On template creation, if the tenant is on Business plan and `COUNT(*) WHERE status = 'active' >= 20`, return 403 with upgrade prompt body. Enforced server-side.

### auto_charge (v2 placeholder)
`auto_charge` column exists in the schema but the feature is inactive. Any API request setting `auto_charge = 1` is accepted but ignored with a `warning` field in the response: `"auto_charge is not yet supported and will be ignored"`.

### Duplicate generation guard
Before generating an invoice from a template, the job checks:
```sql
SELECT COUNT(*) FROM invoices
WHERE recurring_template_id = ? AND recurring_period_start = ? -- the period being generated (template.next_generation_date)
```
If a generated invoice already exists for the current period, skip (idempotency guard for cron retries). (period-keyed guard — created_at windows mis-fire on weekly cadence and on multi-day catch-up runs).
Generation stamps `recurring_period_start = template.next_generation_date` on the new invoice row; the partial UNIQUE index on `(recurring_template_id, recurring_period_start)` enforces the same constraint at insert time.