# Recurring Expenses **Date:** 2026-06-01 **Status:** Draft **Spec:** 172 **Tier:** All tiers **Depends on:** `expenses-module`, `foundation-auth-rbac`, `settings-module` **Referenced by:** `expenses-module` --- ## Overview Many business expenses recur on a regular schedule: SaaS subscriptions, rent, insurance, retainer fees. Manually logging the same expense each month is error-prone and time-consuming. This spec defines recurring expense templates that auto-generate expense records on a configurable schedule. --- ## Data Model ```sql CREATE TABLE recurring_expenses ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, created_by UUID NOT NULL REFERENCES users(id) ON DELETE SET NULL, name TEXT NOT NULL, -- e.g. "AWS monthly bill" category TEXT NOT NULL, -- same as expenses.category amount NUMERIC(12,2) NOT NULL, -- default amount; may vary (see notes) currency TEXT NOT NULL DEFAULT 'ILS', vat_deductible BOOLEAN NOT NULL DEFAULT true, vat_rate NUMERIC(5,4) DEFAULT 0.18, project_id UUID REFERENCES projects(id) ON DELETE SET NULL, vendor_name TEXT, notes TEXT, -- Schedule frequency TEXT NOT NULL CHECK (frequency IN ('weekly', 'monthly', 'quarterly', 'yearly')), day_of_month INTEGER CHECK (day_of_month BETWEEN 1 AND 31), -- for monthly/quarterly/yearly day_of_week INTEGER CHECK (day_of_week BETWEEN 0 AND 6), -- for weekly (0=Sunday) start_date DATE NOT NULL, end_date DATE, -- NULL = no end -- State is_active BOOLEAN NOT NULL DEFAULT true, last_generated_date DATE, -- date of last generated expense next_due_date DATE NOT NULL, -- pre-computed; updated after each generation -- Approval auto_approve BOOLEAN NOT NULL DEFAULT false, -- skip approval queue if true created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() ); CREATE INDEX idx_recurring_exp_tenant ON recurring_expenses(tenant_id, is_active, next_due_date); ``` --- ## Features ### Recurring Expense List (`/expenses/recurring`) Linked from `/expenses` header: "Recurring templates (N active)". ``` ┌──────────────────────────────────────────────────────────────┐ │ Recurring Expenses [+ New template] │ │ │ │ Name Frequency Amount Next due │ │ ─────────────────────────────────────────────────────────── │ │ AWS — cloud services Monthly ₪890 Jul 01, 2026 │ │ Office rent Monthly ₪3,500 Jul 01, 2026 │ │ Accountant retainer Monthly ₪1,200 Jun 15, 2026 │ │ Domain renewal Annually ₪450 Dec 01, 2026 │ │ │ │ ─────────────────────────────────────────────────────────── │ │ 4 active templates · Next due: Jun 15 (Accountant retainer) │ └──────────────────────────────────────────────────────────────┘ ``` Row actions: Edit, Pause, Delete. Paused templates show grayed with "Paused" badge; not deleted. ### Create / Edit Template Modal ``` ┌──────────────────────────────────────────────────────────────┐ │ New recurring expense [✕] │ │ │ │ Name * [AWS — cloud services_____________] │ │ Category * [Software & Subscriptions ▾] │ │ Amount * ₪ [890_______] │ │ Currency [ILS ▾] │ │ VAT deductible ☑ VAT rate: [18%__] │ │ Vendor [Amazon Web Services______] │ │ Project [— (no project) ▾] │ │ │ │ Frequency * [Monthly ▾] │ │ Day of month * [1___] (1st of each month) │ │ Start date * [2026-07-01___] │ │ End date [___________] (leave blank = ongoing) │ │ │ │ Auto-approve ☑ (skip approval queue for this expense) │ │ │ │ [Cancel] [Create template] │ └──────────────────────────────────────────────────────────────┘ ``` ### Auto-generation cron Runs daily at 06:00 UTC via Cloudflare Cron Trigger: ```ts // apps/zync-api/src/cron/recurring-expenses.ts export async function generateDueExpenses(env: Env): Promise { const today = new Date().toISOString().slice(0, 10) const due = await db.query(` SELECT * FROM recurring_expenses WHERE is_active = true AND next_due_date <= $1 AND (end_date IS NULL OR end_date >= $1) `, [today]) for (const template of due) { // Create expense record const expense = await db.query(` INSERT INTO expenses ( tenant_id, category, amount, currency, vendor_name, expense_date, notes, vat_deductible, vat_rate, project_id, source, approval_status, status, created_by, recurring_template_id ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, 'recurring', $11, 'PENDING', $12, $13) RETURNING id `, [ template.tenant_id, template.category, template.amount, template.currency, template.vendor_name, template.next_due_date, // expense dated to due date template.notes || `Recurring: ${template.name}`, template.vat_deductible, template.vat_rate, template.project_id, template.auto_approve ? 'approved' : 'pending', template.created_by, template.id ]) // Update next_due_date const nextDate = computeNextDueDate(template) await db.query(` UPDATE recurring_expenses SET last_generated_date = $1, next_due_date = $2, updated_at = NOW() WHERE id = $3 `, [template.next_due_date, nextDate, template.id]) // Notify: 'expense_submitted' notification to expense approvers if (!template.auto_approve) { await notifyExpenseApprovers(template.tenant_id, expense.id) } } } ``` **`computeNextDueDate`** logic: - `monthly`: add 1 month to current date; set day to `template.day_of_month` (clamped to last day of month) - `quarterly`: add 3 months - `yearly`: add 12 months - `weekly`: add 7 days ### Schema delta on expenses ```sql ALTER TABLE expenses ADD COLUMN recurring_template_id UUID REFERENCES recurring_expenses(id) ON DELETE SET NULL; -- Links a generated expense to its template; NULL for manually created expenses ALTER TABLE expenses ADD COLUMN source TEXT; -- extend source values to include 'recurring' ``` --- ## API ``` GET /api/expenses/recurring → list templates (filterable: active/paused) POST /api/expenses/recurring → create template GET /api/expenses/recurring/:id → template detail + generated expense history PATCH /api/expenses/recurring/:id → edit template DELETE /api/expenses/recurring/:id → delete template (confirm: "X generated expenses will be unlinked") POST /api/expenses/recurring/:id/pause → pause (is_active → false) POST /api/expenses/recurring/:id/resume → resume (is_active → true; recalculates next_due_date from today) ``` All routes require `expenses:write`. --- ## Architecture Decisions | Decision | Choice | Reason | |----------|--------|--------| | Template + generated expense | Not real-time generation | Generated expenses appear in the normal expense flow; staff can edit/reject generated expenses without touching the template | | Daily cron at 06:00 UTC | Not exact-time delivery | Expense timing precision doesn't matter; daily generation in the morning ensures expenses appear before staff starts their day | | `next_due_date` pre-computed | Not computed on-the-fly | Querying "what's due today" across all tenants requires a single index scan; computing next dates dynamically would require per-template logic on every cron run | | Pause vs delete | Both options | Pause preserves template + history; delete removes template (generated expenses remain, just unlinked); both use cases are valid | | Auto-approve flag per template | Not tenant-wide | Some recurring expenses (rent, salaries) need no review; others (variable cloud bills) benefit from review; per-template control is more useful |