Spectral Foreign Keys

IntegrityHigh-IngestEventual Convergence

Stable On by default · production-ready

What it is

In a traditional database, every INSERT into a child table triggers a blocking lookup against the parent’s primary-key index. At high ingest rates — with rows arriving from many sources at once, and parent rows occasionally arriving milliseconds after their children — that blocking lookup becomes the dominant cost, and orphaned children become a constant source of failed loads. Spectral foreign keys replace the synchronous lookup with an eventually-convergent integrity model.

When a child row arrives, the engine performs a fast local membership check against a summary of the parent’s primary keys. If the parent definitely exists, the row is accepted and tagged valid. If the parent is missing — or might be missing — the row is still accepted immediately, but the foreign-key column is marked spectral: a third state alongside “valid” and “null”, meaning “the reference is unresolved right now, and the database will reconcile it in the background.”

Why it matters

Out-of-order arrivals are the rule, not the exception, at high ingest rates. Spectral foreign keys turn that reality from a data-loss problem into a non-event.

• Ingest never blocks on referential integrity — the check is local work, not a network round-trip.
• Late-arriving parents auto-heal their children — once the parent shows up, the database silently flips every matching child from spectral to valid.
• Configurable expiry policy — if a parent never arrives within the window you choose, the row can be nulled, routed to a dead-letter table, or rejected.

flowchart LR
  classDef a fill:#1f2640,stroke:#7c5cff,color:#e8eaf0
  classDef b fill:#1f2640,stroke:#00d4ff,color:#00d4ff
  classDef g fill:#1f2640,stroke:#4ade80,color:#4ade80
  classDef w fill:#1f2640,stroke:#facc15,color:#facc15

  CR[Child row arrives]:::a --> CHK{Membership check<br/>against parent summary}:::b
  CHK -- definitely present --> OK[Accepted: valid FK]:::g
  CHK -- maybe absent --> SP[Accepted: spectral FK<br/>marked unresolved]:::w
  SP --> CONV[Background convergence<br/>re-checks on parent arrival]:::b
  CONV -- parent appears --> OK
  CONV -- window expires --> POL[Apply expiry policy]:::w

How you use it

Declare a foreign key with the spectral clause. Choose how long unresolved references should be tolerated and what should happen if the parent never arrives. From there, the database does the rest.

-- A high-ingest orders table that references customers.
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT,
  amount      DECIMAL(12, 2)
);

ALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  SPECTRAL CONVERGENCE WINDOW INTERVAL '24 hours'
  ON SPECTRAL EXPIRE NULLIFY;

-- See which rows are still waiting for their parent.
SELECT id, customer_id, _spectral_state, _spectral_since
FROM orders
WHERE _spectral_state = 'UNRESOLVED';

-- Standard JOINs behave as you expect.
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;       -- excludes spectral rows

SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;  -- includes them as NULL

The bare INNER JOIN behaves exactly as relational correctness demands — rows without a confirmed parent are not returned. LEFT JOIN keeps them with a null parent. Aggregations on the child table itself include spectral rows, because the child data is fully valid even when the bond is still resolving.

Maturity

Spectral foreign keys ship in layers, and not every layer is equally mature:

• Stable — the SPECTRAL CONVERGENCE WINDOW / ON SPECTRAL EXPIRE DDL, the _spectral_state and _spectral_since system columns, and SPECTRAL OFF strict mode (a traditional synchronous local FK check).
• Beta — single-node background convergence and auto-heal, the NULLIFY / DEAD_LETTER / REJECT expiry policies, and JOIN-time spectral filtering. Use these in development and pilot environments.
• Roadmap — cluster-wide convergence (an out-of-order arrival on one node converging from a parent ingested on another) and a convergence-statistics dashboard.

The DDL is forward-compatible: write the spectral clause once and it is accepted today, gaining the later behaviour automatically as those layers light up. No schema migration is required between stages.

Key concepts

Concept	Meaning
Spectral null	A third foreign-key state: not null, not yet confirmed valid.
Convergence window	How long the engine keeps trying to resolve a spectral reference.
Expiry policy	What happens at window end: NULLIFY, DEAD_LETTER, or REJECT.
_spectral_state	A virtual system column exposing the current resolution status of each row.

Notes & caveats

Tip

Use a generous convergence window for streaming ingest from independent sources, and a short one for synchronous workloads where you genuinely want to know quickly if a reference is wrong. Both run on the same machinery.

Heads up

The membership check is probabilistic — a small fraction of “maybe present” answers turn out to be false positives. These are caught and reclassified by the next convergence sweep; no row is ever silently linked to a parent that does not exist.

Graph-Relational Duality →

How spectral edges plug into the property-graph surface.

Storage Lifecycle →

How parent data that tiers to cold storage still anchors its children.

Configuration & Availability →

The exact flag and its default.