octabits

@octabits-io/data-lifecycle

What happens to a scope’s data over time, made checkable. A scope is whatever your rows belong to — a tenant, an organisation, a workspace, an account — and every table carrying the scope column is a table that must do something definite when the scope is purged, exported, isolated or simply ages. This package holds the checks that keep those answers honest, as pure functions with no dependency at all, and the runner that acts on the one answer that is a calendar.

import { checkTableDispositions, createRetentionRunner } from '@octabits-io/data-lifecycle';
import { describeDatabase, introspectForeignKeys } from '@octabits-io/data-lifecycle/postgres';
import { describeDrizzleSchema, restrictTo } from '@octabits-io/data-lifecycle/drizzle';
Import Depends on What it is
@octabits-io/data-lifecycle nothing The checks over a structural SchemaDescription, and the retention runner. Runs anywhere.
./postgres nothing (structural executor) introspectForeignKeys, introspectScopedTables, describeDatabase, introspectRowSecurity, introspectCurrentRole, and the live isolation audits auditScopeCoverage and auditAppRole, over any query(text, params) — a pg Pool, PGlite, a postgres.js wrapper.
./drizzle drizzle-orm (optional peer) describeDrizzleSchema, the derive* helpers, introspection through a Drizzle instance, restrictTo for retention runners.

