JSKIT Backend Migration Tracker

Scope
- Move DB, schema, casting, and normalization ownership out of jskit-ai.
- Enrich json-rest-schema and json-rest-api so they can natively cover the required capability set.
- Do not add compatibility runtimes.
- Do not bend json-rest-api or json-rest-schema around jskit-ai shapes unless the capability is a clean native addition.

Status Legend
- done
- in_progress
- pending
- blocked

Execution Rule
- Complete one numbered point at a time.
- After each point, write a full report:
  - what changed
  - how it worked before
  - how it works now
  - relevant code snippets
- Stop and wait for the next user prompt before starting the next numbered point.

Work List

0. [done] Create and maintain this tracker as the canonical work ledger.
   Notes:
   - This file is the progress source of truth for the migration work.
   - Status changes must be recorded here before moving to the next point.

1. [done] Tighten json-rest-schema scalar semantics.
   Deliverables:
   - make `id` represent the real external/resource ID contract
   - tighten boolean parsing
   - tighten number/integer parsing where schema should reject invalid inputs instead of silently coercing

2. [done] Define a native json-rest-schema create/replace/patch validation contract.
   Deliverables:
   - explicit operation mode semantics
   - presence-aware field handling
   - patch behavior that preserves omission and only normalizes touched fields
   - switch json-rest-api call sites to the explicit schema contract
   Notes:
   - verification blocker removed by deleting stale DB-helper tests from `json-rest-schema`
   - DB-helper coverage now lives in `json-rest-api`, which owns `createKnexTable()` and `generateKnexMigration()`
   - verified end-to-end against a local symlinked `json-rest-schema@1.0.13`; `json-rest-api` test suite passes with the new schema contract

3. [done] Add transport-schema export from json-rest-schema.
   Deliverables:
   - export usable by JSON Schema / Ajv style consumers
   - enough structure for pre-handler validation in connectors such as Fastify
   Notes:
   - added `schema.toJsonSchema({ mode, additionalProperties })` in `json-rest-schema`
   - export is draft-07, mode-aware, and transport-facing rather than a clone of every in-process coercion path
   - custom transport export hooks are supported through `handler.toJsonSchema`
   - unknown external metadata keys are ignored; custom registered validators without a transport hook fail loudly

4. [done] Extend json-rest-schema field metadata only where it remains schema-level meaning.
   Deliverables:
   - preserve useful passive metadata such as nullability, defaults, precision, scale, unsigned, enum values, and temporal precision
   Notes:
   - made `enum` a first-class built-in rule in `json-rest-schema`
   - added canonical passive metadata support for `temporalPrecision`
   - transport export now preserves passive schema metadata under `x-json-rest-schema.metadata`
   - passive metadata remains schema-owned metadata; no DB-specific runtime logic was added to `json-rest-schema`

5. [done] Add repository/storage mapping in json-rest-api.
   Deliverables:
   - API field to DB column mapping
   - virtual/non-column fields
   - write serializers where persistence formatting is required
   Notes:
   - added `field.storage = { column, serialize }` as the native repository/storage surface in `json-rest-api`
   - storage metadata is compiled once into `schemaInfo.storageInfo`
   - Knex writes now translate logical attributes to storage columns through the compiled mapping
   - table creation and migration generation now respect storage column overrides
   - JSON:API transformers translate storage rows back to logical field names before response shaping
   - storage adapters now own row/id/select translation used by Knex callers, reducing repeated mapping helpers
   - runtime row access now uses the same logical field path for `id` as for other fields: `getFieldValue(record, 'id')`
   - include handling and relationship links now use logical record IDs consistently, including custom-id resources
   - direct DB helper surfaces now support logical `id` fields mapped to custom storage columns without duplicate-column drift
   - verified with dedicated storage-mapping tests and the full `json-rest-api` suite

6. [done] Add DB introspection snapshot support in json-rest-api.
   Deliverables:
   - column type and shape introspection
   - enum/set values
   - indexes
   - foreign keys
   - check constraints
   - precision/scale/temporal precision
   Notes:
   - added `introspectKnexTableSnapshot(knex, { tableName, idColumn })` as the native normalized snapshot helper
   - snapshot support is dialect-aware for `better-sqlite3`/SQLite and `mysql2`
   - SQLite captures named check constraints and foreign keys from table SQL, while indexes/columns/foreign-key actions come from PRAGMA metadata
   - `RestApiKnexPlugin` now exposes `api.resources.{resource}.introspectKnexTableSnapshot()` for table-backed resources
   - verified with real SQLite snapshot tests, MySQL raw-double snapshot tests, and a public scope-method test

7. [done] Add deterministic migration generation and diffing in json-rest-api.
   Deliverables:
   - create and alter support
   - named/composite indexes
   - FK rules
   - enum/set handling
   - defaults and safety warnings for destructive changes
   Notes:
   - `dbTablesOperations` now normalizes table-level metadata (`indexes`, `foreignKeys`, `checkConstraints`) and uses one contract for live table creation, create-migration generation, and snapshot diffing
   - field-level `unique` / `index` / `references` are compiled into deterministic named indexes and foreign keys instead of relying on ad hoc column builder side effects
   - added `generateKnexMigrationDiff(tableName, currentSnapshot, schema, options)` returning `{ migration, warnings, plan }`
   - additive diff generation supports columns, indexes, and foreign keys; destructive or unsupported changes stay explicit as warnings
   - `RestApiKnexPlugin` now exposes `generateKnexMigration()` and `generateKnexMigrationDiff()` on table-backed scopes and threads table-level metadata through `schemaInfo`

