serverpod-offline-sync/docs/projection-model.md

Rebuildable FK and unique projection model

Given the same authored CRDT facts and synchronized schema, every replica must rebuild the same domain rows and projection metadata, including hidden rows. The result must not depend on arrival order, batching, local history, or bootstrap from another replica.

Related documents:

• Foreign-key projection invariants
• Unique-constraint invariants

Decisions

• Domain columns are materialized projection whenever FK or unique processing changes an authored value. The authored value must remain available.
• CrdtDataAttemptedValue is the single, generic attempted-value authority for FK, unique, and fields governed by both. Projectors never keep independent attempted values.
• FK-safe candidates are ephemeral planner output and are never persisted; CrdtDataAttemptedValue also carries a generic diagnostic projectionReason.
• Hidden rows participate in FK and release projection. They may regain an FK-safe candidate, but are always released before unique conflict grouping and never participate in winner selection.
• FK projection runs before unique projection. Unique projection consumes FK-safe candidates and effective visibility, and produces final domain values.
• Projection never emits an authored field update or advances its HLC.
• Physical FK and unique constraints remain correctness backstops.
• Outbound sync sends authored values, never projected or temporary values.
• Persisted schema types are validated eagerly at initialization. A migration hook may automate conversion later, but is not required for safety.

Storage ownership

Authored facts

CrdtDataField owns the field HLC. Its authored value is read as:

attemptedValue(field) =
  CrdtDataAttemptedValue.value, if that row exists
  domainRow[field],             otherwise

CrdtDataAttemptedValue is a sparse one-to-one dependent of CrdtDataField:

# Backlink added to CrdtDataField:
attemptedValue: CrdtDataAttemptedValue?, relation(optional, name=field_attempted_value)

class: CrdtDataAttemptedValue
table: crdt_data_attempted_value
database: all
fields:
  fieldId: int, unique
  field: CrdtDataField?, relation(name=field_attempted_value, field=fieldId, onDelete=Cascade)
  value: dynamic, serializationDataType=jsonb
  projectionReason: CrdtProjectionReason

Use Serverpod's existing dynamic {className, data} representation, as it preserves String, UuidValue, null, and other supported Serverpod values independently of the database dialect.

Create or update the row whenever the final domain value differs from the authored value. Remove it only when the domain value again equals the authored value and no projector still needs an override. At that point the domain column itself safely carries the authored value. A CrdtDataField row is needed when its HLC is newer than the row HLC or it holds a preserved attempted value. Otherwise the field inherits the row's insertion HLC.

Reinserting a deleted row

Inserting or upserting a deleted row keeps its identity and ownership and advances its delete/restore generation. The complete row is authored at a new insertion HLC, including preserved conflict claims, so unique claims receive a new age. This is a full-row write even when the upsert supplies an update column filter or predicate: those restrictions apply to visible existing rows.

Old field timestamps are removed. Only metadata needed to preserve a projected value remains, at that same insertion HLC; deleting that metadata would cascade to the only stored copy of its authored value. Projection uses the renewed claim ages before redundant field records are removed.

Outbound sync sends the full authored row without redundant field updates. Existing receivers still compare each field's HLC, so newer concurrent edits survive. The delete/restore generation is never reset, preventing an older deletion from hiding the restored row. These changes are atomic with the domain write and roll back together on failure.

Diagnostic projection reason

projectionReason replaces the FK-specific override enum and can describe any projector, for example:

enum: CrdtProjectionReason
serialized: byIndex
values:
  - foreignKeySetNull
  - foreignKeySetDefault
  - foreignKeyMissingParent
  - uniqueConflict
  - hiddenUniqueRelease

It is diagnostic-only derived state. Projection decisions must never read it; the planner recomputes from authored facts and schema, then rewrites the reason with the final domain value. Because it is singular, it records the terminal stage that selected that value. Earlier causes remain reconstructible rather than becoming combinatorial enum variants.

The row therefore deliberately contains data with two lifetimes:

• value is a durable authored fact and must survive rebuilds; and
• projectionReason is disposable and may be recomputed in place.

Code that rebuilds diagnostics must update the reason without deleting the row.

Examples:

State	Attempted row	Reason	Domain value
No projection	absent	absent	authored value
FK SET NULL repair	authored UUID	foreignKeySetNull	null
Unique loser	authored value	uniqueConflict	released value
Hidden unique row	authored value	hiddenUniqueRelease	released value
FK candidate then unique release	authored UUID	uniqueConflict	released value

Exact projection order

Local writes and inbound merges use one fact-plus-projection planner.

