The SQL snapshot of the corpus
_source-oldproject/ goes neither into a Docker image nor into a CI runner. Without another
mechanism, a deployed environment would therefore have no technology, no event type and no
template: a database with a complete schema and an empty catalogue. The SQL snapshot fills that
gap. It lives in packages/database/prisma/seed/corpus/, it is versioned in plain text, and it is
what CI and the three environments seed from.
One command regenerates it:
pnpm db:snapshot [corpus-path]
The path is optional: failing that, _source-oldproject at the root of the repository. The
following arguments are passed to the import as-is —
pnpm db:snapshot ../elsewhere --overwrite-templates.
Why photograph rather than port
The corpus lives outside the repository: it carries the production intake keys of the 65 SEKOIA
communities, and both .gitignore and .dockerignore keep it out. Probing it then starts a
Python interpreter — the import does not read files there, it really calls the
generators. Neither the folder nor Python belongs in a
production image.
The SQL produced here asks for none of that: it replays with a PostgreSQL connection, and that is all.
The source of truth remains the corpus. export-corpus.ts reads the database after the import
has written it, so that no assembly, rewriting or pool identification rule is reimplemented here.
That is what stops the two paths from diverging: there is only one reader of the corpus, and it is
not the exporter.
The command's three gestures
| # | Gesture | Why |
|---|---|---|
| 1 | Build of @sekoiacraft/database | The seeds import it by name, so through exports, so from dist/ |
| 2 | prisma/seed/import-source.ts <corpus> | Writes the corpus into the database — the ordinary import, pnpm db:seed's |
| 3 | prisma/seed/export-corpus.ts | Reads the database back and writes it as SQL, one file per technology |
The order is deliberate: it imports, then photographs. Exporting without reimporting would freeze whatever the database holds at the moment of the gesture — that is, sooner or later, a stale corpus taken for the current one.
It requires the corpus, where pnpm db:seed does without
That is the difference that matters between the two commands.
pnpm db:seed chooses: the corpus when it is present, the snapshot
otherwise. pnpm db:snapshot refuses: without a corpus it exits with code 1 rather than copying
the existing snapshot while presenting itself as a regeneration.
The corpus, python3, and a reachable DATABASE_URL. The command needs a working database: it
imports into it, then reads it back. Unlike db:push and db:reset, it does not check that the
target is local — the import only writes through upsert and drops nothing — but the target is
announced before it starts. Read it.
What it produces
The directory is rebuilt on every run: a technology removed from the corpus must see its file disappear, otherwise loading would recreate it.
| File | Contents |
|---|---|
_shared.sql | The pools no technology owns and the six demonstration populations. Loaded first: event types from several technologies attach to them |
<technology>.sql | One technology, its datasets, its event types and their templates |
manifest.json | The loading order, the counts the loading must produce, and the fingerprint of the rules that shaped the templates |
Today 30 files, about 12 MB, from the largest (sekoia-agent.sql, 4.6 MB) to the smallest
(office-365-message-trace.sql, 20 kB). Each file opens on a header stating its volume and
reminding you that it is generated — do not edit by hand.
Rows are written in code-point order, never in the order of the server's ORDER BY: that one
follows the collation, and PGlite (C) and a PostgreSQL in en_US.UTF-8 — which ignores hyphens —
sort windows.sql and bind.sql differently. The same corpus thus yields the same files, whatever
the intermediate database.
What the manifest announces:
| Manifest counts | Value |
|---|---|
| Technologies | 28 |
| Datasets | 398 |
| Records | 10,878 |
| Event types | 474 |
| Templates | 474 |
The SQL is replayable: every INSERT carries its ON CONFLICT … DO UPDATE. Reloading an
already-loaded database updates it instead of failing. The identifiers are gen_random_uuid(),
never frozen UUIDs, and a dataset is found by its slug — a frozen identifier would go stale on
the first reimport.
Hence the gap with the figures of the Importing the corpus page, which
describe the corpus itself. export-corpus.ts reads what is in the database at the moment of the
gesture: the corpus's event types, minus the one
set aside, plus the four Netskope administration gestures
seed-netskope-admin.ts adds, plus the three catalogues the repository owns outright
(web-destinations, mail-client-process, script-process) and which no corpus pool provides.
The loading order is computed, not alphabetical
An event type can attach to another technology's pool — two do so today, at SentinelOne Cloud
Funnel towards a Github Audit Logs pool. The file creating the pool must therefore come before the
one attaching to it, and that is why sentinelone-cloud-funnel.sql closes the march where the
alphabet would place it in the middle.
The order is a topological sort on a single kind of edge — “an event type of A attaches to a
pool of B, therefore B before A” — broken alphabetically so that an unchanged corpus always yields
the same order. It is written in manifest.json, and the loader abides by it.
The failure mode this avoids is silent, and that is what justifies a safeguard rather than a
rule: an unresolved attachment does not make the loading fail, it leaves the template rendering with
sample = {} — hence invalid JSON, from which the intake extracts no field at all and not one smart
description triggers. tests/corpus-snapshot.spec.ts holds the invariant: no dataset is cited
before having been created.
What the SQL deliberately does not carry
| Absent | Why |
|---|---|
EventTypeAction (the coverage) | Inferred in code by deriveActionCoverage(), which runs on both paths |
EventType.scope / scopeOrigin | Written by the same pass, for the same reason |
| The parents of the foreground pools | Laid down by completePoolParents(), also on both paths |
createdAt | The column has a default; emitting it would move the diff of every file on every export |
Freezing them would amount to preventing a code correction from taking effect on a database seeded
from the snapshot — exactly the convergence the origin column of EventTypeAction exists to
guarantee. The SQL carries the corpus; what is inferred from it stays inferred.
No convergence DELETE … NOT IN (…). It would be stricter than the import itself, whose written
contract is “creates and updates, never removes”. Such a DELETE would take away a dataset created
from the Datasets screen or an event type added by hand, the catalogues being
editable. An accepted counterpart, the same as on the corpus side: an event type removed from the
corpus survives on an already-seeded database.
Who loads the snapshot
pnpm db:seed, as soon as_source-oldproject/is absent. The switch is automatic and announced: “Corpus missing from … — loading the SQL snapshot”. The final report is then different from the import's: most of its counters measure the assembly, work the snapshot has already frozen, and showing them at zero would read as “no template rewritten” where it should read “the question does not arise here”.- The
migratecontainer of the four compose files (local,dev,preprod,prod). It lays down the schema, then runs the sameimport-source.ts— which falls back, the corpus being outside the image — before the other seeds. The backends wait for it to succeed. - A workstation without Python, or freshly cloned, through the same switch.
Loading goes through pg and not through Prisma: $executeRawUnsafe only accepts one statement at
a time, where a file carries thousands, and PostgreSQL's simple protocol swallows a whole batch in
one go. It happens in a single transaction — a file failing halfway would otherwise leave a
database with half the technologies. Before committing, the observed counts are confronted with the
manifest's: a snapshot regenerated without its manifest being regenerated too would otherwise leave
incomplete, silently.
When to replay it
After a refresh of _source-oldproject/, obviously. But also — and this is the case that gets
forgotten — after any change to the template assembly or rewriting rules
(template-rewrites.ts, template-assembly.ts). Those rules are baked into the SQL: on the
fallback path, they are not replayed. A correction not followed by a pnpm db:snapshot therefore
leaves CI and the deployed environments on the old rendering.
That is why a test now catches it. manifest.json carries templateDigest, a SHA-256 of the five
modules that shape a template's text — template-rewrites.ts, template-assembly.ts,
excluded-event-types.ts, source-registry.ts and extract-source-generators.py — and
snapshot-digest.spec.ts recomputes it. Touching any of them, a comment included, turns CI red until
the snapshot is regenerated. The import code of pools and populations is left out on purpose: it
lives next to passes that run on both paths, and a safeguard that fires for nothing is one people
stop listening to.
pnpm db:snapshot # imports the corpus, then regenerates prisma/seed/corpus/
git add packages/database/prisma/seed/corpus/
The snapshot is versioned in plain text, one file per technology, precisely so that the diff can be read: it shows which event types and which templates changed. Read it before committing — it is what CI and the deployed environments will seed.
When it goes wrong
| Symptom | Cause | What to do |
|---|---|---|
| “Source corpus not found” | _source-oldproject/ missing | Drop it at the root, or pass its path as an argument |
DATABASE_URL missing | No .env at the root | cp .env.example .env |
python3: command not found | Probing executes the corpus's source | Install Python 3 |
| “The database holds no technology: nothing to photograph” | The import failed, or the database was emptied between the two gestures | Rerun the whole command, never the export alone |
| “Attachment cycle between technologies” | Two technologies attach to each other's pools | The corpus is unsolvable as it stands: the cycle breaks at the source |
| On loading: “The snapshot did not produce what its manifest announces” | Files and manifest out of step | Regenerate the whole snapshot, never a single file |
snapshot-digest.spec.ts red: “…changed without pnpm db:snapshot” | A template rule was edited, the snapshot was not regenerated | pnpm db:snapshot, read the diff, commit corpus/ with the change |