Active Record Conversion Audit & Phased Rollout Plan

2026-03-23-active-record-conversion-audit.md

Active Record Conversion Audit

Task Narrative

We need a comprehensive, up-to-date checklist of all code that still needs refactoring to use the Active Record pattern. An existing conversion plan lives at apps/web/src/models/active-record-conversion.md (created 2026-03-02). Our job is to verify whether that plan is still current, identify any gaps (new tables, new files), and produce a single authoritative checklist suitable for handing off to Kindling.

Synthesized Context

• Active Record library: apps/web/src/lib/active-record/ (base Record, Collection, Relation classes)
• Secure layer: apps/web/src/models/base/secure-record.ts (adds SecureModel + SecureCollection with auto-scoping, permissions, FK ownership validation)
• Existing conversion plan: apps/web/src/models/active-record-conversion.md -- tiered list of tables with scope columns, FK references, permissions, and raw db() file locations
• CHANGELOG pending items (apps/web/src/models/CHANGELOG.md):
  1. No models use FK ownership validation (references) yet
  2. SecureCollection should enforce mustBeScoped
  3. Five shared services still use raw Kysely (destinations, trucks, tow-reasons, hold-reasons, custom-fields)

Investigation Findings

1. Existing Models (7 total, unchanged since plan was written)

Model	Table	File
DestinationRecord	destination	models/destination.ts
TruckRecord	truck	models/truck.ts
TowReasonRecord	tow_reason	models/tow-reason.ts
HoldReasonRecord	hold_reason	models/hold-reason.ts
CustomFieldDefinitionRecord	custom_field_definition	models/custom-field-definition.ts
ServiceRateRecord	service_rate	models/service-rate.ts
PortalUserCompanyRecord	portal_user_company	models/portal-user-company.ts

2. Gaps in the Existing Conversion Plan

The following tables/areas exist in the codebase but are not listed in active-record-conversion.md:

New Tables (added after 2026-03-02)

Table	Scope Column	Raw db() Files	Notes
auction	companyId	auctions/actions.ts, auctions/queries.ts, auctions/import-actions.ts	Heavy CRUD + complex queries
auction_vehicle	(scoped via auctionId)	auctions/actions.ts, auctions/queries.ts, auctions/import-actions.ts	Junction table, indirect scoping
buyer	companyId	auctions/buyer-actions.ts, auctions/queries.ts, auctions/import-actions.ts	Buyer management
permission_group	companyId	settings/permission-groups/actions.ts, shared/services/permission-groups.ts, settings/users/actions.ts	RBAC groups
permission_group_permission	(scoped via permissionGroupId)	Same as above	Junction table

Services Not In Plan

Service	Raw Tables Queried
shared/services/permission-groups.ts	permission_group, permission_group_permission
shared/services/users.ts	auth_user (may remain raw -- auth infra)

3. Shared Services Still Using Raw db() (confirmed)

All 15 shared services still use raw db() -- zero have been converted to use models:

