Backend Type Safety Hardening — RLS Centralisation, Admin Typing & Service Layer Cleanup

Why This Matters

Type safety is not cosmetic. In accounting software, an untyped database access is a latent pathway to data corruption, tenant leakage, or silent miscalculation. SpeyBooks was built rapidly with correct runtime behaviour, but the TypeScript type system was not fully leveraged to enforce that correctness at compile time.

This release is a systematic hardening pass across the entire backend. Every any cast was examined for what it concealed — and in most cases, the underlying code was correct but the types were not proving it. The goal: make the compiler verify what was previously only guaranteed by developer discipline.

RLS Centralisation

• System-level queries now flow through a single controlled channel Cross-tenant operations (consensus evaluation, global rule lookups, health checks) previously accessed the database pool directly. All system-level queries now route through a centralised module with structured logging and background query support.

• Standalone jobs documented as architectural exceptions Scheduled tasks that run outside the API server lifecycle are now explicitly annotated, distinguishing intentional direct pool access from accidental bypass.

• Consensus evaluation engine fully migrated The TMADD 2.0 batch evaluation pipeline — which aggregates categorisation votes across all tenants — now uses the system query pattern exclusively, with pool parameter threading removed from all seven internal functions.

Admin Route Typing

• 57 type assertion casts eliminated from admin routes Every (request as any).dbClient, (request as any).user.id, and (request as any).organisationId cast across admin, billing, and verification routes has been replaced with direct typed access via the Fastify declaration layer.

• GDPR cascade deletes migrated to system client Hard-delete operations for users and organisations now use the system client wrapper with automatic transaction management, replacing manual pool connection handling that was typed as any.

• Cache flush operations properly guarded The admin cache-clear endpoint now uses runtime type narrowing instead of unchecked method calls, handling both Map and node-cache implementations safely.

• Pool statistics accessed through the correct interface Admin health diagnostics now read connection pool metrics from the server-level pool instance rather than attempting to access pool properties through a request-scoped client.

Service Layer Typing

• Row-level interfaces introduced for database results Invoice and invoice line queries now return typed row interfaces that match the exact PostgreSQL column shapes. This is the foundation for a systematic snake_case-to-camelCase translation layer.

• Migration engine fully typed All TMADD 7.0 gate checks, state machine transitions, proof assembly, and execution functions now accept typed database clients instead of untyped parameters.

• Rate limiting, duplicate detection, and seed rules typed Utility modules across the library and service layers now use typed Fastify request objects and database clients, eliminating implicit any at function boundaries.

• Error handling standardised Catch blocks across the CSV parser, migration executor, and contact import service now use unknown with instanceof Error guards instead of any, preventing untyped error property access.

Invoice Number Integrity

• Unique constraint added per organisation A compound unique constraint on organisation and invoice number prevents duplicate invoice numbers under concurrent creation. The original global constraint was correctly removed in an earlier migration; this restores the protection at the correct scope.

Operational Impact

• Type safety violations reduced from 300 to 125 (58% elimination)
• Zero direct pool access in request-scoped code paths (excluding documented standalone jobs)
• All admin routes compile-time verified against Fastify declaration types
• Invoice number uniqueness enforced at the database level
• System-level database access consolidated to a single auditable module

Files Changed

Backend:

• api/src/db/system.ts — centralised system query module with background query support
• api/src/db/index.ts — health check migrated to system query
• api/src/lib/consensus-evaluator.ts — full migration to system query, pool threading removed
• api/src/routes/categorisation-rules.ts — typed database client, system query for global rules
• api/src/routes/admin.ts — 20 type assertion casts removed, pool stats corrected
• api/src/routes/admin-additions.ts — 26 casts removed, GDPR deletes migrated to system client
• api/src/routes/admin-bug-reports.ts — 5 casts removed
• api/src/routes/billing.ts — 3 casts removed
• api/src/routes/email-verification.ts — 2 casts removed
• api/src/routes/transparency.ts — 1 cast removed
• api/src/services/invoice-service.ts — row interfaces, 18 field-level casts eliminated
• api/src/services/pdf-service.ts — address interface, 6 casts eliminated
• api/src/services/mwce/gates.ts — typed database clients across 8 gate functions
• api/src/services/mwce/state-machine.ts — typed database client
• api/src/services/mwce/proof.ts — typed database client
• api/src/services/mwce/executor.ts — typed database client, error narrowing
• api/src/services/seed-rules.ts — typed database client
• api/src/services/contact-import-service.ts — typed query result
• api/src/lib/auth-rate-limits.ts — typed request objects across 6 rate limiters
• api/src/lib/metadata.ts — typed parameter arrays
• api/src/lib/bank-import/duplicate-detection.ts — typed database client
• api/src/lib/bank-import/csv-parser.ts — error narrowing
• api/src/scripts/run-consensus.ts — system database initialisation for standalone execution
• api/src/middleware/idempotency.ts — single-winner claim pattern, stale detection
• api/src/types/fastify.d.ts — canonical request augmentation declarations
• api/db/migrations/v5.9.6-unique-invoice-number.sql — compound unique constraint

Known Issues

• 125 type violations remain across route handlers and import pipelines. These are predominantly untyped handler parameters and untyped database result mapping — symptoms of the snake_case/camelCase boundary between PostgreSQL and TypeScript. A systematic translation layer is being designed to resolve these at the architectural level rather than individually.

• The snake_case-to-camelCase translation currently happens in route handlers. Services return raw database rows and routes perform the mapping. The target architecture moves this translation into the service layer via typed mapper functions, allowing routes to receive camelCase domain objects directly.

This release establishes the type safety foundation for the data boundary redesign that will complete the remaining violations architecturally.