1. Merge or author facts. Resolve row, field, tombstone, and HLC facts. Capture every affected authored field value before changing its materialized domain value. Projection does not alter these facts.
2. Compute visibility and FK candidates. Starting from authored FK values, resolve cascade, restrict/no-action, set-null, and set-default to the FK fixed point over visible and hidden rows. Produce effective visibility and an FK-safe candidate for every FK field.
3. Release hidden rows, then resolve visible claims. First assign every hidden row a deterministic conflict-free value, even when it is the only row with that claim. Hidden rows do not join conflict groups or winner selection. Then build conflict groups from visible rows only. For an FK field, key the lookup by its FK-safe candidate; for other fields, key it by its authored value from CrdtDataAttemptedValue when present, otherwise the domain column. Never key it by a previously released materialized value. Claim ordering still uses canonical authored field HLCs. The deterministic visible winner materializes the canonical claim and every visible loser receives a deterministic conflict-free value.
4. Build one final plan. The plan contains final visibility, final domain values, and required CrdtDataAttemptedValue rows with diagnostic reasons. Unique output does not feed back into FK visibility: it changes materialization, not the authored FK or FK-safe candidate. A full recomputation is the oracle for any incremental affected-closure algorithm.
5. Materialize atomically. Park changing unique tuples at deterministic constraint-safe temporary values when immediate indexes require it, then write final domain values and metadata in the same transaction. Temporary values are never facts or outbound changes.

This order resolves a hidden unique FK without an FK-side exception. If its parent becomes visible, the FK stage may restore the authored UUID as its candidate; the unique stage then releases it because the child is hidden. When the child is restored, it enters visible winner selection and either reclaims the UUID or receives the supported loser value. For a unique FK, that release must be FK-safe; under the current policy the FK is nullable and releases to null.

Local insert, update, upsert, delete, and tombstone restoration need this plan before the underlying write can violate an immediate unique constraint. A full-row passthrough translates an unchanged projected value back to its preserved authored value; explicit/narrowed field writes author the supplied value. Reinsertion follows the full-row reauthoring rule above.

Outbound and rebuild behavior

Collection captures pending insert, update, and delete metadata, attempted values, domain payloads, and ownership checks in one repeatable-read transaction. It releases the transaction before emitting the captured changes. Concurrent commits outside that snapshot wait for a later pass. Consume the collector outside application transactions, with no ambient session transaction.

The complete pass is buffered, so memory use grows with its payloads and the first change is emitted only after capture completes. SQLite writers wait during capture. Bounded wire chunks do not bound capture memory.

Outbound collection substitutes CrdtDataAttemptedValue.value whenever it exists; otherwise it reads the domain value. It never exports an FK candidate, unique loser value, or temporary parking value as an authored update.

Snapshot collection cannot recover changes already skipped by an older collector's checkpoint. Recovery requires an explicit full-history collection using checkpointsBySpaceUuid: {space: []}, merged into the affected peer.

An empty replica merging a complete space export must reproduce normalized domain rows, hidden state, authored values/HLCs, and derived projection. A rebuild may rewrite projectionReason, but it must preserve CrdtDataAttemptedValue.value: while the row exists, that is the only copy of the authored fact.

Type and migration safety

Persisted type identity

Add at least these fields to CrdtSchemaColumn:

columnType: ColumnType
dartType: String
isNullable: bool

All three are required. Serverpod's stable ColumnType enum may be persisted directly and records database storage; dartType records dynamic value semantics. For example, bigint may represent int, Duration, or a by-index enum, while one Dart value can use different storage such as json or jsonb. Relevant parameters, nullability, FK definitions, unique indexes, and release policy also belong in the persisted schema identity and sync compatibility hash.

Dynamic validation

For normal engine writes and inbound sync, let Serverpod deserialize CrdtDataAttemptedValue.value, then compare its canonical serialized class name with the class expected by dartType. Do not compare runtimeType.toString() and do not manually query inside the JSON envelope. Null is validated from the persisted schema because it has no concrete runtime class.

Top-level List does not validate its element type. Any supported generic or custom type therefore needs recursive validation plus deterministic, cross-dialect unique equality. Initialization must reject a schema containing a releasable type whose validation or release semantics the projector does not support; this is a schema check and does not scan persisted attempted values.

For now, synchronized unique projection must reject any ColumnType.json or ColumnType.jsonb domain column participating in a unique claim. Neither nullable JSON as a releasable candidate nor non-nullable JSON as a fixed component is supported: sync semantics do not yet define canonical, cross-dialect JSON equality. This restriction applies to the domain column's schema type, not to the JSONB envelope used by CrdtDataAttemptedValue.value to store an otherwise supported dynamic value.

Initialization algorithm

Call await offlineSyncDatabase.initialize() after application migrations and before serving requests or starting sync. Reconciliation must plan before mutating:

