Skip to content

Database Abstraction Plan

Living document — updated after each refactoring step. Last updated: 2026-03-13

calServer V2 supports three database backends equally: MySQL, PostgreSQL, Microsoft SQL Server.

1. Audit Results

1.1 Backend — DB::raw(), DB::statement(), DB::select(), DB::unprepared()

# File Line Expression Category Status
1 app/Console/Commands/DemoData.php 384–401 DB::statement() for FK checks (mysql/pgsql/sqlite) Trivial DONE — Refactored to DbDialect::disableForeignKeyChecks() / enableForeignKeyChecks()
2 app/Services/DemoDataService.php 751–767 DB::statement() for FK checks (mysql/sqlite only) Trivial DONE — Refactored to DbDialect, added pgsql + sqlsrv support
3 app/Services/DocumentVersioningService.php 103 DB::select() with raw LIMIT 1 Medium DONE — Refactored to Query Builder
4 app/Services/InboxProcessingService.php 317 DB::select() with raw LIMIT 2 + CONCAT_WS Complex DONE — Refactored to Query Builder with DbDialect
5 database/seeders/DemoSetupSeeder.php 66 DB::unprepared() loading raw SQL files P3 Documented — SQL files use PostgreSQL ON CONFLICT DO NOTHING syntax. Multi-DB support requires per-driver SQL or migration to Eloquent upserts.

1.2 Migrations — DB-specific DDL

# File Line Issue Category Status
1 2026_03_13_030000_create_config_and_property_tables.php 36–42 DB::statement() for MySQL prefix-length index, with pgsql fallback OK Already handles mysql/pgsql. DONE — Added sqlsrv branch.
2 Multiple V2 migrations — FK columns defined as string(80) referencing uuid PKs → PostgreSQL type mismatch on joins P0 DONE — Fixed in original migration files

1.3 UUID FK type mismatch (fixed)

All V2 tables used uuid('id') for PKs but string('xxx_id', 80) for FK columns. PostgreSQL rejects varchar = uuid comparisons. Fixed directly in original migrations: - create_inventory_table: customer_id → uuid - create_calibration_table: inventory_id, customer_id → uuid - create_location_table: inventory_id, customer_id, delivery_customer_id → uuid - create_repair_table: inventory_id → uuid - create_booking_table: customer_id, delivery_customer_id, invoice_customer_id → uuid - create_results_table: inventory_id, calibration_id → uuid

1.4 No other issues found

  • No ENUM, SERIAL, or JSONB in migrations (all use Laravel Schema Builder types)
  • No DB::raw() in Controllers or Models
  • No ->enum() in any migration

2. Priority Classification

P0: Migrations with DB-specific types / type mismatches

  • UUID FK type mismatches — FK columns string(80) referencing uuid PKs → fixed in original migration files

P1: DB::raw() / DB::statement() in frequently used code paths (→ DbDialect)

  • DemoData.php — FK check toggle → DbDialect::disableForeignKeyChecks()
  • DemoDataService.php — FK check toggle → DbDialect::disableForeignKeyChecks()
  • DocumentVersioningService.php — raw SELECT → Query Builder
  • InboxProcessingService.php — raw SELECT with CONCAT → Query Builder + DbDialect

P2: Complex queries needing Repository pattern

  • None found yet. Current queries were simple enough for Query Builder + DbDialect.

P3: Edge cases and optimizations

  • DemoSetupSeeder.php — SQL files use ON CONFLICT DO NOTHING (PostgreSQL). For full multi-DB support, these should be converted to Laravel's upsert() or per-driver SQL files.
  • InboxProcessingService::resolveEntity() — CONCAT_WS with dynamic field lists built from patterns. Currently uses CONCAT_WS which works on all three DBs (MySQL natively, PgSQL since 9.1, MSSQL since 2017). Monitored for edge cases.

3. Frontend Audit

The frontend (frontend-v2/) is already database-agnostic:

  • Filter syntax: useDataTable.ts sends abstract filter descriptors (contains, startsWith, gt, lt, between, in) — no SQL fragments.
  • No DB engine detection: No references to mysql/postgres/sqlsrv anywhere in frontend code.
  • Field types: Generic types (varchar, int, float, date, datetime, select) — not DB-specific.
  • V1 column names (I4201, etc.): Used only as translation keys, not for DB queries.
  • Dual endpoint strategy: useFieldConfig.ts tries V2 endpoint first, falls back to V1 — transparent to DB backend.

No frontend changes required.

4. Test Strategy

Existing coverage

  • Feature tests use RefreshDatabase trait with SQLite in-memory by default
  • Tests are already DB-agnostic via Eloquent/Query Builder
  • CI matrix testing against MySQL, PostgreSQL, and MSSQL (when infrastructure allows)
  • Unit tests for DbDialect methods verifying correct SQL generation per driver
  • Integration tests for DemoSetupSeeder with each DB backend

5. DbDialect Methods

Method Description MySQL PostgreSQL MSSQL
driver() Current driver name ✓ ✓ ✓
isPostgres() Convenience check ✓ ✓ ✓
isMysql() Convenience check ✓ ✓ ✓
isMssql() Convenience check ✓ ✓ ✓
ilike() Case-insensitive LIKE LOWER() LIKE LOWER() ILIKE LOWER() LIKE LOWER()
jsonExtract() JSON value as text JSON_UNQUOTE(JSON_EXTRACT()) ->>'key' JSON_VALUE()
concat() String concatenation CONCAT() \|\| CONCAT()
concatWs() Concatenation with separator CONCAT_WS() CONCAT_WS() CONCAT_WS()
castAsText() Cast to text CAST AS CHAR ::text CAST AS NVARCHAR(MAX)
boolean() Boolean literal 1/0 TRUE/FALSE 1/0
now() Current timestamp NOW() NOW() GETDATE()
disableForeignKeyChecks() Disable FK checks SET FOREIGN_KEY_CHECKS=0 SET session_replication_role per-table
enableForeignKeyChecks() Enable FK checks SET FOREIGN_KEY_CHECKS=1 SET session_replication_role per-table
selectWithLimit() SELECT with limit LIMIT N LIMIT N TOP N

6. Decisions Log

Date Decision Rationale
2026-03-13 Created DbDialect as static helper class Simplest approach, no DI needed for pure SQL expression generation
2026-03-13 Repository structure created but empty No queries complex enough to warrant repository pattern yet
2026-03-13 CONCAT_WS kept as-is in InboxProcessingService Supported by all three target DBs natively
2026-03-13 DemoSetupSeeder SQL files flagged as P3 Seeder is dev-only tooling, not production-critical
2026-03-13 Frontend confirmed DB-agnostic No changes needed
2026-03-13 FK columns changed from string(80) to uuid in original migrations PostgreSQL requires matching types in joins; varchar ≠ uuid. Fixed at source, not via ALTER migration, because Schema Builder cannot cast varchar→uuid on PostgreSQL