Add SQLite-native validation and integrity constraints

[eviltester]

Summary The SQLite repository should take advantage of SQLite-native validation and integrity features where they map cleanly to Thingifier schema rules, while keeping the existing Java-side validation as the portable baseline for in-memory and other future repository implementations. This is a hardening issue for the SQLite-backed repository. It should not remove Java validation. Instead, SQLite should become a second line of defence for persisted/file-backed and sqlite-memory repositories. ## Motivation Thingifier currently validates most rules in Java before writing through the repository. That is still required because not every repository can enforce the same constraints natively. SQLite can enforce some of the schema contract directly, which would help: - catch repository bugs that bypass Java validation; - protect file-backed SQLite databases from accidental invalid writes; - make the SQLite backend more robust for long-lived data; - document which Thingifier rules are database-enforced versus Java-only. ## Proposed Plan 1. Audit Thingifier validation rules against SQLite capabilities - Classify fields and relationships into: - safely enforceable by SQLite schema constraints; - enforceable with SQLite indexes or foreign keys; - enforceable with CHECK constraints; - possible but not worth doing yet; - Java-only. - Document the decision matrix in the repository migration notes or SQLite repository docs. 2. Add native field constraints where straightforward - Add NOT NULL for fields marked mandatory where existing schema creation can safely do this. - Add UNIQUE or unique indexes for primary/candidate unique fields, preserving existing unique-index behavior. - Add CHECK constraints for simple supported types and values where SQLite can enforce them without surprising coercion. - Consider simple enum CHECK(field IN (...)) constraints if the enum values are static and safe to render as parameters or safely quoted literals during DDL generation. 3. Add relationship integrity where safe

• Enable SQLite foreign key enforcement for SQLite connections with PRAGMA foreign_keys = ON.
• Add foreign key constraints from relationship join tables to entity tables where possible.
• Explicitly prevent dangling relationship rows:
  • from_internal_id values must reference existing rows in the source entity table;
  • to_internal_id values must reference existing rows in the target entity table;
  • relationship rows should not survive as orphaned data when either linked entity row is removed.
• Decide and document delete behavior:
  • cascade relationship rows when an entity row is deleted;
  • do not cascade entity rows unless Thingifier relationship optionality/cardinality explicitly requires it and the behavior is already matched in Java.
• Add direct SQLite/repository tests that prove invalid relationship rows cannot reference missing entity rows.

4. Preserve Java validation as the canonical portable validation layer - Keep existing Java validation before repository writes. - Convert SQLite constraint failures into useful Thingifier/repository exceptions or validation errors where practical. - Ensure in-memory repository behavior does not diverge from SQLite behavior for normal API paths. 5. Handle schema lifecycle carefully - Avoid silently changing existing tables in unsafe ways. - If SQLite cannot add a constraint to an existing table with ALTER TABLE, either: - apply it only on new table creation in this issue; or - create a deliberate migration/rebuild path in a separate issue. - Make file-backed database behavior explicit when schema constraints evolve. 6. Add tests - Repository contract tests should continue to pass for in-memory and SQLite repositories. - Add SQLite-specific tests proving native constraints reject invalid direct repository writes where applicable. - Add tests for mandatory fields, unique fields, simple type/check constraints, and relationship foreign-key cleanup. - Add tests that SQLite constraint failures are reported in a useful way and do not corrupt repository state. - Add regression tests proving Java validation still runs and in-memory behavior remains compatible. ## Acceptance Criteria - SQLite repository enables and verifies PRAGMA foreign_keys = ON for every owned connection. - New SQLite entity tables include native constraints for the subset of Thingifier field rules chosen as safely enforceable. - New SQLite relationship tables use foreign keys to the source and target entity tables where possible.

• SQLite rejects or prevents relationship rows whose source or target internal IDs do not exist in the corresponding entity tables. - SQLite delete behavior for relationship rows matches Thingifier's existing Java semantics. - Existing Java validation remains in place and is still used before repository writes. - Constraint failures from SQLite are translated or surfaced clearly enough for debugging and API handling. - The implementation documents which validation rules are SQLite-enforced and which remain Java-only. - In-memory repository tests still pass unchanged. - SQLite repository-specific tests cover native validation and relationship integrity. - Full targeted verification passes: powershell mvn -pl ercoremodel -am "-Dtest=ThingRepositoryContractTest" -DfailIfNoTests=false test mvn -pl ercoremodel,thingifier -am "-Dtest=*Repository*Test,*Sqlite*Test" -DfailIfNoTests=false test ## Non-Goals - Removing Java-side validation. - Making SQLite the only source of validation truth. - Requiring every future repository implementation to support native constraints. - Building a full SQLite migration framework unless constraint evolution makes it unavoidable. - Implementing complex validation rules such as regex or cross-field validation in SQLite in this issue. - Changing Challenger-specific behavior. ## Notes This should build on the repository-interface and SQLite repository work from Complete SQLite-backed repository migration for normal API and GUI paths #29. The guiding rule is: Java validation remains portable and canonical, but repository implementations may add native enforcement when doing so improves durability and safety without changing public behavior.