1. Load the persisted CRDT registry and target TableDefinitions.
2. Compare names, persisted type identity, indexes, and relations.
3. Classify additions, drops, possible renames, and type/policy changes.
4. If types match, trust validated engine writes and do not scan values.
5. For each changed type, perform only an existence check for attempted rows; never deserialize them during initialization.
6. If attempted rows exist, fail without changing registry or projection state. The initialization exception must identify the table, column, stored and target type identities, and instruct the developer to convert the domain and attempted values and then attest completion in CrdtSchemaColumn.
7. Also fail without mutation when a rename or drop/add is ambiguous.
8. Otherwise update the registry, clear obsolete derived caches, rebuild, and publish the in-memory schema.

Initialization is therefore bounded by schema metadata and existence queries; it never adds a data-size-dependent attempted-value scan to application startup.

Direct migrations without a hook

For a non-destructive type change with attempted rows, the initialization exception instructs the application migration to perform these operations in one transaction and in this order:

1. Convert the domain column without destroying authored meaning.
2. Convert every affected crdt_data_attempted_value.value envelope using Serverpod's serialization format.
3. As the final step, update the corresponding CrdtSchemaColumn type and policy identity to the target values reported by the exception.

The final registry update is the migration's explicit attestation that all dependent data was converted. If conversion fails before that step, the old identity remains and initialization fails again safely. If the developer converts the data but omits the attestation, the same actionable exception is raised on the next start. Once the stored identity matches, initialization trusts the migration and does not rescan attempted values.

This is an intentional trust boundary. Updating CrdtSchemaColumn without correctly converting the data can cause a later deserialization failure; proving otherwise would require the costly startup scan this model rejects. Manual migrations already carry equivalent responsibility for domain data and constraints.

For a rename with field metadata, the migration updates the existing CrdtSchemaColumn.name so its identity survives. For a real drop, it explicitly removes the old schema column and cascading field metadata. An ambiguous drop/add with metadata fails before reconciliation mutates anything.

The future migration hook tracked by issue #87 can abstract the conversion and final attestation from application developers, but it follows the same protocol and does not make startup scan existing rows. New invalid peer input, external SQL after initialization, a live schema change, or a falsely attested manual migration can still fail at runtime as an atomic integrity or deserialization error. Unattested migration changes fail at initialization with direct repair instructions.

Rejected alternatives

• Ignoring hidden-row materialization: hidden values remain observable, exported, and indexed, so freezing them makes projection history-dependent. Unique conflict grouping is nevertheless intentionally visible-only after hidden rows have been unconditionally released.
• A hidden unique-FK guard: it preserves stale materialization and merely moves failure to a later claim or restoration.
• Extending releaseOnDelete: it misses remote/cascade hiding and later authored changes.
• Partial indexes excluding hidden rows: visibility is stored outside the domain table; denormalizing it creates another engine-owned source of truth.
• Removing physical unique indexes: this hides projection defects instead of preventing divergence.
• Independent FK and unique attempted stores: a field governed by both would have competing authored authorities.
• A separate FK projection table: its candidate is recomputable planner output and its only useful persisted field was a diagnostic reason. A generic reason on CrdtDataAttemptedValue avoids another relation and table.
• Untagged JSON or a custom codec: Serverpod dynamic already preserves the wire type through className.
• One table per Dart type: it multiplies schema and migration machinery for values always addressed by field identity.
• Scanning attempted values after a type change: it proves more about a manual migration, but makes application initialization proportional to real data volume. The migration's final registry update is the explicit attestation instead.

Verification gates

Deterministic tests must cover:

• hidden plain and FK unique claims, including lone hidden claimants;
• later authored writes while hidden and deterministic restoration;
• a visible loser reclaiming its canonical attempted value after the previous winner becomes hidden, without treating hidden rows as conflict candidates;
• bootstrap/export convergence for visible and hidden rows;
• all local write paths before immediate constraint checks;
• swaps, composite indexes, overlapping indexes, and safe two-phase writes;
• incremental projection equivalence to full recomputation;
• batch partition and replay convergence, plus proof that unrelated non-FK updates do not run FK projection;
• matching schema startup without value scans;
• initialization rejection of nullable and non-nullable JSON-backed unique candidates;
• changed types with no attempted rows, and actionable failure without scanning or mutation when attempted rows exist;
• transactional conversion with CrdtSchemaColumn attested last, including repeated failure when the attestation is omitted;
• normal write and sync rejection of malformed or incorrectly typed dynamic values;
• rename and drop migrations;
• sync rejection for incompatible type or projection schemas; and
• rebuild and mutation validation proving canonical-claim substitution and unconditional hidden-row release are required.