8. [done] Preserve and clarify the pass-through searchSchema contract in json-rest-api.
   Deliverables:
   - searchSchema validates allowed filter keys and normalizes values only
   - filter meaning remains backend-owned and server-defined
   - arbitrary public filter keys are supported when the backend maps them intentionally
   - regression coverage for shared merge/validation behavior and invalid filter keys
   Notes:
   - did not introduce a generic filter DSL
   - documented that searchSchema keys are public filter names, not a portable operator language
   - verified that alias filters (`oneOf`) and custom backend filters (`applyFilter`) work as intended in the Knex path, where their execution semantics are owned by the backend

9. [done] Add declarative autofiltering/scoping in json-rest-api.
   Deliverables:
   - public
   - workspace
   - user
   - workspace_user
   Notes:
   - replaced the purpose-specific multihome surface with a generic `AutoFilterPlugin`
   - resources now declare `autofilter` presets or inline filters, while the plugin resolves scope values from configured resolver functions
   - `workspace`, `user`, `workspace_user`, and `public` are example presets, not built-in auth meanings
   - the plugin scopes collection queries and single-record lookups, stamps scoped fields on `POST`/`PUT`, and rejects inconsistent scoped updates
   - scoped `belongsTo` relationships are supported by injecting or validating JSON:API relationship identifiers before write validation
   - auth/admission semantics remain out of scope for `json-rest-api`; core keeps `checkPermissions` hooks as a neutral extension point only
   - shipped `AccessPlugin` was removed before this point so scoping does not inherit built-in auth assumptions

10. [done] Add SQL-backed query projections and stable list behavior in json-rest-api.
    Deliverables:
    - SQL-backed output-only query fields via an optional plugin
    - sparse-field and include support for declared query fields
    - deterministic ordered-list / cursor behavior with stable tie-breakers
    Notes:
    - added an optional `QueryProjectionsPlugin` that compiles resource-level `queryFields`, separate from normal schema fields and response-layer computed fields
    - query fields are selected by default, can be requested in sparse fieldsets, and are stripped from write input
    - query fields can drive sorting and cursor pagination; `id` is appended as a deterministic tie-breaker
    - query-field selection is supported on the Knex path and the AnyAPI Knex path, including included resources
    - documented as a separate advanced guide so it does not blur with response-layer computed fields

11. [done] Add a Fastify-oriented connector layer in json-rest-api.
    Deliverables:
    - consume exported schema before request handling
    - no jskit-ai compatibility shim
    Notes:
    - added `FastifyPlugin` as a transport connector that registers REST routes on a supplied Fastify instance
    - resource `POST`/`PUT`/`PATCH` routes now attach transport-facing JSON Schemas built from `schema.toJsonSchema({ mode })`, wrapped in the JSON:API document envelope
    - connector-level transport schemas strip output-only/computed fields and direct `belongsTo` foreign keys from `data.attributes`, matching the existing transport write contract
    - connector registers an `application/vnd.api+json` parser and rejects unsupported write content types before route handlers run
    - no jskit-ai compatibility shim or duplicate hand-written field validation layer was added

12. [pending] Remove the corresponding DB/schema/normalization ownership from jskit-ai and verify parity.
    Deliverables:
    - no jskit-ai-owned DB/schema normalization path remains for the migrated surface
    - verification notes for anything still intentionally left outside scope

ADDENDUM ITEMS TO POINT 12:

* This is the reason we did points 1 to 11 (!).
* Needs to be done with a lot of care, since it's harder than it looks 
* You MUST read AGENTS.md in jskit-ai VERY carefully, follow it VERY carefully. One of the previous attempts (4 failed in total) was unsuccessful because this step was missed. That file has important instructions on how to avoid repeated helpers and a pointer to important function maps
* There must be NOTHING left of the in house DB stuff. jskit is only a user of what json-rest-api offers.
* NO SHIMS. NO COMPATIBILITY LAYERS. NO LAYERS AT ALL. NO RUNTIMES.
* In an earlier attempt, you made a runtime that mimiked the repository's functions. NO. Repository functions will use json-rest-api's functions to do what they need to do
* A repository should be simple. It can have helper functions, but if it has a LOT of helper functions, there is a problem -- interrupt and let's talk. This happened in the workspace repository
* Multi homing is to be done with the generic filtering option of json-rest-api
* Take a LOT of care with the definiton of the resource file. Do NOT keep the old Type + Normalisation flow. That is GONE. We now have json-rest-schema, and that's what you need to use. One of the 4 failed attempts failed because you were stubborn about reusing that paradigm
* The resource file (which is what is used by the server, AND by the client) needs to be clean, and contain the right information (schemas, relationships, etc). It's a DATA ONLY file.
* The goal is to devoid jskit of any db logic/layering/

