IF Commons GitHub

Documentation in the database

0008Active

0008-sql-documentation · owner if · domain data

The three extensions

FR-001checkpassing

The comments extension MUST, for a dialect that has COMMENT ON, make a COMMENT ON TABLE of each table and a COMMENT ON COLUMN of each column that has a description, with the description as it is, in the group of documentation; for a dialect that has none it MUST make nothing.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: PostgreSQL and DuckDB keep the description of every table and column as a comment, as governance wrote it, and SQLite is given none; suite/documentation.test.ts: a description with a quote is written with the quote doubled, a column with none has no comment

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

Written in: data/sql/extensions/documentation/comments.ts

Tests: passed: suite/documentation.test.ts PostgreSQL and DuckDB keep the description of every table and column as a comment, as gove; passed: suite/documentation.test.ts a description with a quote is written with the quote doubled, a column with none has no co

Cited by: Data, sql/extensions/documentation/comments

FR-002checkpassing

The documentation tables extension MUST make a table documentation of each table's and each column's description (table_name, column_name where the empty text names the table itself, description), keyed by the first two, and load it, where the dialect has no COMMENT ON (its default), or in every dialect, or in none, as its setting says.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: the documentation table is made where there is no comment, everywhere, or nowhere, as its setting says

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

Written in: data/sql/extensions/documentation/documentation-table.ts

Tests: passed: suite/documentation.test.ts the documentation table is made where there is no comment, everywhere, or nowhere, as its

Cited by: Data, sql/extensions/documentation/documentation-table

FR-003checkpassing

