/** * Invoice query helpers — invoices-core. * * All helpers are tenant-filtered; every statement carries a tenant_id WHERE. * Route files MUST NOT import raw Drizzle tables; they import from this module * via @zync/db/queries. * * Key invariants: * - Number assignment (proformaNumber at SENT, invoiceNumber at TAX_ISSUED) * happens atomically inside the same transaction as the status update. * - nextNumber() uses INSERT…ON CONFLICT DO UPDATE to handle first-invoice-per-tenant. * - VAT rate is fetched from vat_rates and stored immutably at issue time. * - billedEntryIds on create marks time_entries.invoice_id via markTimeEntriesBilled. */ import { and, eq, lt, or, lte, desc, count, sql, inArray, isNull, isNotNull } from 'drizzle-orm' import { z } from 'zod' import type { Db, DbTx } from '../client' import { invoices, invoiceLines } from '../schema/invoices' import { customers } from '../schema/customers' import { vatRates } from '../schema/vat-rates' import { tenantSettings } from '../schema/tenants' import { auditLog } from './_audit-forward' import { captureEntityChange, computeDiff } from './entity-history' import { assertTenantOwnsOrThrow, assertTenantOwnsCustomer, assertTenantOwnsProject, } from './tenant-guards' import { nextInvoiceNumber } from './invoice-sequences' import { findAlreadyBilledEntries, markTimeEntriesBilled, } from './time-invoice' import { timeEntries } from '../schema/time' export { nextInvoiceNumber } from './invoice-sequences' /** Optional actor context for operational-audit-trail diff capture. */ export interface InvoiceActorContext { actorName?: string | null actorEmail?: string | null ipAddress?: string | null } // ── Zod schemas (re-exported for routes per require-zod-validation-in-routes) ── export const invoiceLineSchema = z.object({ description: z.string().min(1).max(2000), quantity: z.number().positive(), unitPrice: z.number().nonnegative(), discountPct: z.number().min(0).max(100).optional().default(0), taxable: z.boolean().optional().default(true), position: z.number().int().min(0), }) export const createInvoiceSchema = z.object({ customerId: z.string().uuid(), projectId: z.string().uuid().optional().nullable(), currency: z.string().length(3).optional().default('ILS'), dueDate: z.string().optional().nullable(), // ISO date string notes: z.string().max(5000).optional().nullable(), dedupKey: z.string().max(200).optional().nullable(), source: z .enum(['manual', 'retainer', 'hourly_auto', 'fixed_deposit', 'credit_note', 'auto_charge']) .optional() .default('manual'), lines: z.array(invoiceLineSchema).min(1), // For time-to-invoice (spec 77): billed entry IDs, NOT processed here billedEntryIds: z.array(z.string().uuid()).optional(), expenseId: z.string().uuid().optional(), // Credit note: link to the invoice being credited parentInvoiceId: z.string().uuid().optional().nullable(), // recurring-invoices: link generated invoice to template + billing period recurringTemplateId: z.string().uuid().optional().nullable(), recurringPeriodStart: z .string() .regex(/^\d{4}-\d{2}-\d{2}$/) .optional() .nullable(), }) export const updateInvoiceSchema = z.object({ customerId: z.string().uuid().optional(), projectId: z.string().uuid().optional().nullable(), currency: z.string().length(3).optional(), dueDate: z.string().optional().nullable(), notes: z.string().max(5000).optional().nullable(), lines: z.array(invoiceLineSchema).min(1).optional(), }) export const rejectInvoiceSchema = z.object({ reason: z.string().min(1).max(2000), }) export const voidInvoiceSchema = z.object({ reason: z.string().min(1).max(2000), }) export const recordPaymentSchema = z.object({ amount: z.number().positive(), paymentMethod: z.string().min(1).max(100), paymentDate: z.string(), // ISO date string reference: z.string().max(500).optional().nullable(), }) export const listInvoicesSchema = z.object({ cursor: z.string().optional(), limit: z.coerce.number().int().min(1).max(100).optional().default(50), status: z .enum([ 'DRAFT', 'SENT', 'APPROVED', 'REJECTED', 'TAX_ISSUED', 'PAID', 'PARTIALLY_PAID', 'VOID', 'WRITTEN_OFF', 'BAD_DEBT', ]) .optional(), customerId: z.string().uuid().optional(), projectId: z.string().uuid().optional(), dateFrom: z.string().optional(), dateTo: z.string().optional(), recurringTemplateId: z.string().uuid().optional(), jobId: z.string().uuid().optional(), isTemplate: z.coerce.boolean().optional(), source: z .enum(['manual', 'retainer', 'hourly_auto', 'fixed_deposit', 'credit_note', 'auto_charge']) .optional(), }) export const autoIssueSchema = z.object({ customerId: z.string().uuid(), projectId: z.string().uuid().optional().nullable(), lines: z.array(invoiceLineSchema).min(1), paymentMethodId: z.string().optional().nullable(), source: z.literal('auto_charge').optional().default('auto_charge'), }) // ── Public interfaces ───────────────────────────────────────────────────────── export type InvoiceStatus = | 'DRAFT' | 'SENT' | 'APPROVED' | 'REJECTED' | 'TAX_ISSUED' | 'PAID' | 'PARTIALLY_PAID' | 'VOID' | 'WRITTEN_OFF' | 'BAD_DEBT' export interface InvoiceLine { id: string invoiceId: string tenantId: string description: string quantity: string unitPrice: string discountPct: string lineTotal: string taxable: boolean position: number expenseId: string | null createdAt: string } export interface Invoice { id: string tenantId: string customerId: string | null projectId: string | null invoiceNumber: string | null proformaNumber: string | null status: InvoiceStatus currency: string issueDate: string | null taxIssueDate: string | null dueDate: string | null vatRate: string | null subtotal: string vatAmount: string total: string amountPaid: string notes: string | null source: string sentAt: string | null approvedAt: string | null approvedBy: string | null approvalNote: string | null rejectionReason: string | null rejectionNotifyCustomer: boolean taxIssuedAt: string | null paidAt: string | null externalId: string | null externalProvider: string | null voidReason: string | null voidedAt: string | null voidedBy: string | null parentInvoiceId: string | null htmlSnapshotUrl: string | null createdBy: string createdAt: string updatedAt: string customerName: string | null } export interface InvoiceWithLines extends Invoice { lines: InvoiceLine[] } /** createInvoice result; skipped=true when dedupKey conflicted with an existing row. */ export type CreateInvoiceResult = InvoiceWithLines & { skipped: boolean } export interface InvoiceListPage { items: Invoice[] nextCursor: string | null total: number } // ── Cursor helpers ──────────────────────────────────────────────────────────── function encodeCursor(id: string, createdAt: string): string { return Buffer.from(JSON.stringify({ id, created_at: createdAt })).toString('base64url') } function decodeCursor(cursor: string): { id: string; created_at: string } | null { try { return JSON.parse(Buffer.from(cursor, 'base64url').toString('utf8')) } catch { return null } } // ── Mappers ─────────────────────────────────────────────────────────────────── function mapInvoice(row: typeof invoices.$inferSelect): Invoice { return { id: row.id, tenantId: row.tenantId, customerId: row.customerId, projectId: row.projectId ?? null, invoiceNumber: row.invoiceNumber ?? null, proformaNumber: row.proformaNumber ?? null, status: row.status as InvoiceStatus, currency: row.currency, issueDate: row.issueDate ?? null, taxIssueDate: row.taxIssueDate ?? null, dueDate: row.dueDate ?? null, vatRate: row.vatRate ?? null, subtotal: row.subtotal, vatAmount: row.vatAmount, total: row.total, amountPaid: row.amountPaid, notes: row.notes ?? null, source: row.source, sentAt: row.sentAt?.toISOString() ?? null, approvedAt: row.approvedAt?.toISOString() ?? null, approvedBy: row.approvedBy ?? null, approvalNote: row.approvalNote ?? null, rejectionReason: row.rejectionReason ?? null, rejectionNotifyCustomer: row.rejectionNotifyCustomer ?? false, taxIssuedAt: row.taxIssuedAt?.toISOString() ?? null, paidAt: row.paidAt?.toISOString() ?? null, externalId: row.externalId ?? null, externalProvider: row.externalProvider ?? null, voidReason: row.voidReason ?? null, voidedAt: row.voidedAt?.toISOString() ?? null, voidedBy: row.voidedBy ?? null, parentInvoiceId: row.parentInvoiceId ?? null, htmlSnapshotUrl: row.htmlSnapshotUrl ?? null, createdBy: row.createdBy, createdAt: row.createdAt.toISOString(), updatedAt: row.updatedAt.toISOString(), customerName: null, } } function mapLine(row: typeof invoiceLines.$inferSelect): InvoiceLine { return { id: row.id, invoiceId: row.invoiceId, tenantId: row.tenantId, description: row.description, quantity: row.quantity, unitPrice: row.unitPrice, discountPct: row.discountPct, lineTotal: row.lineTotal, taxable: row.taxable, position: row.position, expenseId: row.expenseId ?? null, createdAt: row.createdAt.toISOString(), } } export async function recalculateInvoiceTotals( db: Db | DbTx, tenantId: string, invoiceId: string, ): Promise { const [invoice] = await db .select({ id: invoices.id, tenantId: invoices.tenantId, vatRate: invoices.vatRate, source: invoices.source, }) .from(invoices) .where(and(eq(invoices.id, invoiceId), eq(invoices.tenantId, tenantId))) .limit(1) if (!invoice) { throw new NotFoundError('Invoice not found') } const lines = await db .select({ quantity: invoiceLines.quantity, unitPrice: invoiceLines.unitPrice, discountPct: invoiceLines.discountPct, taxable: invoiceLines.taxable, }) .from(invoiceLines) .where(eq(invoiceLines.invoiceId, invoiceId)) const totals = computeTotals( lines.map((line) => ({ quantity: parseFloat(line.quantity), unitPrice: parseFloat(line.unitPrice), discountPct: parseFloat(line.discountPct), taxable: line.taxable, })), invoice.vatRate ? parseFloat(invoice.vatRate) : 0, ) assertPositiveSalesInvoiceTotals(invoice.source, totals, 'recalculate invoice') await db .update(invoices) .set({ subtotal: String(totals.subtotal), vatAmount: String(totals.vatAmount), total: String(totals.total), updatedAt: new Date(), }) .where(and(eq(invoices.id, invoiceId), eq(invoices.tenantId, tenantId))) } // ── VAT helpers ─────────────────────────────────────────────────────────────── /** * Fetch the VAT rate in effect for a country on a given date. * Uses lte(effective_from, date) DESC LIMIT 1 — finds the rate in effect on * that date even if a newer rate exists in the future. */ export async function getVatRateForDate( db: Db | DbTx, countryCode: string, asOfDate: string, ): Promise { const [row] = await db .select({ rate: vatRates.rate }) .from(vatRates) .where( and(eq(vatRates.countryCode, countryCode), lte(vatRates.effectiveFrom, asOfDate)), ) .orderBy(desc(vatRates.effectiveFrom)) .limit(1) return row?.rate ?? null } // ── Totals calculation ──────────────────────────────────────────────────────── interface LineParsed { quantity: number unitPrice: number discountPct: number taxable: boolean } function assertPositiveSalesInvoiceTotals( source: string | null | undefined, totals: { subtotal: number }, action: string, ): void { if (source === 'credit_note') return if (totals.subtotal <= 0) { throw new Error(`Cannot ${action}: invoice subtotal must be positive`) } } function computeTotals( lines: LineParsed[], vatRate: number, ): { subtotal: number; vatAmount: number; total: number } { let subtotal = 0 let taxableSubtotal = 0 for (const l of lines) { const lineTotal = l.quantity * l.unitPrice * (1 - l.discountPct / 100) subtotal += lineTotal if (l.taxable) taxableSubtotal += lineTotal } const vatAmount = taxableSubtotal * vatRate const total = subtotal + vatAmount return { subtotal: Math.round(subtotal * 100) / 100, vatAmount: Math.round(vatAmount * 100) / 100, total: Math.round(total * 100) / 100, } } // ── Query helpers ───────────────────────────────────────────────────────────── export async function listInvoices( db: Db, tenantId: string, opts: z.input = {}, ): Promise { const limit = Math.min(opts.limit ?? 50, 100) const jobIdCondition = opts.jobId ? sql`${invoices.id} IN ( SELECT entity_id FROM import_job_results WHERE import_job_id = ${opts.jobId}::uuid AND status = 'success' AND entity_id IS NOT NULL )` : undefined const statusFilter = opts.status != null ? eq(invoices.status, opts.status) : sql`${invoices.status} <> 'DRAFT'` const templateFilter = opts.isTemplate === true ? eq(invoices.isTemplate, true) : eq(invoices.isTemplate, false) // Total count (no cursor) const countRows = await db .select({ total: count() }) .from(invoices) .where( and( eq(invoices.tenantId, tenantId), statusFilter, templateFilter, opts.customerId ? eq(invoices.customerId, opts.customerId) : undefined, opts.projectId ? eq(invoices.projectId, opts.projectId) : undefined, opts.recurringTemplateId ? eq(invoices.recurringTemplateId, opts.recurringTemplateId) : undefined, jobIdCondition, opts.source ? eq(invoices.source, opts.source) : undefined, opts.dateFrom ? sql`${invoices.issueDate} >= ${opts.dateFrom}` : undefined, opts.dateTo ? sql`${invoices.issueDate} <= ${opts.dateTo}` : undefined, ), ) const totalCount = countRows[0]?.total ?? 0 // Cursor let cursorCondition: ReturnType | undefined if (opts.cursor) { const decoded = decodeCursor(opts.cursor) if (decoded) { cursorCondition = or( lt(invoices.createdAt, new Date(decoded.created_at)), and(eq(invoices.createdAt, new Date(decoded.created_at)), lt(invoices.id, decoded.id)), ) } } const rows = await db .select() .from(invoices) .where( and( eq(invoices.tenantId, tenantId), statusFilter, templateFilter, opts.customerId ? eq(invoices.customerId, opts.customerId) : undefined, opts.projectId ? eq(invoices.projectId, opts.projectId) : undefined, opts.recurringTemplateId ? eq(invoices.recurringTemplateId, opts.recurringTemplateId) : undefined, jobIdCondition, opts.source ? eq(invoices.source, opts.source) : undefined, opts.dateFrom ? sql`${invoices.issueDate} >= ${opts.dateFrom}` : undefined, opts.dateTo ? sql`${invoices.issueDate} <= ${opts.dateTo}` : undefined, cursorCondition, ), ) .orderBy(desc(invoices.createdAt), desc(invoices.id)) .limit(limit + 1) const hasMore = rows.length > limit const items = rows.slice(0, limit) const lastItem = items[items.length - 1] const nextCursor = hasMore && lastItem ? encodeCursor(lastItem.id, lastItem.createdAt.toISOString()) : null return { items: items.map(mapInvoice), nextCursor, total: totalCount, } } export async function getInvoice( db: Db, tenantId: string, id: string, ): Promise { const [row] = await db .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) return row ? mapInvoice(row) : null } export async function getInvoiceWithLines( db: Db, tenantId: string, id: string, ): Promise { const [row] = await db .select({ invoice: invoices, customerName: customers.name, }) .from(invoices) .leftJoin(customers, eq(invoices.customerId, customers.id)) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) if (!row) return null const lines = await db .select() .from(invoiceLines) .where(eq(invoiceLines.invoiceId, id)) .orderBy(invoiceLines.position) return { ...mapInvoice(row.invoice), customerName: row.customerName ?? null, lines: lines.map(mapLine), } } async function loadInvoiceWithLinesByDedupKey( tx: DbTx, tenantId: string, dedupKey: string, ): Promise { const [row] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.dedupKey, dedupKey))) .limit(1) if (!row) return null const lines = await tx .select() .from(invoiceLines) .where(eq(invoiceLines.invoiceId, row.id)) .orderBy(invoiceLines.position) return { ...mapInvoice(row), lines: lines.map(mapLine) } } export async function createInvoiceInTx( tx: DbTx, tenantId: string, actorId: string, input: z.infer, /** Country code for VAT lookup — typically from tenant.country */ _countryCode: string, actorCtx?: InvoiceActorContext, ): Promise { assertTenantOwnsOrThrow( 'customerId', await assertTenantOwnsCustomer(tx, tenantId, input.customerId), ) assertTenantOwnsOrThrow( 'projectId', await assertTenantOwnsProject(tx, tenantId, input.projectId), ) // Compute line totals const parsedLines = input.lines.map((l) => ({ ...l, lineTotal: Math.round(l.quantity * l.unitPrice * (1 - l.discountPct / 100) * 100) / 100, })) // For draft — no VAT rate yet (assigned at tax issue time) const totals = computeTotals(parsedLines, 0) let dueDate = input.dueDate ?? null if (!dueDate) { const [tenantSetting] = await tx .select({ defaultPaymentTermsDays: tenantSettings.defaultPaymentTermsDays }) .from(tenantSettings) .where(eq(tenantSettings.tenantId, tenantId)) .limit(1) const paymentTermsDays = tenantSetting?.defaultPaymentTermsDays ?? 30 const base = new Date() base.setUTCDate(base.getUTCDate() + paymentTermsDays) dueDate = base.toISOString().slice(0, 10) } const insertValues = { tenantId, customerId: input.customerId, projectId: input.projectId ?? null, currency: input.currency ?? 'ILS', dueDate, notes: input.notes ?? null, dedupKey: input.dedupKey ?? null, source: input.source ?? 'manual', parentInvoiceId: input.parentInvoiceId ?? null, recurringTemplateId: input.recurringTemplateId ?? null, recurringPeriodStart: input.recurringPeriodStart ?? null, status: 'DRAFT' as const, subtotal: String(totals.subtotal), vatAmount: '0', total: String(totals.subtotal), createdBy: actorId, } let invoice: typeof invoices.$inferSelect if (input.dedupKey) { const invoiceRows = await tx .insert(invoices) .values(insertValues) .onConflictDoNothing({ target: [invoices.tenantId, invoices.dedupKey], where: isNotNull(invoices.dedupKey), }) .returning() const inserted = invoiceRows[0] if (!inserted) { const existing = await loadInvoiceWithLinesByDedupKey(tx, tenantId, input.dedupKey) if (!existing) throw new Error('Invoice dedup conflict but existing row not found') return { ...existing, skipped: true } } invoice = inserted } else { const invoiceRows = await tx.insert(invoices).values(insertValues).returning() const inserted = invoiceRows[0] if (!inserted) throw new Error('Invoice insert failed') invoice = inserted } const lineRows = await tx .insert(invoiceLines) .values( parsedLines.map((l) => ({ invoiceId: invoice.id, tenantId, description: l.description, quantity: String(l.quantity), unitPrice: String(l.unitPrice), discountPct: String(l.discountPct), lineTotal: String(l.lineTotal), taxable: l.taxable, position: l.position, })), ) .returning() if (input.expenseId) { const expenseRows = await tx.execute(sql` SELECT e.id, e.project_id, e.billed_at, e.status, p.customer_id FROM expenses e LEFT JOIN projects p ON p.id = e.project_id WHERE e.id = ${input.expenseId} AND e.tenant_id = ${tenantId} LIMIT 1 `) const expense = (expenseRows as unknown as Array<{ id: string project_id: string | null billed_at: string | null status: string | null customer_id: string | null }>)[0] if (!expense) { throw new NotFoundError('Expense not found') } if (expense.status !== 'COMPLETED' || expense.billed_at || !expense.project_id) { throw new ConflictError('Expense is not billable') } if (expense.customer_id !== invoice.customerId) { throw new ConflictError('Expense customer does not match invoice customer') } const firstLine = lineRows[0] if (!firstLine) { throw new ConflictError('Invoice must contain at least one line') } await tx .update(invoiceLines) .set({ expenseId: input.expenseId }) .where(eq(invoiceLines.id, firstLine.id)) await tx.execute(sql` UPDATE expenses SET billed_at = NOW(), invoice_id = ${invoice.id}, updated_at = NOW() WHERE id = ${input.expenseId} AND tenant_id = ${tenantId} AND billed_at IS NULL `) } if (input.billedEntryIds && input.billedEntryIds.length > 0) { const alreadyBilled = await findAlreadyBilledEntries(tx, tenantId, input.billedEntryIds) if (alreadyBilled.length > 0) { throw new ConflictError( `Cannot bill time entries already linked to an invoice: ${alreadyBilled.join(', ')}`, ) } const billableRows = await tx .select({ id: timeEntries.id }) .from(timeEntries) .where( and( eq(timeEntries.tenantId, tenantId), inArray(timeEntries.id, input.billedEntryIds), isNull(timeEntries.invoiceId), eq(timeEntries.billable, true), ), ) if (billableRows.length !== input.billedEntryIds.length) { throw new ConflictError( 'One or more time entries are missing, not billable, or already billed', ) } const billedAt = new Date() await markTimeEntriesBilled(tx, tenantId, { invoiceId: invoice.id, entryIds: input.billedEntryIds, billedAt, }) } await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: invoice.id, action: 'invoice.created', changes: null, }) // operational-audit-trail: capture create event (no beforeState for creates) await captureEntityChange({ tx, tenantId, userId: actorId, actorName: actorCtx?.actorName ?? null, actorEmail: actorCtx?.actorEmail ?? null, eventType: 'invoice.created', entityType: 'invoice', entityId: invoice.id, entityLabel: invoice.proformaNumber ?? invoice.id, beforeState: null, afterState: { status: 'DRAFT', customerId: invoice.customerId }, ipAddress: actorCtx?.ipAddress ?? null, }) return { ...mapInvoice(invoice), lines: lineRows.map(mapLine), skipped: false } } /** Per-isolate cache for tenant default-payment-terms (60s TTL). */ const paymentTermsCache = new Map() async function getCachedPaymentTermsDays(db: Db, tenantId: string): Promise { const now = Date.now() const cached = paymentTermsCache.get(tenantId) if (cached && cached.expiresAt > now) return cached.days const [tenantSetting] = await db .select({ defaultPaymentTermsDays: tenantSettings.defaultPaymentTermsDays }) .from(tenantSettings) .where(eq(tenantSettings.tenantId, tenantId)) .limit(1) const days = tenantSetting?.defaultPaymentTermsDays ?? null paymentTermsCache.set(tenantId, { days, expiresAt: now + 60_000 }) return days } export async function createInvoice( db: Db, tenantId: string, actorId: string, input: z.infer, /** Country code for VAT lookup — typically from tenant.country */ countryCode: string, actorCtx?: InvoiceActorContext, ): Promise { // Normalize empty-string dedupKey to null: "" is falsy so it would otherwise take // the CTE branch (stores NULL) while createInvoiceInTx stored "" — both paths now // treat "" deterministically as "no dedup". const dedupKey = input.dedupKey ? input.dedupKey : null // Rare branches keep the proven interactive-tx path: dedupKey needs ON CONFLICT // dedup → skipped:true; billedEntryIds needs validation + markTimeEntriesBilled. // Both require conditional flow that cannot live in a single CTE. if (dedupKey || input.expenseId || (input.billedEntryIds && input.billedEntryIds.length > 0)) { return db.transaction(async (tx) => createInvoiceInTx(tx, tenantId, actorId, input, countryCode, actorCtx), ) } // Common case: collapse the whole create into ONE data-modifying CTE so it rides // Hyperdrive's pooled fast path (one round-trip, atomic by definition) instead of // an interactive tx that pins a connection (~8s, killed under concurrency). const parsedLines = input.lines.map((l) => ({ ...l, lineTotal: Math.round(l.quantity * l.unitPrice * (1 - l.discountPct / 100) * 100) / 100, })) const totals = computeTotals(parsedLines, 0) // dueDate default — byte-identical to createInvoiceInTx: one pooled (non-tx) read, // then compute in JS (CURRENT_DATE in-CTE would risk a UTC/day-boundary mismatch). let dueDate = input.dueDate ?? null if (!dueDate) { const paymentTermsDays = (await getCachedPaymentTermsDays(db, tenantId)) ?? 30 const base = new Date() base.setUTCDate(base.getUTCDate() + paymentTermsDays) dueDate = base.toISOString().slice(0, 10) } const currency = input.currency ?? 'ILS' const source = input.source ?? 'manual' const projectId = input.projectId ?? null const parentInvoiceId = input.parentInvoiceId ?? null const recurringTemplateId = input.recurringTemplateId ?? null const recurringPeriodStart = input.recurringPeriodStart ?? null const notes = input.notes ?? null const actorName = actorCtx?.actorName ?? null const actorEmail = actorCtx?.actorEmail ?? null const ipAddress = actorCtx?.ipAddress ?? null // Tenant-ownership guard folded INTO the insert: the row is created only when the // customer (and project, when supplied) belongs to the tenant. 0 rows ⇒ guard // failed; the audit inserts SELECT FROM ins_invoice, so they cascade with it. const projectGuard = projectId != null ? sql` AND EXISTS (SELECT 1 FROM projects WHERE id = ${projectId}::uuid AND tenant_id = ${tenantId}::uuid)` : sql`` const lineValues = sql.join( parsedLines.map( (l) => sql`(${l.description}::text, ${String(l.quantity)}::numeric, ${String(l.unitPrice)}::numeric, ${String(l.discountPct)}::numeric, ${String(l.lineTotal)}::numeric, ${l.taxable}::boolean, ${l.position}::int)`, ), sql`, `, ) const rows = (await db.execute(sql` WITH ins_invoice AS ( INSERT INTO invoices ( tenant_id, customer_id, project_id, currency, due_date, notes, dedup_key, source, parent_invoice_id, recurring_template_id, recurring_period_start, status, subtotal, vat_amount, total, created_by ) SELECT ${tenantId}::uuid, ${input.customerId}::uuid, ${projectId}::uuid, ${currency}::text, ${dueDate}::date, ${notes}::text, ${null}::text, ${source}::text, ${parentInvoiceId}::uuid, ${recurringTemplateId}::uuid, ${recurringPeriodStart}::date, 'DRAFT'::text, ${String(totals.subtotal)}::numeric, '0'::numeric, ${String(totals.subtotal)}::numeric, ${actorId}::uuid WHERE EXISTS ( SELECT 1 FROM customers WHERE id = ${input.customerId}::uuid AND tenant_id = ${tenantId}::uuid )${projectGuard} RETURNING * ), ins_lines AS ( INSERT INTO invoice_lines ( invoice_id, tenant_id, description, quantity, unit_price, discount_pct, line_total, taxable, position ) SELECT i.id, ${tenantId}::uuid, v.description, v.quantity, v.unit_price, v.discount_pct, v.line_total, v.taxable, v.position FROM ins_invoice i CROSS JOIN (VALUES ${lineValues}) AS v(description, quantity, unit_price, discount_pct, line_total, taxable, position) RETURNING * ), ins_audit AS ( INSERT INTO audit_log (tenant_id, actor_id, actor_type, entity_type, entity_id, action, changes) SELECT ${tenantId}::uuid, ${actorId}::uuid, 'user'::text, 'invoice'::text, i.id, 'invoice.created'::text, ${null}::jsonb FROM ins_invoice i RETURNING 1 ), ins_tenant_audit AS ( INSERT INTO tenant_audit_log ( tenant_id, user_id, actor_name, actor_email, event_type, entity_type, entity_id, entity_label, metadata, ip_address, before_state, after_state ) SELECT ${tenantId}::uuid, ${actorId}::uuid, ${actorName}::text, ${actorEmail}::text, 'invoice.created'::text, 'invoice'::text, i.id, COALESCE(i.proforma_number, i.id::text), '{}'::jsonb, ${ipAddress}::text, ${null}::jsonb, jsonb_build_object('status', 'DRAFT', 'customerId', i.customer_id) FROM ins_invoice i RETURNING 1 ) SELECT i.id AS "id", i.tenant_id AS "tenantId", i.customer_id AS "customerId", i.project_id AS "projectId", i.invoice_number AS "invoiceNumber", i.proforma_number AS "proformaNumber", i.status AS "status", i.currency AS "currency", i.issue_date::text AS "issueDate", i.tax_issue_date::text AS "taxIssueDate", i.due_date::text AS "dueDate", i.vat_rate::text AS "vatRate", i.subtotal::text AS "subtotal", i.vat_amount::text AS "vatAmount", i.total::text AS "total", i.amount_paid::text AS "amountPaid", i.notes AS "notes", i.source AS "source", -- State-transition timestamps (sent_at/approved_at/tax_issued_at/paid_at/voided_at) -- are cast ::text and safe ONLY because they are NULL for a fresh DRAFT, so -- mapInvoice's ?.toISOString() short-circuits. Do NOT reuse this SELECT shape for a -- non-DRAFT row — a non-null ::text string would make ?.toISOString() throw. i.sent_at::text AS "sentAt", i.approved_at::text AS "approvedAt", i.approved_by AS "approvedBy", i.approval_note AS "approvalNote", i.rejection_reason AS "rejectionReason", i.rejection_notify_customer AS "rejectionNotifyCustomer", i.tax_issued_at::text AS "taxIssuedAt", i.paid_at::text AS "paidAt", i.external_id AS "externalId", i.external_provider AS "externalProvider", i.void_reason AS "voidReason", i.voided_at::text AS "voidedAt", i.voided_by AS "voidedBy", i.parent_invoice_id AS "parentInvoiceId", i.html_snapshot_url AS "htmlSnapshotUrl", i.created_by AS "createdBy", to_char(i.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS "createdAt", to_char(i.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS "updatedAt", l.id AS "ln_id", l.invoice_id AS "ln_invoiceId", l.tenant_id AS "ln_tenantId", l.description AS "ln_description", l.quantity::text AS "ln_quantity", l.unit_price::text AS "ln_unitPrice", l.discount_pct::text AS "ln_discountPct", l.line_total::text AS "ln_lineTotal", l.taxable AS "ln_taxable", l.position AS "ln_position", to_char(l.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS "ln_createdAt" FROM ins_invoice i CROSS JOIN ins_lines l ORDER BY l.position `)) as unknown as Array> if (rows.length === 0) { // Guard failed (0 rows inserted). Reproduce the EXACT throw createInvoiceInTx // raises, preserving customer-before-project field precedence. assertTenantOwnsOrThrow( 'customerId', await assertTenantOwnsCustomer(db, tenantId, input.customerId), ) assertTenantOwnsOrThrow( 'projectId', await assertTenantOwnsProject(db, tenantId, input.projectId), ) throw new Error('Invoice insert failed') } const head = rows[0]! const invoiceRow = { ...head, createdAt: new Date(head.createdAt as string), updatedAt: new Date(head.updatedAt as string), } as unknown as typeof invoices.$inferSelect const lineRows = rows.map( (r) => ({ id: r.ln_id, invoiceId: r.ln_invoiceId, tenantId: r.ln_tenantId, description: r.ln_description, quantity: r.ln_quantity, unitPrice: r.ln_unitPrice, discountPct: r.ln_discountPct, lineTotal: r.ln_lineTotal, taxable: r.ln_taxable, position: r.ln_position, createdAt: new Date(r.ln_createdAt as string), }) as unknown as typeof invoiceLines.$inferSelect, ) return { ...mapInvoice(invoiceRow), lines: lineRows.map(mapLine), skipped: false } } export async function updateInvoice( db: Db, tenantId: string, id: string, actorId: string, patch: z.infer, actorCtx?: InvoiceActorContext, ): Promise { return db.transaction(async (tx) => { const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) if (!existing) throw new Error('Invoice not found') if (existing.status !== 'DRAFT' && existing.status !== 'REJECTED') { throw new Error('Only DRAFT or REJECTED invoices can be edited') } if (patch.customerId !== undefined) { assertTenantOwnsOrThrow( 'customerId', await assertTenantOwnsCustomer(tx, tenantId, patch.customerId), ) } if (patch.projectId !== undefined) { assertTenantOwnsOrThrow( 'projectId', await assertTenantOwnsProject(tx, tenantId, patch.projectId), ) } let subtotal = existing.subtotal let total = existing.total if (patch.lines) { // Replace lines await tx.delete(invoiceLines).where(eq(invoiceLines.invoiceId, id)) const parsedLines = patch.lines.map((l) => ({ ...l, lineTotal: Math.round(l.quantity * l.unitPrice * (1 - l.discountPct / 100) * 100) / 100, })) const totals = computeTotals(parsedLines, 0) subtotal = String(totals.subtotal) total = String(totals.subtotal) await tx .insert(invoiceLines) .values( parsedLines.map((l) => ({ invoiceId: id, tenantId, description: l.description, quantity: String(l.quantity), unitPrice: String(l.unitPrice), discountPct: String(l.discountPct), lineTotal: String(l.lineTotal), taxable: l.taxable, position: l.position, })), ) } const [updated] = await tx .update(invoices) .set({ customerId: patch.customerId, projectId: patch.projectId ?? undefined, currency: patch.currency, dueDate: patch.dueDate ?? undefined, notes: patch.notes ?? undefined, subtotal, vatAmount: '0', total, updatedAt: new Date(), }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() const lines = await tx .select() .from(invoiceLines) .where(eq(invoiceLines.invoiceId, id)) .orderBy(invoiceLines.position) await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.updated', changes: null, }) // operational-audit-trail: capture field diff const beforeRecord: Record = { customerId: existing.customerId, projectId: existing.projectId, currency: existing.currency, dueDate: existing.dueDate, notes: existing.notes, } const afterRecord: Record = { customerId: updated!.customerId, projectId: updated!.projectId, currency: updated!.currency, dueDate: updated!.dueDate, notes: updated!.notes, } const diff = computeDiff(beforeRecord, afterRecord) if (diff) { await captureEntityChange({ tx, tenantId, userId: actorId, actorName: actorCtx?.actorName ?? null, actorEmail: actorCtx?.actorEmail ?? null, eventType: 'invoice.field_updated', entityType: 'invoice', entityId: id, entityLabel: updated!.proformaNumber ?? updated!.invoiceNumber ?? id, beforeState: diff.before, afterState: diff.after, ipAddress: actorCtx?.ipAddress ?? null, }) } return { ...mapInvoice(updated!), lines: lines.map(mapLine) } }) } export async function deleteDraftInvoice( db: Db, tenantId: string, id: string, actorId: string, ): Promise { return db.transaction(async (tx) => { const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) if (!existing) throw new Error('Invoice not found') if (existing.status !== 'DRAFT') { throw new Error('Only DRAFT invoices can be deleted') } await tx.delete(invoices).where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.deleted', changes: null, }) }) } /** * DRAFT → SENT: assigns proforma number atomically. * Stores issue_date = today, vat_rate from vat_rates. * countryCode typically sourced from tenant settings. */ export async function sendInvoice( db: Db, tenantId: string, id: string, actorId: string, countryCode: string, issueDate: string, actorCtx?: InvoiceActorContext, ): Promise { return db.transaction(async (tx) => { const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) .for('update') if (!existing) throw new Error('Invoice not found') if (existing.source === 'credit_note') { throw new Error('Cannot send a credit note — use issue credit note instead') } if (existing.status !== 'DRAFT' && existing.status !== 'REJECTED') { throw new Error(`Cannot send invoice in status ${existing.status}`) } // Fetch VAT rate at issue date const vatRate = await getVatRateForDate(tx, countryCode, issueDate) // Recompute totals with VAT const lines = await tx .select() .from(invoiceLines) .where(eq(invoiceLines.invoiceId, id)) const parsedLines = lines.map((l) => ({ quantity: parseFloat(l.quantity), unitPrice: parseFloat(l.unitPrice), discountPct: parseFloat(l.discountPct), taxable: l.taxable, })) const totals = computeTotals(parsedLines, vatRate ? parseFloat(vatRate) : 0) assertPositiveSalesInvoiceTotals(existing.source, totals, 'send invoice') // Assign proforma number atomically in same transaction const proformaNumber = await nextInvoiceNumber(tx, tenantId, 'proforma') const [updated] = await tx .update(invoices) .set({ status: 'SENT', proformaNumber, issueDate, vatRate: vatRate ?? null, subtotal: String(totals.subtotal), vatAmount: String(totals.vatAmount), total: String(totals.total), sentAt: new Date(), updatedAt: new Date(), }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.sent', changes: null, }) // operational-audit-trail: status change await captureEntityChange({ tx, tenantId, userId: actorId, actorName: actorCtx?.actorName ?? null, actorEmail: actorCtx?.actorEmail ?? null, eventType: 'invoice.status_changed', entityType: 'invoice', entityId: id, entityLabel: proformaNumber ?? id, beforeState: { status: existing.status }, afterState: { status: 'SENT' }, ipAddress: actorCtx?.ipAddress ?? null, }) return mapInvoice(updated!) }) } /** * SENT → APPROVED */ export async function approveInvoice( db: Db, tenantId: string, id: string, actorId: string, actorCtx?: InvoiceActorContext, ): Promise { return db.transaction(async (tx) => { const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) .for('update') if (!existing) throw new Error('Invoice not found') if (existing.source === 'credit_note') { throw new Error('Cannot approve a credit note') } if (existing.status !== 'SENT') { throw new Error(`Cannot approve invoice in status ${existing.status}`) } const [updated] = await tx .update(invoices) .set({ status: 'APPROVED', approvedAt: new Date(), updatedAt: new Date() }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.approved', changes: null, }) // operational-audit-trail: status change await captureEntityChange({ tx, tenantId, userId: actorId, actorName: actorCtx?.actorName ?? null, actorEmail: actorCtx?.actorEmail ?? null, eventType: 'invoice.status_changed', entityType: 'invoice', entityId: id, entityLabel: updated!.proformaNumber ?? updated!.invoiceNumber ?? id, beforeState: { status: existing.status }, afterState: { status: 'APPROVED' }, ipAddress: actorCtx?.ipAddress ?? null, }) return mapInvoice(updated!) }) } /** * SENT → REJECTED */ export async function rejectInvoice( db: Db, tenantId: string, id: string, actorId: string, reason: string, actorCtx?: InvoiceActorContext, ): Promise { return db.transaction(async (tx) => { const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) .for('update') if (!existing) throw new Error('Invoice not found') if (existing.source === 'credit_note') { throw new Error('Cannot reject a credit note') } if (existing.status !== 'SENT') { throw new Error(`Cannot reject invoice in status ${existing.status}`) } const [updated] = await tx .update(invoices) .set({ status: 'REJECTED', notes: existing.notes ? `${existing.notes}\n\nRejection reason: ${reason}` : `Rejection reason: ${reason}`, updatedAt: new Date(), }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.rejected', changes: null, }) // operational-audit-trail: status change await captureEntityChange({ tx, tenantId, userId: actorId, actorName: actorCtx?.actorName ?? null, actorEmail: actorCtx?.actorEmail ?? null, eventType: 'invoice.status_changed', entityType: 'invoice', entityId: id, entityLabel: updated!.proformaNumber ?? updated!.invoiceNumber ?? id, beforeState: { status: existing.status }, afterState: { status: 'REJECTED', reason }, ipAddress: actorCtx?.ipAddress ?? null, }) return mapInvoice(updated!) }) } export type IssueTaxInvoiceTxOpts = { htmlSnapshotUrl?: string actorCtx?: InvoiceActorContext /** Status precondition; defaults to APPROVED-only (issue-tax route behavior). */ allowedStatuses?: string[] } /** * Tx-composable APPROVED → TAX_ISSUED (or allowedStatuses): assigns invoice number, * locks VAT at taxIssueDate, recomputes totals. Caller owns the transaction. */ export async function issueTaxInvoiceTx( tx: DbTx, tenantId: string, id: string, actorId: string, taxIssueDate: string, countryCode: string, opts?: IssueTaxInvoiceTxOpts, ): Promise { const allowedStatuses = opts?.allowedStatuses ?? ['APPROVED'] const htmlSnapshotUrl = opts?.htmlSnapshotUrl const actorCtx = opts?.actorCtx const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) .for('update') if (!existing) throw new Error('Invoice not found') if (!allowedStatuses.includes(existing.status)) { throw new Error(`Cannot issue tax invoice in status ${existing.status}`) } const lines = await tx .select() .from(invoiceLines) .where(eq(invoiceLines.invoiceId, id)) const parsedLines = lines.map((l) => ({ quantity: parseFloat(l.quantity), unitPrice: parseFloat(l.unitPrice), discountPct: parseFloat(l.discountPct), taxable: l.taxable, })) const vatRateStr = await getVatRateForDate(tx, countryCode, taxIssueDate) const vatRate = vatRateStr ? parseFloat(vatRateStr) : 0 const totals = computeTotals(parsedLines, vatRate) assertPositiveSalesInvoiceTotals(existing.source, totals, 'issue tax invoice') // Assign invoice number atomically in same transaction const invoiceNumber = await nextInvoiceNumber(tx, tenantId, 'invoice') const [updated] = await tx .update(invoices) .set({ status: 'TAX_ISSUED', invoiceNumber, taxIssueDate, vatRate: vatRateStr ?? null, subtotal: String(totals.subtotal), vatAmount: String(totals.vatAmount), total: String(totals.total), taxIssuedAt: new Date(), htmlSnapshotUrl: htmlSnapshotUrl ?? null, updatedAt: new Date(), }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.tax_issued', changes: null, }) // operational-audit-trail: status change await captureEntityChange({ tx, tenantId, userId: actorId, actorName: actorCtx?.actorName ?? null, actorEmail: actorCtx?.actorEmail ?? null, eventType: 'invoice.status_changed', entityType: 'invoice', entityId: id, entityLabel: invoiceNumber, beforeState: { status: existing.status }, afterState: { status: 'TAX_ISSUED', invoiceNumber }, ipAddress: actorCtx?.ipAddress ?? null, }) return mapInvoice(updated!) } /** * APPROVED → TAX_ISSUED: assigns invoice number atomically. * VAT rate is looked up at taxIssueDate (Israeli tax point) and totals recomputed. * htmlSnapshotUrl may be set later once the issued-doc HTML is stored in R2. */ export async function issueTaxInvoice( db: Db, tenantId: string, id: string, actorId: string, taxIssueDate: string, countryCode: string, htmlSnapshotUrl?: string, actorCtx?: InvoiceActorContext, ): Promise { return db.transaction(async (tx) => issueTaxInvoiceTx(tx, tenantId, id, actorId, taxIssueDate, countryCode, { htmlSnapshotUrl, actorCtx, }), ) } /** * TAX_ISSUED → PAID (or PARTIALLY_PAID if partial) */ export async function recordPayment( db: Db, tenantId: string, id: string, actorId: string, input: z.infer, ): Promise { return db.transaction(async (tx) => { const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) .for('update') if (!existing) throw new Error('Invoice not found') const allowed = ['TAX_ISSUED', 'PARTIALLY_PAID'] if (!allowed.includes(existing.status)) { throw new Error(`Cannot record payment for invoice in status ${existing.status}`) } const prevPaid = parseFloat(existing.amountPaid) const newPaid = Math.round((prevPaid + input.amount) * 100) / 100 const totalAmount = parseFloat(existing.total) if (newPaid > totalAmount + 0.01) { throw new Error( `Payment of ${input.amount.toFixed(2)} exceeds remaining balance ${(totalAmount - prevPaid).toFixed(2)}`, ) } const newStatus: InvoiceStatus = newPaid >= totalAmount ? 'PAID' : 'PARTIALLY_PAID' const [updated] = await tx .update(invoices) .set({ status: newStatus, amountPaid: String(newPaid), paidAt: newStatus === 'PAID' ? new Date() : null, updatedAt: new Date(), }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.payment_recorded', changes: null, }) return mapInvoice(updated!) }) } // ── Credit Note Error ───────────────────────────────────────────────────────── /** Typed error for credit-note business-rule violations. Maps to HTTP 422 or 409. */ export class CreditNoteError extends Error { constructor( public readonly code: | 'PARENT_NOT_CREDITABLE' | 'CREDIT_EXCEEDS_PARENT' | 'REASON_REQUIRED' | 'NOT_DRAFT' | 'DUPLICATE_DRAFT', message: string, ) { super(message) this.name = 'CreditNoteError' } } // ── Credit-note input schemas ───────────────────────────────────────────────── export const creditNoteLineInputSchema = z .object({ description: z.string().min(1).max(2000), quantity: z.number().refine((value) => value !== 0, 'quantity cannot be zero'), unitPrice: z.number().refine((value) => value !== 0, 'unit price cannot be zero'), discountPct: z.number().min(0).max(100).optional().default(0), taxable: z.boolean().optional().default(true), }) .refine( (line) => line.quantity * line.unitPrice * (1 - (line.discountPct ?? 0) / 100) < 0, 'credit note lines must have a negative total', ) export const createCreditNoteDraftSchema = z .object({ mode: z.enum(['full', 'partial']), reason: z.string().trim().min(1), lines: z.array(creditNoteLineInputSchema).optional(), }) .refine((d) => d.mode === 'full' || (d.lines && d.lines.length > 0), { message: 'partial mode requires at least one line', }) const CREDITABLE_STATUSES = new Set([ 'SENT', 'APPROVED', 'TAX_ISSUED', 'PAID', ]) function assertCreditableParentRow(parent: Invoice): void { if (parent.source === 'credit_note') { throw new CreditNoteError( 'PARENT_NOT_CREDITABLE', 'Cannot create a credit note for another credit note', ) } if (!CREDITABLE_STATUSES.has(parent.status)) { throw new CreditNoteError( 'PARENT_NOT_CREDITABLE', `Parent invoice must be in a creditable status (current: ${parent.status})`, ) } } function assertCreditWithinParentAmounts(creditTotal: number, remainingCreditable: number): void { if (Math.abs(creditTotal) > Math.abs(remainingCreditable) + 0.001) { throw new CreditNoteError( 'CREDIT_EXCEEDS_PARENT', `Credit total (${creditTotal}) exceeds remaining creditable amount (${remainingCreditable})`, ) } } function assertNegativeCreditTotal(total: number): void { if (!(total < 0)) { throw new CreditNoteError('CREDIT_EXCEEDS_PARENT', 'Credit note total must be negative') } } /** Lock parent invoice row and sum issued credits (S9-i2-005). */ async function lockParentAndGetRemainingCreditable( tx: DbTx, tenantId: string, parentId: string, ): Promise { const parentRows = await tx.execute(sql` SELECT total::numeric AS total FROM invoices WHERE tenant_id = ${tenantId}::uuid AND id = ${parentId}::uuid FOR UPDATE `) const parentRow = parentRows[0] as { total: string } | undefined if (!parentRow) throw new NotFoundError('Parent invoice not found') const creditedRows = await tx.execute(sql` SELECT COALESCE(SUM(ABS(total::numeric)), 0) AS credited FROM invoices WHERE tenant_id = ${tenantId}::uuid AND parent_invoice_id = ${parentId}::uuid AND source = 'credit_note' AND status IN ('TAX_ISSUED', 'PAID', 'PARTIALLY_PAID') `) const creditedRow = creditedRows[0] as { credited: string } | undefined const alreadyCredited = parseFloat(creditedRow?.credited ?? '0') const parentTotal = Math.abs(parseFloat(parentRow.total)) return -(parentTotal - alreadyCredited) } type CreditLineBuilt = { description: string quantity: string unitPrice: string discountPct: string lineTotal: string taxable: boolean position: number } function buildCreditLines( parentLines: typeof invoiceLines.$inferSelect[], mode: 'full' | 'partial', customLines?: z.infer[], ): CreditLineBuilt[] { if (mode === 'full') { return parentLines.map((l) => ({ description: l.description, quantity: String(-parseFloat(l.quantity)), unitPrice: l.unitPrice, discountPct: l.discountPct, lineTotal: String(-parseFloat(l.lineTotal)), taxable: l.taxable, position: l.position, })) } return (customLines ?? []).map((l, i) => { const disc = l.discountPct ?? 0 const lt = l.quantity * l.unitPrice * (1 - disc / 100) return { description: l.description, quantity: String(l.quantity), unitPrice: String(l.unitPrice), discountPct: String(disc), lineTotal: String(Math.round(lt * 100) / 100), taxable: l.taxable ?? true, position: i, } }) } /** * Create a DRAFT credit note for a parent invoice. * Number NOT assigned here — assigned only at issueCreditNote() (gap-free). */ export async function createCreditNoteDraft( db: Db, tenantId: string, parentId: string, actorId: string, input: z.infer, ): Promise { return db.transaction(async (tx) => { const [parent] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, parentId))) .limit(1) .for('update') if (!parent) throw new NotFoundError('Parent invoice not found') assertCreditableParentRow(mapInvoice(parent)) if (!input.reason.trim()) { throw new CreditNoteError('REASON_REQUIRED', 'A reason is required') } const [existingDraft] = await tx .select({ id: invoices.id }) .from(invoices) .where( and( eq(invoices.tenantId, tenantId), eq(invoices.parentInvoiceId, parentId), eq(invoices.source, 'credit_note'), eq(invoices.status, 'DRAFT'), ), ) .limit(1) if (existingDraft) { throw new CreditNoteError('DUPLICATE_DRAFT', 'A draft credit note already exists for this invoice') } const parentLines = await tx .select() .from(invoiceLines) .where(eq(invoiceLines.invoiceId, parentId)) .orderBy(invoiceLines.position) const creditLines = buildCreditLines(parentLines, input.mode, input.lines) const vatRate = parent.vatRate ? parseFloat(parent.vatRate) : 0 const totals = computeTotals( creditLines.map((l) => ({ quantity: parseFloat(l.quantity), unitPrice: parseFloat(l.unitPrice), discountPct: parseFloat(l.discountPct), taxable: l.taxable, })), vatRate, ) assertNegativeCreditTotal(totals.total) const remaining = await lockParentAndGetRemainingCreditable(tx, tenantId, parentId) assertCreditWithinParentAmounts(totals.total, remaining) const [creditNote] = await tx .insert(invoices) .values({ tenantId, customerId: parent.customerId, projectId: parent.projectId ?? null, currency: parent.currency, vatRate: parent.vatRate ?? null, subtotal: String(totals.subtotal), vatAmount: String(totals.vatAmount), total: String(totals.total), source: 'credit_note', status: 'DRAFT', parentInvoiceId: parent.id, notes: input.reason, createdBy: actorId, }) .returning() if (!creditNote) throw new Error('Credit note insert failed') const insertedLines = await tx .insert(invoiceLines) .values( creditLines.map((l) => ({ invoiceId: creditNote.id, tenantId, description: l.description, quantity: l.quantity, unitPrice: l.unitPrice, discountPct: l.discountPct, lineTotal: l.lineTotal, taxable: l.taxable, position: l.position, })), ) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: creditNote.id, action: 'invoice.created', changes: null, }) return { ...mapInvoice(creditNote), lines: insertedLines.map(mapLine) } }) } /** * Issue a DRAFT credit note: DRAFT → TAX_ISSUED. * Assigns CN-##### number atomically in the same transaction (gap-free). */ export async function issueCreditNote( db: Db, tenantId: string, creditNoteId: string, actorId: string, ): Promise { return db.transaction(async (tx) => { const [creditNote] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, creditNoteId))) .limit(1) .for('update') if (!creditNote) throw new NotFoundError('Credit note not found') if (creditNote.source !== 'credit_note') { throw new CreditNoteError('NOT_DRAFT', 'Invoice is not a credit note') } if (creditNote.status !== 'DRAFT') { throw new CreditNoteError('NOT_DRAFT', `Credit note is already in status ${creditNote.status}`) } if (!creditNote.notes?.trim()) { throw new CreditNoteError('REASON_REQUIRED', 'A reason is required to issue a credit note') } if (creditNote.parentInvoiceId) { assertNegativeCreditTotal(parseFloat(creditNote.total)) const remaining = await lockParentAndGetRemainingCreditable( tx, tenantId, creditNote.parentInvoiceId, ) assertCreditWithinParentAmounts(parseFloat(creditNote.total), remaining) } const creditNoteNumber = await nextInvoiceNumber(tx, tenantId, 'credit_note') const today = new Date().toISOString().slice(0, 10) const [issued] = await tx .update(invoices) .set({ status: 'TAX_ISSUED', invoiceNumber: creditNoteNumber, taxIssueDate: today, taxIssuedAt: new Date(), updatedAt: new Date(), }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, creditNoteId))) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: creditNoteId, action: 'invoice.status_changed', changes: { status: ['DRAFT', 'TAX_ISSUED'], invoice_number: [null, creditNoteNumber], } as Record, }) return mapInvoice(issued!) }) } /** * List all credit notes for a parent invoice + compute netInvoiced. * netInvoiced = parent.total + SUM(credit_note.total) */ export async function listCreditNotesForParent( db: Db, tenantId: string, parentId: string, ): Promise<{ items: Invoice[]; netInvoiced: number }> { const [parent] = await db .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, parentId))) .limit(1) if (!parent) throw new NotFoundError('Parent invoice not found') const items = await db .select() .from(invoices) .where( and( eq(invoices.tenantId, tenantId), eq(invoices.parentInvoiceId, parentId), eq(invoices.source, 'credit_note'), ), ) .orderBy(invoices.createdAt) const creditSum = items.reduce((sum, cn) => sum + parseFloat(cn.total), 0) const netInvoiced = Math.round((parseFloat(parent.total) + creditSum) * 100) / 100 return { items: items.map(mapInvoice), netInvoiced } } /** * Guard: an issued credit note cannot be deleted. */ export function assertDeletableCreditNote(invoice: Invoice): void { if (invoice.source === 'credit_note' && invoice.status !== 'DRAFT') { throw new CreditNoteError( 'NOT_DRAFT', 'Issued credit notes cannot be deleted — only DRAFT credit notes can be discarded', ) } } /** * @deprecated Use createCreditNoteDraft + issueCreditNote instead. */ export async function createCreditNote( db: Db, tenantId: string, id: string, actorId: string, _countryCode: string, ): Promise { const draft = await createCreditNoteDraft(db, tenantId, id, actorId, { mode: 'full', reason: 'Credit note', lines: undefined, }) const issued = await issueCreditNote(db, tenantId, draft.id, actorId) return { ...issued, lines: draft.lines } } /** * DRAFT | SENT → VOID * Cannot void TAX_ISSUED or later (must use credit note instead). * Requires privileged actor (enforced at route layer). */ export async function voidInvoiceTx( tx: DbTx, tenantId: string, id: string, actorId: string, reason: string, actorCtx?: InvoiceActorContext, ): Promise { const [existing] = await tx .select() .from(invoices) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .limit(1) .for('update') if (!existing) throw new Error('Invoice not found') const voidable = ['DRAFT', 'SENT'] if (!voidable.includes(existing.status)) { if (existing.status === 'APPROVED' || existing.status === 'REJECTED') { throw new ConflictError( `Cannot void invoice in status ${existing.status} — edit the invoice or issue a credit note instead`, ) } const taxIssuedOrLater = ['TAX_ISSUED', 'PAID', 'PARTIALLY_PAID', 'WRITTEN_OFF', 'BAD_DEBT'] if (taxIssuedOrLater.includes(existing.status)) { throw new ConflictError( 'Cannot void a TAX_ISSUED or later invoice — use a credit note instead', ) } throw new ConflictError(`Cannot void invoice in status ${existing.status}`) } const [updated] = await tx .update(invoices) .set({ status: 'VOID', voidReason: reason, voidedAt: new Date(), voidedBy: actorId, updatedAt: new Date(), }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: id, action: 'invoice.voided', changes: null, }) await captureEntityChange({ tx, tenantId, userId: actorId, actorName: actorCtx?.actorName ?? null, actorEmail: actorCtx?.actorEmail ?? null, eventType: 'invoice.status_changed', entityType: 'invoice', entityId: id, entityLabel: updated!.invoiceNumber ?? updated!.proformaNumber ?? id, beforeState: { status: existing.status }, afterState: { status: 'VOID', reason }, ipAddress: actorCtx?.ipAddress ?? null, }) return mapInvoice(updated!) } export async function voidInvoice( db: Db, tenantId: string, id: string, actorId: string, reason: string, actorCtx?: InvoiceActorContext, ): Promise { return db.transaction((tx) => voidInvoiceTx(tx, tenantId, id, actorId, reason, actorCtx)) } /** * Internal: create DRAFT, assign invoice number, transition to TAX_ISSUED in one * transaction. Used by the billing module (auto_charge flow). Skips SENT/APPROVED steps. */ export async function autoIssueInvoice( db: Db, tenantId: string, actorId: string, input: z.infer, countryCode: string, ): Promise<{ invoiceId: string; invoiceNumber: string }> { return db.transaction(async (tx) => { assertTenantOwnsOrThrow( 'customerId', await assertTenantOwnsCustomer(tx, tenantId, input.customerId), ) assertTenantOwnsOrThrow( 'projectId', await assertTenantOwnsProject(tx, tenantId, input.projectId), ) const today = new Date().toISOString().slice(0, 10) const vatRate = await getVatRateForDate(tx, countryCode, today) const parsedLines = input.lines.map((l) => ({ ...l, lineTotal: Math.round(l.quantity * l.unitPrice * (1 - l.discountPct / 100) * 100) / 100, })) const totals = computeTotals(parsedLines, vatRate ? parseFloat(vatRate) : 0) const invoiceNumber = await nextInvoiceNumber(tx, tenantId, 'invoice') const invoiceAutoRows = await tx .insert(invoices) .values({ tenantId, customerId: input.customerId, projectId: input.projectId ?? null, currency: 'ILS', status: 'TAX_ISSUED', invoiceNumber, source: 'auto_charge', vatRate: vatRate ?? null, subtotal: String(totals.subtotal), vatAmount: String(totals.vatAmount), total: String(totals.total), issueDate: today, taxIssueDate: today, sentAt: new Date(), approvedAt: new Date(), taxIssuedAt: new Date(), createdBy: actorId, }) .returning() const invoice = invoiceAutoRows[0] if (!invoice) throw new Error('Auto-issue invoice insert failed') await tx .insert(invoiceLines) .values( parsedLines.map((l) => ({ invoiceId: invoice.id, tenantId, description: l.description, quantity: String(l.quantity), unitPrice: String(l.unitPrice), discountPct: String(l.discountPct), lineTotal: String(l.lineTotal), taxable: l.taxable, position: l.position, })), ) await tx.insert(auditLog).values({ tenantId, actorId, actorType: 'user', entityType: 'invoice', entityId: invoice.id, action: 'invoice.auto_issued', changes: null, }) return { invoiceId: invoice.id, invoiceNumber } }) } /** Persist R2 object key for immutable issued-doc HTML snapshot (served via authenticated API). */ export async function setInvoiceHtmlSnapshotUrl( db: Db, tenantId: string, id: string, htmlSnapshotUrl: string, ): Promise { const [updated] = await db .update(invoices) .set({ htmlSnapshotUrl, updatedAt: new Date() }) .where(and(eq(invoices.tenantId, tenantId), eq(invoices.id, id))) .returning() if (!updated) throw new Error('Invoice not found') return mapInvoice(updated) } // ── HTML escape helper (XSS prevention) ────────────────────────────────────── function esc(s: unknown): string { return String(s ?? '') .replace(/&/g, '&') .replace(//g, '>') .replace(/"/g, '"') .replace(/'/g, ''') } const ISO_DATE_ONLY_RE = /^(\d{4})-(\d{2})-(\d{2})$/ /** Format a PostgreSQL DATE (YYYY-MM-DD) using calendar parts — no local-TZ day shift. */ function formatCalendarDate( dateStr: string, locale: string, options: Intl.DateTimeFormatOptions, ): string { const match = ISO_DATE_ONLY_RE.exec(dateStr) if (match) { const year = Number(match[1]) const month = Number(match[2]) const day = Number(match[3]) return new Intl.DateTimeFormat(locale, { ...options, timeZone: 'UTC' }).format( new Date(Date.UTC(year, month - 1, day)), ) } return new Intl.DateTimeFormat(locale, options).format(new Date(dateStr)) } /** * Render invoice to HTML for print-to-PDF. * RTL/Hebrew support per spec. * When locale=he-IL, dir="rtl"; otherwise dir="ltr". */ export function renderInvoiceHtml( invoice: InvoiceWithLines, opts: { tenantName: string tenantTaxId?: string tenantAddress?: string customerName: string customerTaxId?: string customerAddress?: string locale?: string r2PublicUrl?: string }, ): string { const isHebrew = opts.locale === 'he-IL' const dir = isHebrew ? 'rtl' : 'ltr' const lang = isHebrew ? 'he' : 'en' const fontUrl = isHebrew && opts.r2PublicUrl ? `${opts.r2PublicUrl.replace(/\/$/, '')}/_static/fonts/heebo-variable.woff2` : null const fontFaceBlock = fontUrl ? `@font-face { font-family: 'Heebo'; src: url('${fontUrl}') format('woff2'); font-display: block; font-weight: 100 900; }` : '' const fontImportBlock = isHebrew && !fontUrl ? `@import url('https://fonts.googleapis.com/css2?family=Heebo:wght@400;500;700&display=swap');` : '' const formatCurrency = (val: string | number) => { const n = typeof val === 'string' ? parseFloat(val) : val return new Intl.NumberFormat(opts.locale ?? 'en-US', { style: 'currency', currency: invoice.currency, }).format(n) } const dateFormatOptions = { year: 'numeric', month: 'long', day: 'numeric', } as const satisfies Intl.DateTimeFormatOptions const formatDate = (dateStr: string | null) => { if (!dateStr) return '—' return formatCalendarDate(dateStr, opts.locale ?? 'en-US', dateFormatOptions) } const title = isHebrew ? invoice.invoiceNumber ? `חשבונית מס ${invoice.invoiceNumber}` : `חשבונית עסקה ${invoice.proformaNumber ?? ''}` : invoice.invoiceNumber ? `Tax Invoice ${invoice.invoiceNumber}` : `Invoice ${invoice.proformaNumber ?? ''}` const linesHtml = invoice.lines .map( (l) => `
${esc(l.description)}
${l.quantity}
${formatCurrency(l.unitPrice)}
${l.discountPct}%
${formatCurrency(l.lineTotal)}
`, ) .join('') return ` ${title}

