Support relationships between record types (associations / foreign keys)

[jkalias]

Background

A core capability of relational schema design is expressing relationships between tables — foreign keys, joins, and the cardinalities that connect entities. sqlite-reflection does not support this yet. Each reflected record maps to a standalone table (one CREATE TABLE per registered type in CreateTableQuery), and there is no way for one record to reference another. Every record is effectively an island.

In practice almost any non-trivial domain needs relations: an Order belongs to a Customer, a Customer has many Orders, a Student enrolls in many Courses and vice versa. Today the only workaround is to store a raw int64_t id of the related row by hand and manage the lookups, integrity, and cascade behavior entirely in user code — with no type safety and no schema-level enforcement.

The relationships to support

Cardinality	Example	Typical SQL realization
one-to-one (1 → 1)	Person ↔ Passport	foreign key with a UNIQUE constraint
many-to-one (n → 1)	Order → Customer	foreign key column on the "many" side
one-to-many (1 → n)	Customer → Orders	inverse of many-to-one
many-to-many (n → n)	Student ↔ Course	a join/junction table

Design considerations

This is a large feature; the issue is to scope and design it, not necessarily to land all four cardinalities at once. Open questions:

• Declaration syntax. How is a relationship expressed in the X-macro field list in reflection.h? E.g. a new macro such as MEMBER_REFERENCE(Customer, customer) for the foreign-key side, and some declarative form for the inverse (collection) side. The MemberMetadata model and SqliteStorageClass enum currently describe only scalar columns and would need to carry relationship metadata (target record, cardinality, FK column name).
• Foreign keys in the schema. SQLite enforces foreign keys only when PRAGMA foreign_keys = ON is set per connection — it is off by default. Supporting real FK constraints means emitting REFERENCES/FOREIGN KEY clauses in CreateTableQuery and enabling the pragma at connection open in database.cc.
• Join/junction tables. Many-to-many needs an automatically managed link table. The library would need to create and name it, and keep it in sync on save/update/delete.
• Fetching related data. Decide between eager joins and lazy/explicit loading (e.g. db.Fetch<Order>(...) then a separate call to resolve its Customer, vs. a single query that hydrates both). This intersects with the predicate API in query_predicates.h, which today only references members of a single record type.
• Cascade / referential integrity. Behavior on delete (restrict, cascade, set null) of a referenced row needs a defined default and ideally a way to configure it.
• Ordering of table creation. Foreign keys imply a dependency order when creating tables; registration order is currently arbitrary.

Suggested approach

Given the size, consider splitting the work into linked sub-issues per cardinality, landing the simplest first:

1. many-to-one / one-to-many via a single foreign-key column (covers the most common cases),
2. one-to-one as a constrained special case of the above,
3. many-to-many with an auto-managed junction table.

Acceptance criteria (per increment)

• A relationship can be declared between two record types using the reflection macros.
• The generated schema expresses the relationship (foreign-key column and/or junction table), with PRAGMA foreign_keys = ON enabled so constraints are enforced.
• Related records can be saved and fetched with a typed API, without hand-written id bookkeeping.
• Tests cover each supported cardinality, and the README gains a "Relationships" section.