Service File	Tables Queried	Has Existing Model?
accounts.ts	account	No
calls.ts	call	No
trucks.ts	call (not truck!)	Yes (TruckRecord)
tow-reasons.ts	tow_reason	Yes (TowReasonRecord)
hold-reasons.ts	hold_reason	Yes (HoldReasonRecord)
custom-fields.ts	custom_field_definition	Yes (CustomFieldDefinitionRecord)
companies.ts	company	No
invitations.ts	invitation	No
display-id.ts	tenant_counter	No (not candidate -- atomic counter)
impound-search.ts	impound	No
vehicle.ts	vehicle	No
permission-groups.ts	permission_group, permission_group_permission	No
users.ts	auth_user	No (auth infra -- not candidate)
clickins.ts	(TBD)	No
integration/*	conversation_log, various	No

4. Conversion Plan Accuracy

The existing plan's tier structure and table inventory are mostly accurate but missing:

• 3 new tables: auction, auction_vehicle, buyer
• 2 new tables: permission_group, permission_group_permission
• Auctions page: actions.ts, queries.ts, import-actions.ts, buyer-actions.ts -- the single largest area of raw db() usage not in the plan
• The shared services flagged in the CHANGELOG as "pending" are still all unconverted

5. CHANGELOG Pending Items Status

Item	Status
FK ownership validation (references)	Still not used by any model
SecureCollection enforce mustBeScoped	Still pending
Shared services conversion (5 files)	Still raw db()

---

Updated Comprehensive Checklist

Already Converted (7 models)

• destination -- DestinationRecord
• truck -- TruckRecord
• tow_reason -- TowReasonRecord
• hold_reason -- HoldReasonRecord (WARNING: no requiredScope)
• custom_field_definition -- CustomFieldDefinitionRecord
• service_rate -- ServiceRateRecord
• portal_user_company -- PortalUserCompanyRecord

Tier 1 -- Core Business Entities (high IDOR risk, many raw queries)

• account -- Files: accounts/actions.ts, accounts/queries.ts, services/accounts.ts, bulk-import
• call -- Files: calls/actions.ts, calls/queries.ts, calls/routes.ts, services/calls.ts, bulk-import
• impound -- Files: impounds/actions.ts, new-impound/ routes, impound-search.ts, yard-pass
• vehicle -- Files: vehicle-db.ts, bulk-import, calls, impounds (NOTE: uses company_id snake_case)

Tier 2 -- Financial & Charges

• charge -- Files: chargeActions.ts, impoundChargeActions.ts, calls/actions.ts (no companyId -- indirect scope)
• invoice -- Files: chargeActions.ts, impoundChargeActions.ts (no companyId -- indirect scope)
• account_rate -- Files: accountRateActions.ts (no companyId -- indirect scope)

Tier 3 -- Supporting Entities

• call_note -- Files: calls/actions.ts
• call_status_history -- Files: calls/actions.ts, services/calls.ts
• impound_note -- Files: impounds/actions.ts
• impound_activity -- Files: impound-activity.ts
• impound_release -- Files: impounds/actions.ts
• vehicle_image -- Files: vehicle-images.ts
• vin_scan -- Files: vin-scans.ts
• inspection_record -- Files: inspection/actions.ts
• address -- Files: spatial.ts, calls, destinations (NOTE: uses company_id snake_case, nullable)

Tier 4 -- Settings & Configuration

• company -- Files: company/actions.ts, services/companies.ts
• invitation -- Files: invitations.ts, auth routes
• driver_route_sheet -- Files: route-sheet/actions.ts
• driver_route_sheet_item -- Files: route-sheet/actions.ts (no companyId -- indirect scope)
• custom_field_archive -- Files: custom-fields/actions.ts
• yard_pass_log -- Files: yard-pass-actions.ts
• clickins_inspection -- Files: clickins-inspections.ts

Tier 5 -- Reporting & Logs (read-heavy, lower IDOR risk)

• report_category -- Files: reports/actions.ts
• report_definition -- Files: reports/actions.ts, reports/queries.ts
• report_run -- Files: reports/actions.ts (no companyId -- indirect scope)
• conversation_log -- Files: conversation-log.ts, ivr/account-lookup.ts
• bulk_import_batch -- Files: bulk-import/actions.ts

NEW -- Tier 6 -- Auctions & Marketplace (not in original plan)

• auction -- Files: auctions/actions.ts, auctions/queries.ts, auctions/import-actions.ts
• auction_vehicle -- Files: same as auction (no companyId -- indirect scope via auctionId)
• buyer -- Files: auctions/buyer-actions.ts, auctions/queries.ts, auctions/import-actions.ts

NEW -- Tier 7 -- RBAC (not in original plan)

• permission_group -- Files: settings/permission-groups/actions.ts, shared/services/permission-groups.ts
• permission_group_permission -- Files: same as above (no companyId -- indirect scope)

Not Candidates (auth infrastructure, unchanged from original plan)

• auth_user -- Managed by Better Auth adapter
• auth_account -- Auth provider link table
• auth_session -- Session store
• auth_verification -- Token verification
• portal_user -- Portal auth
• portal_account -- Portal auth provider links
• portal_session -- Portal session store
• portal_verification -- Portal token verification
• portal_user_company_account -- Junction table managed by PortalUserCompanyRecord.delete()
• call_status -- Global enum-like lookup, not tenant-scoped
• call_note_read_status -- User-specific read markers
• tenant_counter -- Atomic counter, special transaction semantics
• expo_push_token -- Device token, user-scoped not company-scoped

Settings Page Actions Still Using Raw db() Despite Having Models

These settings page action files have raw Kysely queries for tables that already have Active Record models:

• settings/subpages/trucks/actions.ts -- raw truck queries (model exists: TruckRecord)
• settings/subpages/trucks/queries.ts -- raw truck, auth_user queries
• settings/subpages/services/actions.ts -- raw service_rate, account, charge, company queries (model exists: ServiceRateRecord)
• settings/subpages/tow-reasons/actions.ts -- raw tow_reason, account queries (model exists: TowReasonRecord)

Files Using Both Raw Queries AND Models (mixed usage)

• accounts/actions.ts -- imports Destinations model but still uses raw db() for account table
• Some Zod superRefine() validators use raw db() for uniqueness checks even when a model exists for the table

Portal Queries (cross-company, may need special handling)

• portal/queries.ts -- raw queries on company, call, impound, vehicle (portal has separate auth flow, may need unscoped or portal-scoped access)

Admin Pages (intentionally raw -- cross-company admin access)

• admin/pages/subpages/companies/actions.ts -- raw company, address queries
• Admin pages make cross-company queries by design. These should remain raw or use is_internal_admin gating, NOT SecureModel.

Infrastructure Improvements (from CHANGELOG pending)

• Add FK ownership validation (references) to existing models
• Enforce mustBeScoped in SecureCollection factory
• Convert 5 shared services to use existing models: trucks.ts, tow-reasons.ts, hold-reasons.ts, custom-fields.ts, destinations.ts
• Convert settings page actions to use existing models (trucks, services, tow-reasons)

---

Next Steps

The original conversion plan at apps/web/src/models/active-record-conversion.md is stale -- it was written 2026-03-02, before auctions (merged 2026-03-04/2026-03-11) and permission groups (merged 2026-03-12) existed. It needs updating to reflect the current state of the codebase.

Plan: Update active-record-conversion.md

1. Add Tier 6 -- Auctions & Marketplace: auction (companyId-scoped), auction_vehicle (indirect scope via auctionId), buyer (companyId-scoped). Note the heavy raw db() surface area across auctions/actions.ts, auctions/queries.ts, auctions/import-actions.ts, auctions/buyer-actions.ts.

2. Add Tier 7 -- RBAC: permission_group (companyId-scoped), permission_group_permission (indirect scope via permissionGroupId). Raw usage in settings/permission-groups/actions.ts and shared/services/permission-groups.ts.

3. Add "Existing Model Consumer Migration" section: Document the settings page actions and shared services that still use raw db() for tables that already have models. This is a distinct work stream from creating new models -- it's about migrating call sites.

4. Add portal queries note: portal/queries.ts uses cross-company queries with a separate auth flow. Needs special handling -- possibly unscoped access or a portal-scoped actor pattern.

5. Update security concerns: Add permission_group scoping considerations (company-scoped, but permission_group_permission is indirect).

6. Update recommended conversion order: Slot auctions after existing Tier 4 (settings), and RBAC alongside it. Both are self-contained feature areas.

---

Execution Plan: Phased Rollout

Guiding Principle

Start with consumer migrations (call sites that already have models) before creating new models. This develops the migration playbook with minimal risk -- if something goes wrong, the model layer is already proven.

Wave 1 -- Consumer Migrations (3 parallel chunks, lowest risk)

Models exist, tests exist. We are only swapping raw db() call sites to use them. Each chunk touches a different feature area, so they can run in parallel with no merge conflicts.

Chunk A: Tow Reasons + Hold Reasons

• shared/services/tow-reasons.ts -> use TowReasons / TowReasonRecord
• shared/services/hold-reasons.ts -> use HoldReasons / HoldReasonRecord
• settings/subpages/tow-reasons/actions.ts -> use TowReasons
• Scope: ~3 files, read-heavy queries, simple equality filters
• Learning target: how do superRefine() validators interact with collection queries? Do we need .getQuery() escape hatches for complex filters?

Chunk B: Trucks

• shared/services/trucks.ts -> use Trucks / TruckRecord
• settings/subpages/trucks/actions.ts -> use Trucks
• settings/subpages/trucks/queries.ts -> use Trucks (has joins to auth_user -- may need .getQuery())
• Scope: ~3 files
• Learning target: how do we handle queries that join across tables (trucks + drivers)? Does .getQuery() + Kysely join work cleanly, or do we need a pattern for cross-model joins?

Chunk C: Custom Fields

• shared/services/custom-fields.ts -> use CustomFieldDefinitions / CustomFieldDefinitionRecord
• Scope: ~1 file, straightforward read queries
• Learning target: baseline migration -- simplest possible case. Good "hello world" for the pattern.

Expected learnings from Wave 1:

1. Pattern for migrating superRefine() uniqueness validators
2. Pattern for queries that need joins (use .getQuery() vs raw)
3. Pattern for services that return plain data vs record instances
4. Whether SecureCollection needs any API additions (e.g. .count(), .exists())
5. How actor propagation works through shared services (do callers already have actors?)

Wave 2 -- Service Rate + Destination Consumer Migrations (2 parallel chunks)

Apply Wave 1 learnings. Still consumer-only, but slightly more complex.

Chunk D: Service Rates

• settings/subpages/services/actions.ts -> use ServiceRates / ServiceRateRecord
• This file also queries account, charge, company -- only the service_rate parts can be converted now. The rest waits for those models.
• Learning target: partial migration in a file that touches multiple tables

Chunk E: Destinations

• Any remaining destination raw queries in settings or account pages
• Learning target: FK ownership -- destinations are referenced by accounts. Good test case for references config if we enable it.

Wave 3 -- First New Models: Core Entities (sequential, high value)

Create models for the Tier 1 tables. These have the highest IDOR risk and most raw query surface area. Sequential because call and impound depend on account.

1. account model -- foundation for everything else
2. call model -- largest raw query surface, references account/truck/destination/tow_reason
3. impound model -- second largest, similar shape to call
4. vehicle model -- referenced by calls and impounds (note: company_id snake_case)

After each model is created, migrate its consumer files (actions, queries, services) before moving to the next model. This keeps the blast radius small.

Wave 4 -- Financial Models (sequential)

charge, invoice, account_rate -- these have indirect scoping (no companyId, scoped via parent call/impound/account). Needs parent-chain ownership validation pattern.

Wave 5 -- Supporting Entities (parallel by parent)

These are smaller tables that hang off calls or impounds. Can parallelize by parent:

Call children (parallel): call_note, call_status_history, vin_scan Impound children (parallel): impound_note, impound_activity, impound_release, yard_pass_log Shared children: vehicle_image, inspection_record, address, clickins_inspection

Wave 6 -- New Feature Areas (parallel)

Auctions: auction, auction_vehicle, buyer -- self-contained feature area RBAC: permission_group, permission_group_permission -- self-contained Reporting: report_category, report_definition, report_run

Wave 7 -- Remaining + Infrastructure

• invitation, driver_route_sheet + items, custom_field_archive, conversation_log, bulk_import_batch
• Portal queries (special handling TBD)
• Infrastructure: enable FK ownership validation (references), enforce mustBeScoped

Wave Summary

Wave	What	Parallelism	Risk	Depends On
1	Consumer migrations (tow-reasons, trucks, custom-fields)	3 parallel	Lowest	Nothing
2	Consumer migrations (service-rates, destinations)	2 parallel	Low	Wave 1 learnings
3	New models: account, call, impound, vehicle	Sequential	Medium	Wave 1-2 learnings
4	New models: charge, invoice, account_rate	Sequential	Medium	Wave 3 (parent models)
5	Supporting entity models	Parallel by parent	Low-Med	Wave 3 (parent models)
6	Auctions, RBAC, reporting	Parallel by area	Medium	Wave 3 patterns
7	Remaining + infrastructure	Mixed	Low	All above

---

Plan Gist

Published as a public gist for PR visibility: https://gist.github.com/justinvdm/93104d6354a70eb90c550454347b94df

Each Wave 1 PR (#1001, #1002, #1003) has a comment linking here. Keep the gist updated when the plan changes.

Wave 1 Agent Briefs

Brief A: Tow Reasons + Hold Reasons Consumer Migration

Objective: Migrate the tow-reasons and hold-reasons shared services and settings page actions from raw db() queries to use the existing TowReasons/TowReasonRecord and HoldReasons/HoldReasonRecord Active Record models.

Problem: These services and actions bypass the model layer entirely, duplicating scoping logic and missing permission enforcement. The models already exist and are tested -- the call sites just haven't adopted them.

What to Do:

1. Replace raw Kysely queries in shared/services/tow-reasons.ts with TowReasons collection methods. Functions should accept an actor or use TowReasons.for() where called from request context.
2. Replace raw Kysely queries in shared/services/hold-reasons.ts with HoldReasons collection methods.
3. Replace raw Kysely queries in the tow-reasons settings page actions with model usage, following the same Model.for({ companyId }) pattern used by Destinations.for() throughout the codebase.
4. Where a raw query does something the collection API doesn't support (aggregates, complex filters), use .getQuery() to access the underlying Kysely builder rather than bypassing the model.
5. Record learnings about any patterns that emerge -- especially around Zod superRefine() validators, aggregate queries, and cases where .getQuery() was needed.

What NOT to Do:

• Do not modify the model definitions (models/tow-reason.ts, models/hold-reason.ts) or the base SecureModel/SecureCollection classes.
• Do not change function signatures or return types of the service functions -- consumers should not need updating.
• Do not convert the admin page call sites (those intentionally bypass models for cross-company access).
• Do not add new features or refactor surrounding code. This is a 1:1 migration of query implementation.

Invariants & Constraints:

• All existing E2E tests must continue to pass.
• The HoldReasonRecord has no requiredScope -- it's scoped indirectly via towReasonId. Respect this; do not add scoping it doesn't have.
• accountIds filtering happens in-memory (JSON column parsed in JS). Preserve this pattern.

---

Brief B: Trucks Consumer Migration

Objective: Migrate the trucks shared service, settings page actions, and settings page queries from raw db() queries to use the existing Trucks/TruckRecord Active Record model.

Problem: The trucks shared service and settings page actions use raw db() Kysely queries, bypassing the Trucks/TruckRecord Active Record model that already exists with company scoping and permission enforcement. This duplicates scoping logic and misses the security guarantees the model layer provides.

What to Do:

1. Replace raw Kysely queries in shared/services/trucks.ts with Trucks collection methods.
2. Replace raw Kysely queries in the trucks settings page actions and queries with model usage.
3. For queries that join trucks with other tables (e.g. drivers/auth_user), use .getQuery() to get the scoped Kysely builder and then add joins on top. This tests an important pattern for future migrations.
4. Record learnings about cross-table joins when using the Active Record layer.

What NOT to Do:

• Do not modify the model definition (models/truck.ts) or the base classes.
• Do not change function signatures or return types.
• Do not convert admin page call sites.

Invariants & Constraints:

• All existing E2E tests must continue to pass.
• The trucks queries file joins to auth_user for driver data. The join pattern chosen here becomes precedent for Wave 3+ migrations.

---

Brief C: Custom Fields Consumer Migration

Objective: Migrate the custom-fields shared service from raw db() queries to use the existing CustomFieldDefinitions/CustomFieldDefinitionRecord Active Record model.

Problem: The custom-fields shared service uses raw db() Kysely queries, bypassing the CustomFieldDefinitions/CustomFieldDefinitionRecord Active Record model that already exists with company scoping and permission enforcement. This duplicates scoping logic and misses the security guarantees the model layer provides.

What to Do:

1. Replace raw Kysely queries in shared/services/custom-fields.ts with CustomFieldDefinitions collection methods.
2. Where the service has complex filtering (LIKE patterns, dynamic ordering, aggregate queries), use .getQuery() for the scoped builder.
3. Record learnings about complex query patterns with the Active Record layer.

What NOT to Do:

• Do not modify the model definition or base classes.
• Do not change function signatures or return types.
• Do not convert the custom-fields settings page actions file -- it has complex multi-table transactions (archival, column migration) that are out of scope for Wave 1.
• Do not convert admin page call sites.

Invariants & Constraints:

• All existing E2E tests must continue to pass.
• The actions file (settings/subpages/custom-fields/actions.ts) is explicitly out of scope -- it touches custom_field_archive, call, and impound tables in transactions. That's a Wave 3+ problem.