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

Recommended additions¶

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