The governance tables extension MUST make the tables governance_annotation (the element, the position of the annotation among the element's, its kind and its value as JSON) and governance_trace (each table and column, as written in the database, with the governance element and the logical element it was made from), and load them, in every dialect, unless its setting is off.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: ${name}: the governance tables hold every annotation of the domain and the trace of every table and column; suite/documentation.test.ts: the governance tables are left out by a setting, or by leaving the extension out of the composition

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

Written in: data/sql/extensions/documentation/governance-tables.ts

Tests: passed: suite/documentation.test.ts the governance tables are left out by a setting, or by leaving the extension out of the co; passed: suite/documentation.test.ts duckdb: the governance tables hold every annotation of the domain and the trace of every t; passed: suite/documentation.test.ts postgres: the governance tables hold every annotation of the domain and the trace of every; passed: suite/documentation.test.ts sqlite: the governance tables hold every annotation of the domain and the trace of every t

Cited by: Data, sql/extensions/documentation/governance-tables

FR-004checkpassing

A comment MUST hold the human description of what it is on and no machine-readable governance: its text is the definition, as it is.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: PostgreSQL and DuckDB keep the description of every table and column as a comment, as governance wrote it, and SQLite is given none

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

Written in: data/sql/extensions/documentation/comments.ts

Tests: passed: suite/documentation.test.ts PostgreSQL and DuckDB keep the description of every table and column as a comment, as gove

Cited by: Data, sql/extensions/documentation/comments

Settings and composition

FR-005checkpassing

The settings MUST be a typed value: the comments (dialect, the default, or never), the documentation tables (when-no-comment, the default, always or never) and the governance tables (on, the default, or off), with the names of the tables; a setting that is not given MUST be its default.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: the documentation table is made where there is no comment, everywhere, or nowhere, as its setting says

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

Written in: data/sql/extensions/documentation/mod.ts, data/sql/extensions/documentation/settings.ts

Tests: passed: suite/documentation.test.ts the documentation table is made where there is no comment, everywhere, or nowhere, as its

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

FR-006checkpassing

Each extension MUST be an extension of the engine that contributes a statement source to physical.statements, and the defaults MUST be a list of the three, so that a project drops one by leaving it out and replaces one by its own; documentationSources MUST give the same sources without the engine.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: the governance tables are left out by a setting, or by leaving the extension out of the composition

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

Written in: data/sql/extensions/documentation/mod.ts

Tests: passed: suite/documentation.test.ts the governance tables are left out by a setting, or by leaving the extension out of the co

What the database holds

FR-007checkpassing

The tables of FR-002 and FR-003 MUST be made by the physical schema's own machinery from a logical schema of their own, so that their names are written by the case style, their types are the dialect's and their statements are CREATE TABLE IF NOT EXISTS and upserts; a table of the schema that has one of their names MUST be returned as an error value that names both.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: the tables are made by the physical machinery with the naming of the schema, and a table of the schema with their name is an error value

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

Written in: data/sql/extensions/documentation/documentation-table.ts, data/sql/extensions/documentation/fragment.ts, data/sql/extensions/documentation/governance-tables.ts

Tests: passed: suite/documentation.test.ts the tables are made by the physical machinery with the naming of the schema, and a table o

FR-008checkpassing

Loading the statements twice MUST leave the same rows in each table, and loading them after a description or an annotation changed MUST update the row and leave no row of what is gone to be read as current.

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: ${name}: a load is whole, run twice it leaves the same rows, and what changed or is gone is not read as current

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

Written in: data/sql/extensions/documentation/documentation-table.ts, data/sql/extensions/documentation/governance-tables.ts

Tests: passed: suite/documentation.test.ts duckdb: a load is whole, run twice it leaves the same rows, and what changed or is gone is; passed: suite/documentation.test.ts postgres: a load is whole, run twice it leaves the same rows, and what changed or is gone ; passed: suite/documentation.test.ts sqlite: a load is whole, run twice it leaves the same rows, and what changed or is gone is

FR-009checkpassing

Where the governance tables are on, every annotation of the domain MUST be a row of governance_annotation, and every table and column of the schema a row of governance_trace; this is where an annotation that the schema cannot express itself is carried as data, which the flow report says (0006-logical FR-011).

Enforced (check): .github: cd commons/data && bun run verify — suite/documentation.test.ts: ${name}: the governance tables hold every annotation of the domain and the trace of every table and column

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

Written in: data/sql/extensions/documentation/governance-tables.ts

Tests: passed: suite/documentation.test.ts duckdb: the governance tables hold every annotation of the domain and the trace of every t; passed: suite/documentation.test.ts postgres: the governance tables hold every annotation of the domain and the trace of every; passed: suite/documentation.test.ts sqlite: the governance tables hold every annotation of the domain and the trace of every t

Cited by: Data, sql/extensions/documentation/governance-tables

Out of scope

  • The descriptions of views and routines: they are built with them.
  • A description in another language, and the history of a description: a later extension.
  • Row-level security and the other things a governance annotation may become: their own extensions.
  • Reading the tables back as a governance domain: a later extension.

Edge cases

  • PostgreSQL by default: COMMENT ON for every table and column, no documentation table, and the governance tables, per FR-001, FR-002 and FR-003.
  • SQLite by default: no comment (it has none), a documentation table, and the governance tables, per FR-001 to FR-003.
  • PostgreSQL with the documentation tables always: both the comments and the table, per FR-002.
  • A project that leaves the governance extension out: no governance table, per FR-006.
  • A description with a quote in it: written with the quote doubled, per FR-001 and FR-007.
  • A concept named documentation: an error value naming it and the table of the extension, per FR-007.
  • A column with no description: no comment and no row, per FR-001 and FR-002.
  • An annotation of a kind no vocabulary knows: a row like any other, its value as JSON, per FR-009.
  • A closed code list, which has no table: its annotations are rows of governance_annotation and it has no trace, per FR-009.

Assumptions

  • A governance element's identity is its text (arcaParty/person.givenName), which is what the tables hold.
  • The value of an annotation is JSON; where a value cannot be (it holds a function), it is not a governance annotation, and the projection would not carry it.
  • COMMENT ON in PostgreSQL and in DuckDB means the same: a description kept with the object.
  • The governance tables are small and are loaded whole: a project that wants a history keeps one of its own.

Open questions

OQ-1open question

Whether the governance tables are also to carry the code lists' codes and the definitions of the elements, so that a program may read the whole of governance from the database.

Key entities

  • Comments extension, documentation tables extension, governance tables extension - the three.
  • Settings - the typed values that say which are on and how.
  • documentation, governance_annotation, governance_trace - the tables.

Success criteria

SC-001success criterion

A person who opens a database made from governance finds what each table and column is, in the way their engine shows it, and a program finds what governance says of each in a table.

SC-002success criterion

What governance said of the data is in the database, or the report says where it is not.

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/documentation/comments, sql/extensions/documentation/documentation-table, sql/extensions/documentation/governance-tables, sql/extensions/documentation/mod, sql/extensions/documentation/settings, SQL features as extensions

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