IF Commons GitHub

Integrations

0022Active

0022-sql-integrations · owner if · domain data

The integration

FR-001checkpassing

loader MUST resolve an integration (a name, a table and the columns of the contract by the words of their names, the source, the strategy and the key) against a logical schema, a dialect and a naming, and MUST give errors as values for a table or column the schema does not have, for a column the target requires (not optional and with no default) that the contract leaves out, and for an upsert or snapshot whose key is empty or is not in the contract; the key is the table's primary key where it is not said, and the strategy is upsert where it is not said.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: an integration is resolved by the words of its names, and a table or column that is not there, a required column left out and a missing key are errors as values

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/integration.ts, data/sql/extensions/integrations/mod.ts

Tests: passed: suite/integrations.test.ts an integration is resolved by the words of its names, and a table or column that is not th

Cited by: Data, sql/extensions/integrations/integration

Sources

FR-002checkpassing

A source MUST be one of: rows written in TypeScript (all with the same columns, which are the contract), CSV text (read by the library, with quoted fields, doubled quotes and a delimiter that is a setting, an empty field being null, and each field cast to its column's type), a table of another database file of the same engine (attached for the load and detached after it, for a dialect that says it can attach), or an adapter (a typed hook that reads rows, or says why it could not); a source that the dialect cannot read, a failing adapter and an adapter row with a column outside the contract MUST each be an error value that changes nothing.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: CSV text is parsed with quotes and read into the target's types, an empty field is null, and a header the contract does not name is refused; suite/integrations.test.ts: a table of another database file is attached for the load and detached, on the engines that can attach, and refused as a value on the one that cannot; an adapter gives rows, fails, or gives a column the contract does not

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/load.ts

Tests: passed: suite/integrations.test.ts CSV text is parsed with quotes and read into the target's types, an empty field is null, a; passed: suite/integrations.test.ts a table of another database file is attached for the load and detached, on the engines tha

The load

FR-003checkpassing

A load MUST land the source in a temporary table of the contract's columns typed as the target's, and MUST run everything after landing in one transaction that is rolled back where anything fails, and MUST drop the landing whether the load succeeded or not.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: on three engines each strategy loads as it says, an upsert run twice leaves the rows as after once, a failure rolls back, and the landing is gone

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/load.ts

Tests: passed: suite/integrations.test.ts on three engines each strategy loads as it says, an upsert run twice leaves the rows as af

FR-004checkpassing

The strategies MUST be: append (every landed row inserted), upsert (a landed row whose key is in the target changes that row, any other is inserted), replace (the target's rows deleted and the landed rows inserted) and snapshot (the target made to hold the landed rows by key: upserted, and a row whose key was not landed deleted); an upsert run twice MUST leave the rows as after once.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: on three engines each strategy loads as it says, an upsert run twice leaves the rows as after once, a failure rolls back, and the landing is gone

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/load.ts

Tests: passed: suite/integrations.test.ts on three engines each strategy loads as it says, an upsert run twice leaves the rows as af

FR-005checkpassing

Before it loads, a load MUST find, in the landing, a key that is there more than once (for upsert, snapshot and replace) and a null in a column the target requires, and MUST refuse, as an error value that names the count and the column and leaves the target unchanged, a landing that has either.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: duplicate keys, a missing required value, an empty snapshot and a snapshot that would delete most of the target are refused and change nothing

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/load.ts

Tests: passed: suite/integrations.test.ts duplicate keys, a missing required value, an empty snapshot and a snapshot that would dele

FR-006checkpassing

A snapshot MUST refuse a landing with no rows, and one that would delete a larger share of the target's rows than the setting allows (half by default), as an error value that leaves the target unchanged, unless the setting says otherwise.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: duplicate keys, a missing required value, an empty snapshot and a snapshot that would delete most of the target are refused and change nothing

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/load.ts

Tests: passed: suite/integrations.test.ts duplicate keys, a missing required value, an empty snapshot and a snapshot that would dele

The record

FR-007checkpassing

A successful load MUST add one row to a ledger table (the integration's name, its sequence, a label of the source, the hash of the landed rows, the strategy and the counts) made IF NOT EXISTS, in the same transaction as the load; and where the setting says so (by default for append only), a load whose landed rows have the hash of the last load of that integration MUST change nothing and report that it was unchanged.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: every load is a row of the ledger with its counts and the hash of what it landed, and an append of rows that have not changed does nothing

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/load.ts

Tests: passed: suite/integrations.test.ts every load is a row of the ledger with its counts and the hash of what it landed, and an a

FR-008checkpassing

A report MUST give the number of rows landed, inserted, updated and deleted, the hash of what was landed, and whether the load was made or unchanged.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: on three engines each strategy loads as it says, an upsert run twice leaves the rows as after once, a failure rolls back, and the landing is gone; suite/integrations.test.ts: every load is a row of the ledger with its counts and the hash of what it landed, and an append of rows that have not changed does nothing

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/load.ts

Tests: passed: suite/integrations.test.ts on three engines each strategy loads as it says, an upsert run twice leaves the rows as af; passed: suite/integrations.test.ts every load is a row of the ledger with its counts and the hash of what it landed, and an a

FR-009checkpassing

lineageOf MUST give the edges of an integration (the source to its landing, the landing to its target) as values in a stable order, and upstream MUST give every node whose rows reach a node, nearest first.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: lineage is the edges from the source to the landing and the landing to the target, and what reaches a node is found nearest first

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/integration.ts

Tests: passed: suite/integrations.test.ts lineage is the edges from the source to the landing and the landing to the target, and wha

Cited by: Data, sql/extensions/integrations/integration

Settings

FR-010checkpassing

The ledger's table, the landing's name, when an unchanged load is skipped (append, all or none), the share of a snapshot that may be deleted, and the CSV delimiter MUST each have a default and be changeable.

Enforced (check): .github: cd commons/data && bun run verify — suite/integrations.test.ts: every load is a row of the ledger with its counts and the hash of what it landed, and an append of rows that have not changed does nothing

Proved by: data/suite/integrations.test.ts

Written in: data/sql/extensions/integrations/settings.ts

Tests: passed: suite/integrations.test.ts every load is a row of the ledger with its counts and the hash of what it landed, and an a

Cited by: Data, sql/extensions/integrations/settings

Out of scope

  • Reading a file from disk: the caller reads it; the library parses text.
  • Loading from an attached database of another engine.
  • Change data capture and streaming.
  • Transforms and queries: a later part of this phase.

Edge cases

  • A source with no rows: lands nothing; an upsert, append and replace load nothing (replace empties the target), a snapshot refuses, per FR-006.
  • A CSV with a header the contract does not name: the contract's columns are taken by header name, and a header that is not a column is refused, per FR-002.
  • An attached source on PostgreSQL: an error value, per FR-002.
  • A load run twice with append and the same rows: the second is unchanged, per FR-007.

Assumptions

  • The target tables exist; the lifecycle makes them.
  • A key that is present in the target and in the landing is the same row.

Open questions

OQ-1open question

Whether the load's ledger is also a table of the schema, made by bind, so that convergence does not read it as drift; for now the convergence ignore list names it.

Key entities

  • Integration - a source, a contract, a strategy and a key, for one table.
  • Load - what an integration does on one run.
  • Landing - the temporary table the source is put in first.
  • Ledger - the record of what each load did.

Success criteria

SC-001success criterion

Rows from outside come in once, are checked before they touch a table, can be loaded again without harm, and are accounted for.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/extensions/integrations/integration, sql/extensions/integrations/load, sql/extensions/integrations/settings, sql/physical/dialect

Titles, headings, exports, tables, columns and requirements. Esc closes.