${title}

${esc(opts.tenantName)}
${opts.tenantTaxId ? `${isHebrew ? 'עוסק מורשה/ח.פ.' : 'Tax ID'} ${esc(opts.tenantTaxId)}
` : ''} ${opts.tenantAddress ? `${esc(opts.tenantAddress)}
` : ''}
${esc(opts.customerName)}
${opts.customerTaxId ? `${isHebrew ? 'עוסק מורשה/ח.פ.' : 'Tax ID'} ${esc(opts.customerTaxId)}
` : ''} ${opts.customerAddress ? `${esc(opts.customerAddress)}
` : ''}
${isHebrew ? 'תאריך הנפקה' : 'Issue Date'}: ${formatDate(invoice.issueDate ?? invoice.taxIssueDate)}
${invoice.dueDate ? `${isHebrew ? 'תאריך פירעון' : 'Due Date'}: ${formatDate(invoice.dueDate)}
` : ''} ${invoice.currency} ${invoice.currency !== 'ILS' ? '' : ''}
${isHebrew ? 'תיאור' : 'Description'}
${isHebrew ? 'כמות' : 'Qty'}
${isHebrew ? 'מחיר יחידה' : 'Unit Price'}
${isHebrew ? 'הנחה' : 'Discount'}
${isHebrew ? 'סה"כ' : 'Total'}
${linesHtml}
${isHebrew ? 'סכום לפני מע"מ' : 'Subtotal'}
${formatCurrency(invoice.subtotal)}
${ invoice.vatRate ? `
${isHebrew ? `מע"מ (${(parseFloat(invoice.vatRate) * 100).toFixed(0)}%)` : `VAT (${(parseFloat(invoice.vatRate) * 100).toFixed(0)}%)`}
${formatCurrency(invoice.vatAmount)}
` : '' }
${isHebrew ? 'סה"כ לתשלום' : 'Total Due'}
${formatCurrency(invoice.total)}
${invoice.notes ? `

${esc(invoice.notes).replace(/\n/g, '
')}

` : ''} ` } // ── Error types ─────────────────────────────────────────────────────────────── export class ConflictError extends Error { readonly statusCode = 409 constructor(message: string) { super(message) this.name = 'ConflictError' } } export class NotFoundError extends Error { readonly statusCode = 404 constructor(message = 'Not found') { super(message) this.name = 'NotFoundError' } }