When not to use it

  • To delete, export or anonymise. Nothing here issues a DELETE, reads a row for export, or rewrites one. The checks hold your manifests to the schema; the purge, the export and the retention runners are code you write and this The access and erasure requests themselves — sources per category, erasure steps per source, the coverage check between them, the cover sheet, the honest outcome and the request log — are @octabits-io/data-subject; its steps and this package’s dispositions describe the same tables from two sides. package calls or verifies.
  • As the isolation runtime. auditRlsCoverage is a static read of Drizzle schema source and migration SQL — it looks for pgTable( and the ENABLE / FORCE ROW LEVEL SECURITY and CREATE POLICY statements, not at pg_policies. It reports what your source says, not what a live database enforces, and it sets no GUC: per-request scoping is @octabits-io/rls/drizzle or @octabits-io/rls/prisma, and the policies are yours. auditScopeCoverage is the live counterpart: it reads pg_class and pg_policy after your migrations ran, and still sets nothing.
  • A schema without a scope column. Every check runs over the list of scoped tables you hand it. A single-scope application has no scope to purge or isolate; what is left for it is the retention runner and, over whatever table list it passes, the export classification.
  • Scheduling. createRetentionRunner is the sweep; the clock is yours — a cron, a pg-boss schedule, a Kubernetes CronJob.
  • A data-subject-request workflow. No request queue, no deadline tracking, no data map for a person to browse. This is the CI test that says every scoped table has an answer; the process around it is a product of yours.

The five questions, and the check for each

Purge. When the scope goes, every scoped table is either deleted explicitly, cascaded from something that is, or retained on purpose with a reason. checkTableDispositions holds a manifest to that: nothing unclassified, nothing stale, the purge order and the purge dispositions the same set, every cascade pointing at something itself removed, every retain justified. checkCascadeDispositions then asks the database’s foreign keys whether each cascade claim is real — a wrong claim is silent in unit tests and either retains a purged scope’s rows or aborts the purge — and findBlockingTables asks, independent of the manifest, what would block DELETE FROM <root>.

Export. Before a data-subject export can sweep every scoped table, every encrypted column (bytea) and every credential-shaped one (*token*, *secret*, *hash*, … as a string type) is classified: decrypt with a named regime, omit with a reason, or emit plain with a reason — so “nobody looked” and “somebody checked” stay distinguishable. sensitiveColumns derives the set; checkExportDispositions holds the manifest to it, and a mustOmit list names the columns that may never be emitted in any form.

Personal data at rest. A column named like a person’s data — email, phone, name, first_name, street, date_of_birth, iban, tax_id, … (DEFAULT_PERSONAL_DATA_COLUMN_PATTERNS, matched in snake_case on either the property or the SQL name, and extendable with your domain’s words) — holds ciphertext, or a blind index over it, or is declared plain with the reason it may stay readable. personalDataColumns derives the set over every table, global ones included; checkPersonalDataColumns({ personalDataColumns, protectedColumns, plain }) reports a plaintext column nobody declared (personal_data_plaintext), a declaration without a reason (personal_data_plain_unjustified) and one that no longer fits (personal_data_plain_stale). Which columns are protected is the host’s to say — a column type that refuses plaintext, a declared set; the check only holds the two lists against the schema, so adding a phone column fails CI until somebody encrypts it or says why it need not be:

import { checkPersonalDataColumns, personalDataColumns } from '@octabits-io/data-lifecycle';

expect(checkPersonalDataColumns({
  personalDataColumns: personalDataColumns(describeDrizzleSchema(schema)),
  protectedColumns: ['contact.nameEncrypted', 'contact.emailEncrypted', 'contact.emailIndex'],
  plain: { 'organization.name': "a company's name, not a person's" },
})).toEqual([]);

Isolation. auditRlsCoverage reads your schema source and migration SQL and reports every scoped table without row-level security enabled, forced and policied — enabled without forced is bypassed for the owner, enabled without a policy denies every row — against a pruned allowlist, with renames modelled because Postgres carries RLS through ALTER TABLE … RENAME TO. Static; no database. A scope column pattern that matches no table is itself a finding, rls_scope_pattern_unmatched — a pattern with the wrong quotes would otherwise audit nothing and pass.

The live counterpart asks the question the static one cannot: is there a table that should be scoped and was never given the scope column? auditScopeCoverage(db, { scopeColumn, global }) describes every table of the migrated database and reports each one that neither carries the scope column under row-level security enabled, forced and policied, nor is declared global with a reason (scope_missing, rls_uncovered, rls_half_applied, and scope_global_stale for an entry that no longer fits). auditAppRole(db) checks the role the application’s connection runs as: a superuser or a BYPASSRLS role ignores every policy, so the first two checks would pass with isolation off (role_bypasses_rls). Both run in a test against PGlite in milliseconds, which is how a host turns “every table is isolated” into a check a coding agent cannot skip:

import { auditAppRole, auditScopeCoverage } from '@octabits-io/data-lifecycle/postgres';

expect(await auditScopeCoverage(owner, {
  scopeColumn: 'org_id',
  global: { user: 'sign-in identities, shared across organisations' },
})).toEqual([]);
expect(await auditAppRole(app)).toEqual([]);

checkScopeCoverage and checkAppRole are the same checks as pure functions over a description you build yourself.

Age. createRetentionRunner({ policies, runners, dateProvider, logger }) is the batched, per-policy, failure-isolated sweep for storage limitation (GDPR Art. 5(1)(e)): each RetentionPolicy names its window and the reason for it, its runner affects at most limit rows per call, the loop stops on a short batch or loudly at a ceiling, and the summary says what was affected, what was truncated and what failed. run({ scopeValues }) restricts a sweep to given scopes; the runner receives them and builds its own predicate.

Every check returns LifecycleFinding[] — { code, subject, message } — and a test asserts the list empty. Adding a scoped table or a *_token column then fails CI until somebody says what should happen to it.

Where the schema comes from

The checks read a SchemaDescription: tables with a name, a SQL name and columns with their SQL types. Three ways to get one:

// Drizzle: by export name, columns by property name
const tables = describeDrizzleSchema(schema);
const scoped = deriveScopedTables(schema, 'orgId');

// Any database, any ORM: the live schema, names = SQL names
const tables = await describeDatabase(pool);           // pg, PGlite, postgres.js
const scoped = scopedTables(tables, 'org_id');

// By hand, for a schema in whatever you use
const tables: SchemaDescription = [{ name: 'invoice', sqlName: 'invoice', columns: [{ name: 'org_id', sqlName: 'org_id', sqlType: 'text' }] }];

A Prisma or Kysely codebase takes the second path and runs every check against the migrated database, which is also the only description that can never drift from what is deployed.

A manifest test

import { checkTableDispositions, checkCascadeDispositions, findBlockingTables } from '@octabits-io/data-lifecycle';
import { deriveScopedTables, sqlNameOf, introspectForeignKeys } from '@octabits-io/data-lifecycle/drizzle';
import * as schema from './schema';
import { DISPOSITIONS, PURGE_ORDER } from './purge-manifest';

const scoped = deriveScopedTables(schema, 'orgId');

test('every org-scoped table has a disposition', () => {
  expect(checkTableDispositions({ scopedTables: scoped, dispositions: DISPOSITIONS, purgeOrder: PURGE_ORDER, root: 'org' })).toEqual([]);
});

test('every cascade claim is backed by the database', async () => {
  const foreignKeys = await introspectForeignKeys(db);
  const nameOf = (n: typeof scoped[number]) => sqlNameOf(schema, n);
  expect(checkCascadeDispositions({ scopedTables: scoped, dispositions: DISPOSITIONS, sqlNameOf: nameOf, foreignKeys })).toEqual([]);
  expect(findBlockingTables({ scopedSqlTables: scoped.map(nameOf), purgedSqlTables: PURGE_ORDER.map(nameOf), rootSqlTable: 'org', foreignKeys })).toEqual([]);
});

A retention runner

const retention = createRetentionRunner({
  policies: [{ key: 'mail_log', table: 'mail_log', action: 'delete', days: 90, rationale: 'delivery evidence; 90 days covers every dispute window in the terms' }],
  runners: {
    mail_log: async ({ cutoff, limit, scopeValues }) => {
      const rows = await db.delete(t.mailLog)
        .where(and(restrictTo(t.mailLog.orgId, scopeValues), lt(t.mailLog.createdAt, cutoff)))
        .returning({ id: t.mailLog.id });   // with a LIMIT via a subquery on your driver
      return rows.length;
    },
  },
  dateProvider: { now: () => new Date() },
  logger,
});
await retention.run();                          // the platform-wide sweep, from a cron
await retention.run({ scopeValues: ['org-7'] }); // one scope, on demand

What stays yours

The manifests — which table is purged in what order, which column is decrypted with which key, which tables are allowlisted out of RLS and why, which retention windows apply — and the purge itself. This package never issues a DELETE; it holds the lists to the schema and the schema to the database.

Stability

0.x: patches never break, minors say so in the changelog. The finding codes (table_unclassified, cascade_no_fk, rls_half_applied, …) are part of the surface — a test that matches on them keeps working across patches.

License

MIT

From @octabits-io/data-lifecycle@0.4.0 in the platform repository.