Relationships: many-to-one / one-to-many via a foreign-key column

[jkalias]

Sub-issue of #3. This is the first increment and also lands the shared plumbing the other relationship types build on.

Scope

Support the most common association: a "many" record holding a reference to a "one" record (e.g. Order → Customer), and the inverse collection (Customer → its Orders). Realized in SQL as a single foreign-key column on the many side.

Shared plumbing introduced here (reused by sub-issues for 1→1 and n→n)

• Relationship metadata. Reflection::MemberMetadata (include/reflection.h, fields: name, storage_class, sqlite_column_name, offset) today describes only scalar columns. Extend it to carry optional relationship info: target record name, cardinality, and the generated FK column name. The SqliteStorageClass enum may need a dedicated reference kind, or relationship-ness can be a separate flag alongside an underlying kInt storage (the FK value is an int64_t id).
• FK enforcement. SQLite has foreign keys off by default. Enable PRAGMA foreign_keys = ON right after the connection is opened in the Database constructor (src/database.cc:84, the sqlite3_open_v2 call), so constraints are actually enforced. (No PRAGMA is set today.)
• Table-creation ordering. The Database constructor currently iterates reg.records in arbitrary (map) order (src/database.cc:94). A referenced table must exist before the referencing one; introduce a dependency-aware ordering (topological sort of the FK graph). Note cyclic references and decide handling (deferred FK / nullable column).

Declaration syntax (proposed)

A new macro on the many side:

#define REFLECTABLE Order
#define FIELDS \
MEMBER_REAL(total) \
MEMBER_REFERENCE(Customer, customer) // adds an int64_t customer_id FK to Customer
#include "reflection.h"

MEMBER_REFERENCE(TargetRecord, name) expands to an int64_t <name>_id field plus metadata marking it a many-to-one FK to TargetRecord. The inverse (Customer → Orders) is resolved by query, not stored as a column (see Fetching).

Schema

CreateTableQuery::CustomizedColumnName (src/queries.cc:192) emits, for a reference member, customer_id INTEGER REFERENCES Customer(id) (with ON DELETE per the integrity decision below).

Fetching

Decide and document the loading model. Proposal for this increment: explicit/lazy loading rather than auto-join —

• db.Fetch(&pred) returns rows with the raw customer_id populated;
• a helper resolves the target, e.g. db.FetchReferenced(order.customer_id) (or a typed Resolve(&Order::customer) form);
• the inverse collection is a predicate query: fetch all Order where customer_id == customer.id. Consider a typed helper db.FetchChildren(&Order::customer, customer).

This intersects with query_predicates.h, which today only references members of one record type — the FK column is an ordinary int64_t member, so existing predicates work on it without extension.

Referential integrity

Pick a default ON DELETE behavior (proposed: RESTRICT) and, ideally, a way to opt into CASCADE / SET NULL. SET NULL presupposes nullable FK columns, which ties into #2.

Acceptance criteria

• MEMBER_REFERENCE declares a many-to-one FK; generated CREATE TABLE includes the REFERENCES clause and FK enforcement is on.
• Tables are created in dependency order; a clear error (not a SQLite failure) on unsatisfiable/cyclic graphs.
• Save/fetch round-trips an Order with its customer_id; the inverse collection query returns a customer's orders.
• A delete that violates the constraint fails per the chosen ON DELETE policy, with a test covering it.
• README gains a "Relationships" section documenting MEMBER_REFERENCE and the loading model.

Out of scope

One-to-one uniqueness and many-to-many junction tables (their own sub-issues).