Synedre OS data layer

The data layer

This page describes how the Synedre OS agentic harness reads and writes its data: a central PostgreSQL database, an adapter that translates legacy MySQL SQL to PostgreSQL on the web interface side, Python entity classes on the agentic tooling side, an ORM used as schema-as-code for DDL and migrations, and finally the naming conventions — table prefixes, internationalisation, polymorphism — that hold everything together. It is intended for engineers taking over the codebase.

1. The central database

All mother-ship data lives in a single PostgreSQL schema, hosted in a dedicated container on the mother-ship VPS.

Element	Description
Container	The mother-ship PostgreSQL container
Database	The main database (distinct from the container name)
Schema	The central harness schema
DB user	A dedicated user, defined by environment variable
Password	Carried by the .env / .env.host file, never in plain text — an exception is raised if absent

Watch out for this recurring pitfall: the container and the database carry distinct names. Harness tables live in the central schema, not in the public schema.

Harness / multi-tenant boundary

This chapter documents exclusively the central schema of the Synedre OS harness (single-database, private agentic cockpit). CodeMyShop tenant databases use the same PostgreSQL adapter (see §4–5), but their data never crosses the harness schema: each tenant has its own database, its own schema, and its own tables. §4 covers the adapter because it also serves the cockpit — but any reference to tenants there is contextual, not constitutive of the harness.

Three access paths

Two paths operate at runtime (DML: reading/writing rows); a third is involved at design time (DDL: evolving the structure):

                   ┌──────────────────────────────────┐
                   │  PostgreSQL — central database     │
                   │  harness schema                    │
                   └───────────────┬──────────────────┘
                                   │
   ┌─────────────────┬─────────────┴──────────┬─────────────────────┐
   │  DML runtime    │  DML tooling            │  DDL / structure     │
   │  Web interface  │  Agentic Python         │  Schema-as-code ORM  │
   │  TCP pool       │  SQL execution via      │  generation +        │
   │  (postgres-js)  │  DB container           │  SQL migrations      │
   └─────────────────┴─────────────────────────┴─────────────────────┘

Both DML paths address the same schema but via different transports:

• Web interface side: a TCP connection pool (postgres-js library).
• Python tooling side: SQL execution via the container — writes go through a temporary .sql file that is copied then executed; reads use an inline SQL command. No path goes through a stdin pipe.

The third path does not manipulate rows: it is the DDL as schema-as-code (Drizzle ORM) that serves as the authoritative source for the structural evolution of tables — detailed in §6.

Table family status (snapshot 2026-05-31)

Family	Tables	Scope
Private legacy tables (prefix ps_ac_)	159	Legacy CodeMyShop / cockpit (debt to migrate)
Synedre OS cockpit tables (prefix sy_)	69	Agents, worksites, runs, negotiations…
Native PrestaShop tables (prefix ps_)	18	Historical debt: products, categories, translations, languages…
Public PaaS OSS tables (prefix cs_)	1	See note below

Notes regarding the public PaaS tables:

• The table that logged cockpit errors has migrated to the cockpit family (sy_): it records cockpit errors, not product errors.
• Conversely, the bulk SEO queue table has joined the PaaS family (cs_) — enqueued by the CodeMyShop back office, it lives primarily in tenant databases; its presence here is a vestige of its creation (empty table on the central database, no active writer).

In addition, there are 16 views in the central schema (see §3.4 — critical point: the agents view depends on the corresponding cockpit table).

⚠️ The exact count evolves with each worksite; the figures above are a snapshot. The ground-truth query is given in §7.

2. Table families (prefixes)

A table's prefix encodes its scope and ownership regime.

Prefix	Scope	Status	Representative examples
cs_	Public PaaS OSS (core + community)	Target	FAQ, homepage blocks (in tenant databases)
sy_	Synedre OS internal agentic cockpit	Target	Worksite tasks, agents, runs, negotiations
ps_ac_	CodeMyShop / cockpit private legacy	Debt to migrate	Worksites, scars, client VPS
ps_	Native PrestaShop	Historical debt	Products, categories, translations, languages

Cross-cutting conventions

• Singular name (cs_faq, not cs_faqs) — aligned with the native PrestaShop convention.
• One table = one parent entity; no catch-all table.
• Source files in kebab-case; Vue components in PascalCase.

Prefix migration history

The cs_ prefix results from a single rename carried out during the upgrade to version 0.2.0, from the former prefix ps_ac_ (at the time when CodeMyShop was a PrestaShop extension). In the mother-ship schema, most tables remained under ps_ac_ (the debt has not yet been settled), while the cs_ prefix is applied in OSS tenant databases.

2.1 Prefix coexistence within a single logical entity

A point that surprises engineers taking over the codebase: a single logical "worksite" entity is spread across two prefix families — legacy AND cockpit.

Table	Family	Role
Main worksite table	Legacy (ps_ac_)	One row per worksite
Granular work-item table	Legacy (ps_ac_)	N work items per worksite
Atomic task table	Cockpit (sy_)	N tasks per work item
Cockpit satellites (team, multi-session lock, QA…)	Cockpit (sy_)	Orchestration and control data

Detail revealing the history: the atomic task table was renamed at the level of its visible name, but its primary key sequence and the name of its PRIMARY KEY constraint retain the old prefix. The rename affected the table name, not all dependent objects — this must be taken into account when inspecting the PostgreSQL catalogue.

Data Model of the Main Tables

The Projects Table

The central projects table relies on an auto-incremented primary key. Notable columns are as follows:

Column	Type	Role
codename	varchar(64) NOT NULL	Unique kebab-case identifier for the project
title	varchar(255) NOT NULL	Human-readable label
client_id	varchar(32)	Reference to the associated tenant; NULL = internal project
status	varchar(9), default 'planning'	Current project status
priority	varchar(2), default 'P2'	Priority level
current_focus	text	Immediate objective currently in progress
deadline	date	Target deadline
notes / mission_letter / preprod_test_plan	text	Free-form documentation, mission brief, acceptance test plan
external_contacts	text	External contacts associated with the project
ship_command	varchar(255)	Associated delivery command
scope	varchar(32)	Functional perimeter (constrained enum)
auto_explode	boolean, default true	Automatic decomposition into tasks
mode_auto	boolean, default false	Fully automated execution mode
max_cost_eur	numeric(8,2)	Maximum budget envelope
archived_at / archived_by	—	Archival timestamp and author
date_add / date_upd	timestamptz, default now()	Creation and last modification timestamps

The scope column is constrained by a CHECK whose enumeration covers in particular the perimeters synedre, codemyshop-oss, codemyshop-enterprise, tenant, business, juridique, negociation, and conseil. Note that the values juridique, negociation, and conseil exist in the production database but do not appear in all internal reference documents — live database introspection should be relied upon rather than static documentation.

A trigger fires after any status update: when a project transitions to the completed state, it automatically propagates the resolution to associated inbox items.

The Tasks Table

Each project decomposes into tasks. The corresponding table links each task to a parent work item (logical foreign key) and exposes the following columns:

• title — task label
• status — current state, default 'todo'
• priority — priority level, default 'P2'
• assignee_codename — codename of the assigned agent or automation
• estimated_tokens / actual_tokens / actual_cost_usd — AI consumption metrics
• recommended_model — language model recommended for this task
• position — display order within the work item
• scope — functional perimeter (constrained enum: synedre-internal, codemyshop-oss, codemyshop-enterprise, tenant-single, tenant-multi, infra, doctrine)
• visual_intent — description of what must be observable on screen after the change (NULL = non-visual task); consumed by the automated visual verification engine
• visual_url — visual verification URL (NULL = default staging URL for the project)

The visual_intent and visual_url columns were added in June 2026 via an idempotent migration (ADD COLUMN IF NOT EXISTS). They are recognised by the Python project-management layer but not yet by the web application's TypeScript schema — this is a deliberate, documented divergence: the migration was applied manually at the database level, outside the scope of the schema-drift detector, which monitors only a subset of TypeScript definitions.

Inter-task dependencies are modelled in a dedicated table, forming a directed graph.

The Error and Victory Log (Scars)

The learning log records both failures and successes. Its main columns:

• agent_codename NOT NULL — agent concerned
• error_type — error category
• description NOT NULL — narrative of the event
• root_cause — identified root cause
• corrected_by — author of the correction
• severity — severity: low | medium | high | critical
• kind — entry type: 'failure' (default) or 'victory' (recorded via the dedicated skill)
• resolved — smallint constrained to {0, 1, 2}
• tags — array of keywords (text[])
• importance — score from 1 to 10
• recall_count — number of times this entry has been recalled
• learnable — indicates whether the entry is usable for learning

This log is strictly internal: it has no language column, and its content is not intended for end users.

Agents: a Compatibility View, Not a Physical Table

Resume trap. The physical agents table is distinct from the view that most queries target. The view is a backward-compatibility shim produced by a past migration.

The physical agents table exposes the columns codename, nickname, role, group_name, active, various workstation configuration columns (job_*), cognitive_frame, and heritage. The compatibility view is a direct projection of it (SELECT … FROM <physical-table>), fully transparent for reads — existing queries work without modification.

The same view-shim mechanism covers approximately a dozen other table/view pairs, ensuring continuity of historical references. In total, the database hosts sixteen views, including:

• Twelve compatibility views covering agents, automations, activity, heartbeats, relationships, experience, behaviours, logs, and advanced automation
• One Atlas spawn-event view
• Three SRE analytical views:
  • Daily error aggregation view — count of log entries from the past 14 days, broken down by tenant, day, severity, and type
  • Rollback tracking view — over runs from the past 14 days: total run count, rollback count, rollback rate as a percentage, number of blocking verdicts, date of last run
  • Warning frequency view — over 30 days, frequency of each warning code emitted by runs, number of associated blocking verdicts, last occurrence

Warning. A unified error view (merging client-side and server-side errors) is referenced in the enterprise product manifest and queried by the API, but it is not instantiated in the mothership's main database: the client-side error table does not exist there. The actual count is therefore sixteen views, not seventeen. This view does exist, however, in the product-specific databases, where it automatically followed a table rename in August 2026 — a PostgreSQL view stores an OID rather than a name, making it immune to renames.

To be confirmed. These compatibility views are most likely read-only. Any write operation (INSERT, UPDATE, DELETE) must target the underlying physical table, not the view. This must be verified systematically before modifying any agent records.

Runtime Database Access — Multi-Tenant Architecture

Tenant Resolution and Adapter Selection

Database access within the web application follows a two-step resolution chain. For each incoming request, the system first determines which tenant it belongs to, then selects the appropriate database adapter.

Step 1 — Tenant identifier resolution: the resolution logic consults the following sources, in order of priority:

1. The application runtime configuration (runtimeConfig.clientId), defined at the client VPS level — each tenant VPS carries its own identifier.
2. The request hostname: a keyword is extracted from the hostname, normalised (stripping the version suffix -v2, removing hyphens), then matched against the keys of the database configuration map.
3. If no match is found, the mothership's default identifier is used.

Step 2 — Adapter selection: once the tenant identifier is known, the system checks whether PostgreSQL access is authorised for that tenant. Two conditions must both be true simultaneously:

• The global PostgreSQL flag must be enabled (see below).
• The tenant must appear in the mothership tenant list or in the explicit activation list defined by environment variable.

If either condition fails, the request is rejected: the MySQL path was removed during a prior migration phase (end of April 2026) and is no longer available as a fallback.

The Global PostgreSQL Flag — Critical Watchpoint

Caution — risk of total outage. A global flag controls PostgreSQL access for all tenants, including mothership tenants. If the corresponding environment variable does not contain the wildcard character *, no tenant can access the PostgreSQL database, regardless of its presence in the activation lists. In production, this flag is set to * (universal access). Clearing this variable is equivalent to cutting all PostgreSQL access in a single operation.

This mechanism is a legacy of the progressive migration strategy: during the initial phase, only certain modules were enabled on PostgreSQL; the wildcard character was introduced at the time of the global switchover. It remains today as an emergency breaker for a full rollback.

The Per-Tenant Database Configuration Map

The configuration map is built dynamically at application startup from environment variables following the convention NUXT_TENANT_DB_<IDENTIFIER>, where <IDENTIFIER> is the tenant's codename in uppercase with hyphens converted to underscores. The value of each variable encodes, in order: the database name, host, port, username, and password — separated by commas.

A utility function allows retrieval of the list of known tenant identifiers, used in particular to broadcast global secrets to each relevant tenant database.

The convention "one tenant = one canonical codename, everywhere" applies to approximately fifteen surface points in the system (configuration, database, routing, etc.). The resolution logic described above is one of them and must always return that canonical codename.

The PostgreSQL Database Adapter

The base runtime exposes a unified query / get / run interface — identical to the former MySQL layer — but now dispatches to the postgres-js library. An SQL rewriting pipeline converts legacy MySQL queries into valid PostgreSQL SQL on the fly.

The Connection Pool

The adapter instantiates a PostgreSQL connection singleton with the following parameters:

• Host, port, user, and database are configurable via environment variables, with sensible defaults.
• The database password is mandatory: the adapter throws an error at startup if the corresponding variable is absent.
• Pool sized at a maximum of 20 connections, with an idle timeout of 60 seconds and a maximum lifetime of 30 minutes per connection.
• A target database schema is defined as a constant and automatically prefixes tables during SQL rewriting.

MySQL → PostgreSQL SQL Rewriting Pipeline

Each query passes through a series of transformations applied in the following order:

#	Transformation	Detail
1	Backticks → double quotes	`col` → "col"
2	Schema qualification	After FROM, JOIN, INTO, UPDATE, TABLE: tables belonging to the catalogue and cart families are prefixed with the target schema.
2b/2c	DATE_SUB / DATE_ADD	Converted to native PostgreSQL interval arithmetic; handles DAY, MONTH, YEAR, HOUR, MINUTE, SECOND — both as literals and with a placeholder.
2d	TIMESTAMPDIFF(unit, a, b)	→ FLOOR(EXTRACT(EPOCH FROM (b - a)) / divisor) for SECOND, MINUTE, HOUR, DAY.
—	IFNULL(a, b)	→ COALESCE(a, b) (PostgreSQL does not support IFNULL).
—	INSERT IGNORE INTO …	→ INSERT … ON CONFLICT DO NOTHING (skipped if ON CONFLICT is already present).
—	Auto-quoting of camelCase aliases	AS fooBar → AS "fooBar" — preserves case, since PostgreSQL normalises unquoted identifiers to lowercase. Exception: native PostgreSQL types (TEXT, INTEGER, etc.) are left as-is to avoid breaking CAST(x AS TEXT) expressions.
3	Positional placeholders	? → $1, $2, … via a character-by-character parser that ignores ? characters inside quoted strings.

Important — parameter safety. The placeholder transformation is limited to renaming the markers within the SQL text (? → $N). The actual binding of values is delegated to postgres-js through its parameterised query API: the parameter array is passed as-is, without interpolation into the SQL string. There is therefore no SQL injection risk on parameters.

SQL Functions Not Handled Automatically

The following constructs are not automatically rewritten by the pipeline; any endpoint that requires them must provide a dedicated PostgreSQL branch:

• ON DUPLICATE KEY UPDATE
• LAST_INSERT_ID()
• GROUP_CONCAT
• FIND_IN_SET
• DATE_FORMAT
• CURDATE()

Client Interface and insertId Emulation

The adapter exposes three methods:

• query<T>(sql, params?) — returns an array of typed rows.
• get<T>(sql, params?) — returns the first row or null.
• run(sql, params?) — returns { affectedRows, insertId }, thereby emulating MySQL behaviour.

insertId emulation works by automatically injecting a RETURNING id_<entity> clause into INSERT queries that do not already include one. This convention relies on the primary key naming rule of the catalogue schema.

Two categories of tables are excluded from this automatic injection, as they do not have a simple primary key:

• Tables with a composite primary key (customer groups, category-product associations, carrier zones, accessories, category cross-references, etc.) — listed explicitly in the adapter configuration.
• Any table whose suffix indicates a language or shop variant (_lang, _shop), detected by regular expression.

Drizzle ORM — schema-as-code for DDL migrations

The two paths described previously — the PostgreSQL connector on the web interface side and the Python connector on the tooling side — perform DML: they read and write rows against a table structure assumed to already be in place. Neither creates nor alters tables. The structure (DDL — CREATE TABLE, columns, types, indexes, constraints) is governed by a third path: Drizzle ORM, used in schema-as-code mode. TypeScript schemas are the declared source of truth for table structure; the CREATE/ALTER SQL is generated (or written) and then applied to the database, never the reverse.

Drizzle migrated from a MariaDB dialect to PostgreSQL during a platform modernisation effort. The active dependencies are drizzle-orm and drizzle-kit at their current versions.

6.1 Drizzle configuration file

The Drizzle configuration file (at the repository root) defines the following parameters:

Key	Value	Note
dialect	'postgresql'	Replaces the former MariaDB dialect
schema	3 TypeScript globs	Core scope, cockpit modules, enterprise packs
out	Folder for generated SQL migrations	Within the repository's core scope
schemaFilter	Dedicated application schema	Restricts introspection and diff to that schema only (ignores public and native schemas)
dbCredentials	Host, port, user, DB password, database name	All values carried by environment variables, never in plaintext
strict / verbose	true / true	Confirmation before push, verbose output

⚠️ The port configured by default in drizzle-kit is the TCP exposure port of the database, distinct from the direct container access port. drizzle-kit is designed to target a database exposed over TCP and does not go through direct container access.

6.2 drizzle-kit commands

No dedicated npm scripts are exposed in the project configuration: commands are run via npx.

Command	Effect
npx drizzle-kit generate	Compares TS schemas against the known state and writes a new SQL migration to the out/ folder
npx drizzle-kit migrate	Applies pending migrations to the target database defined in dbCredentials
npx drizzle-kit introspect	Generates TypeScript files from an existing database (useful for adopting a legacy table not yet declared)

6.3 TypeScript schemas

Each schema file declares one or more tables via Drizzle's pgSchema(...).table(...) API. Declarations are distributed across three scopes corresponding to the three globs in the configuration:

Scope	Contents	Audited by the drift detector
Open-source core	E-commerce platform tables (approximately 85 files, including bridge re-exports)	✅ Yes
Mothership cockpit modules	Agent, project, task, invoicing, drill… tables (approximately 47 files)	❌ No
Enterprise packs	Business extensions (approximately 14 files)	❌ No

Pitfall: some schema files exist in the repository for illustration and type-reference purposes only, but do not match any of the three active globs in the Drizzle configuration. These files are not an active DDL input. Likewise, cockpit tables (scopes 2 and 3) do not trigger a blocking drift from the drift detector — which is why recently added columns may coexist in the database and in the code without being covered by the core-scope Drizzle audit.

What TypeScript schemas express: physical column name, PostgreSQL type, notNull, default/defaultNow, simple or composite primary key, unique, index. Business types are refined via $type<...>() (TypeScript constraint with no impact on the database). Simplified declaration example:

export const appSchema = pgSchema('<schema-name>')
export type Runtime      = 'ps' | 'nuxt'
export type ModuleStatus = 'active' | 'disabled' | 'deprecated'

export const moduleRegistryTable = appSchema.table('<table-name>', {
  id:       serial('id').primaryKey(),
  codename: varchar('codename', { length: 128 }).notNull().unique(),
  version:  varchar('version',  { length: 32 }).notNull(),
  runtime:  varchar('runtime',  { length: 4 }).$type<Runtime>().notNull().default('ps'),
  status:   varchar('status',   { length: 10 }).$type<ModuleStatus>().notNull().default('active'),
  manifestJson: text('manifest_json').$type<ManifestType | null>(),
  // … audit columns (schema hash, migration dates, timestamps)
}, (t) => ({
  idxRuntimeStatus: index('idx_runtime_status').on(t.runtime, t.status)
}))

export type Row       = typeof moduleRegistryTable.$inferSelect
export type RowInsert = typeof moduleRegistryTable.$inferInsert

Key takeaways:

• The former MariaDB ENUM types have been ported to varchar(N) + $type<Union>(): the constraint is enforced at the TypeScript level, not via a native PostgreSQL ENUM type.
• Tables with composite primary keys (a frequent pattern on translation tables) use primaryKey({ columns: [t.idFoo, t.idLang] }).
• The exported $inferSelect/$inferInsert types are consumable on the application code side. The PostgreSQL connector of the web runtime goes through its own adapter (not through the Drizzle query builder) — here Drizzle serves only for DDL and static typing.

6.4 Generated migrations

The core migrations folder currently contains nine numbered SQL migrations, covering among other things: adding a phone column to the customer table (B2B/C registration), import mapping tables, stock alert tables, mega-menu context, and header link tables. A journal file meta/_journal.json is present.

Structural fact: the journal file meta/_journal.json contains an empty entry list. Automatic tracking via drizzle-kit migrate is therefore not the operative path. The SQL files in the out/ folder are in practice written idempotently (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS) and applied manually per client. In practice: the Drizzle TypeScript schema is the declarative source of truth; actual application remains a manual psql operation per client, never auto-propagated.

Second migration path — cockpit tables: the mothership cockpit tables, which live in modules and are not audited by the drift detector, use a separate manual SQL migration system spread across two folders:

• Cockpit-exclusive migrations (synedre.com, Odyssée, documentation, AI routing…): files named <YYYYMMDD>_<subject>.sql, all idempotent. Recent examples: adding visual intent columns to the task table, creating the AI routing table with an initial seed, external review tables, and a public documentation mirror table.
• Migrations applied directly to the local database: cockpit-scope tables outside the main path. This folder also hosts per-client subdirectories and two archiving folders (applied/ and _applied/) for already-executed migrations.

Application is manual in both cases — these migrations do not go through the automated deployment pipeline or through drizzle-kit migrate.

6.5 The DDL drift detector — schema ↔ live database anti-drift safeguard

Manual migration application across multiple clients creates a risk: an ALTER applied to the central database but missed on a client's database would silently reach production. Past incidents (propagation of ALTER statements, missing columns in production) motivated the creation of a dedicated tool: the DDL drift detector.

How it works:

• A regex parser reads the TypeScript schema files in the core scope and extracts the {table → {columns}} map.
• A database interrogator queries information_schema.columns for each target client, filtered on the application schema and the relevant table prefixes.
• The per-client diff distinguishes two levels: blocking = table or column declared in TypeScript but absent from the database (missing migration); informational = present in the database but not in TypeScript.
• A false-positive configuration file allows certain tables to be excluded depending on context: cockpit-only tables ignored for storefront clients, storefront tables excluded from the central database since scope separation.

Exit codes: 0 no blocking drift; 1 blocking drift (at least one client); 2 execution error.

Command-line usage:

# Audit of the central database only (default behaviour, backwards-compatible)
python3 <drift-detector>

# Audit of a specific client
python3 <drift-detector> --tenant <ID>

# Audit of all clients
python3 <drift-detector> --all

# Verbose mode
python3 <drift-detector> --all --verbose

Integration as a deployment gate (verified fact): the detector is wired as a blocking step in the deployment pipeline. It runs before any build or artifact push. If drift is detected, the deployment stops immediately — preventing code that expects columns absent from the client database from being pushed. The audit is a pre-deployment check; no dedicated periodic cron exists.

Full deployment pipeline (step order):

1. DDL drift check (blocking)
2. Automatic DDL drift application (if enabled)
3. Background hooks
4. Database-driven i18n route generation
5. Web interface build
6. i18n seed
7. Source maps
8. Deployment archive creation
9. Background git push
10. Archive upload
11. Remote reload (graceful reload of the process manager or container)
12. Client cron installation on the VPS (optional, if declared in the client configuration)
13. Background hook wait
14. Health check
15. Background SSR cache warm-up (non-blocking, launched after the health check; can be disabled via environment variable)
16. End banner display

6.6 The automatic DDL drift applicator

Complement to the detector (§6.5): this tool corrects drift rather than merely reporting it.

Core principles:

• No DROP ever (neither table nor column). Only missing items are added.
• Idempotent: replayable with no effect if the database is already up to date.
• Single transaction BEGIN … COMMIT with stop-on-error: full rollback if any statement fails.
• Generates only ADD COLUMN IF NOT EXISTS and CREATE TABLE IF NOT EXISTS, with types, NOT NULL/DEFAULT, and CHECK constraints derived from TypeScript schemas.

The tool reuses the drift detector's definitions (target client list, schema parser, database interrogator, false-positive configuration) to guarantee a single source of truth.

Internal pipeline:

1. Enhanced TS schema parser — builds typed structures (PG types, nullability, default value, CHECK constraints). Notable fix: a Drizzle index declared across multiple lines with a trailing comma produced an empty last token, which generated a CREATE UNIQUE INDEX (..., "") rejected by PostgreSQL with a zero-length identifier error, causing an automatic rollback and deployment cancellation. The fix filters empty tokens after splitting.
2. Drift computation — TypeScript ↔ live database diff: missing tables → CREATE TABLE; missing columns → ADD COLUMN.
3. SQL generation — idempotent SQL block ready for application.
4. Application — executed in a transaction against the target client, only when the --apply flag is passed.

Usage modes:

Command	Effect
python3 <drift-applicator> --tenant <ID> --dry-run	Displays the SQL without executing it (default mode)
python3 <drift-applicator> --tenant <ID> --apply	Applies within a transaction
python3 <drift-applicator> --all --dry-run	Inspects all clients

Exit codes: 0 no drift (or successfully applied); 1 drift detected in --dry-run mode or error during application; 2 parsing or connection error.

The applicator is called by the deployment pipeline's automatic application function with the --apply flag. This function is active in the unified deployment path that all clients have followed since the consolidation of per-client deployment scripts (now removed) — the historical gap is therefore closed.

Default behaviour of automatic application: enabled (DRIFT_AUTO_APPLY=1) since a June 2026 fix (the former default was 0). The actual opt-in is the presence of a drift: key in the client's YAML configuration; if it is absent, the application block is skipped and the applicator is never called.

6.7 Authority over structural evolution

TypeScript schemas (core scope + modules)
   │  (1) Edit the TypeScript file
   ▼
Idempotent SQL migration (out/ folder or manual migration folders)
   │  (2a) Manual psql application on each affected client
   │  (2b) Automatic drift applicator via the deployment pipeline
   ▼
Live PostgreSQL database (central database + clients)
   ▲
   └─ (3) Drift detector verifies TypeScript == live,
          blocks deployment if a blocking discrepancy is detected

Recovery rule: to evolve a table, first edit the TypeScript schema, generate or write the idempotent migration, apply it per client (manual or automatic path), then re-run the audit — the deployment pipeline replays it regardless. The live database must never be modified without reflecting the change in the TypeScript schema: the audit would flag it as blocking at the next deployment.

Conversely, the Python entity classes on the tooling side never create a column: their field list is a whitelist of columns assumed to already be present in the database, not a structure declaration.

Agentic Access (Python) — the Entity Pattern

The Python orchestration code relies on a base class from which around forty specialized classes inherit, each representing a business entity in the cockpit: project, work, task, agent, doctrine, billing, etc.

Transport to the Database

Unlike the front-end runtime, which uses a TCP connection pool, the Python layer accesses the database via system calls to the containerized PostgreSQL engine. Three distinct helpers cover the use cases:

Helper	Usage	Note
Write via temporary file	All write operations	The SQL is written to a uniquely named temporary file (pid + uuid to prevent multi-process collisions), copied into the container, then executed with immediate halt on error — no injection via stdin.
Inline tab-separated read	Simple reads	SQL passed directly to the engine; avoid on TEXT columns containing newlines.
Inline CSV read	Reads with potential newlines	CSV mode (RFC 4180); NULL values are normalized to empty string.

All three helpers automatically prefix every query with the working schema declaration. Connection parameters (target container, database name, user, DB password, schema) are read from environment variables at startup.

The Entity Base Class

Generic CRUD is parameterized by three class attributes: the name of the target table, the primary key, and the whitelist of columns accepted for insertion or update.

• Creation: business validation, forced canonical client identifier if the column is whitelisted, whitelist filtering, then INSERT … RETURNING <pk> with automatic timestamping.
• Update: same filtering, automatic date_upd update.
• Read / existence / deletion: find, find_one, exists, delete methods.
• Escaping: an internal helper normalizes booleans to SQL TRUE/FALSE and serializes dicts and lists to JSON.

Subclasses override validate(data, mode) for their specific business rules: blocking errors raise a ValidationError; non-blocking warnings are collected and returned in the response.

Atomic Project Creation with Skeleton

The project creation method prevents orphaned projects by creating in a single transaction the project, its first work item, and at least one task. Simplified signature:

create_with_skeleton(
    codename, title, *,
    first_travail: dict,
    first_taches: list[dict] | None,
    first_tache: dict | None,
    client_id=None, priority="P2", scope=None,
    current_focus=None, notes=None,
) -> {id_chantier, id_travail, id_tache, id_taches, codenames, warnings}

The following fields are transparently propagated to each created task: priority, description, estimated_tokens, estimated_h, position, recommended_model, scope, visual_intent, visual_url.

Blocking validations (raise ValidationError):

• The codename must conform to kebab-case format (4 to 64 characters) and be unique.
• The priority must belong to {P0, P1, P2, P3}.
• Each task must have a non-empty title and assignee_codename, and the assigned agent must exist in the agent registry.
• Multi-agent recruitment: if scope starts with tenant, at least two distinct agents must be assigned — a mechanism derived from the cross-supervision doctrine.

Non-blocking warning: estimated_tokens missing on a task.

The transaction chains the insertions (project, then work item retrieved by codename, then each task retrieved by id_travail) with immediate halt on error, guaranteeing full rollback. The recommended AI model per task is auto-computed if absent.

Task Entity — Estimation, Model, Skills, and Tools

• Creation: if estimated_tokens is absent, an automatic estimator is invoked; the scope field is validated against the list of allowed values; a non-blocking warning is emitted if the assigned agent does not belong to the project's production team.
• Model recommendation: three-level heuristic — most powerful model if priority is P0, if the agent has ≥ 2 failed iterations, or if the task exceeds 8,000 estimated tokens; intermediate model above 1,500 tokens; lightweight model otherwise.
• Skill attachment: skill lookup by name (natural key), insertion into the junction table with ON CONFLICT … DO NOTHING. If the skill is unknown, a proposal is recorded with status pending and the method returns False.
• Tool attachment: same mechanism by slug, with a proposal recorded for unknown tools.

Status Cascades

Status updates on tasks and work items automatically trigger cascades to higher levels:

Task → done/cancelled (all tasks in the work item terminal, ≥1 done)
   └─ Cascade task → work item
        ├─ QA team recruited? → applies the validation verdict
        └─ Otherwise → work item = 'done'

Work item → done/cancelled (all work items in the project terminal, ≥1 done)
   └─ Cascade work item → project
        ├─ Discovery-only guardrail: all done in 'discovery' phase
        │    → auto-expansion by LLM (creates implementation work items)
        ├─ Otherwise → project = 'test' (pre-prod, awaiting review)
        └─ Non-blocking warning if pre-prod test plan or delivery command are missing

A complementary resolution mechanism allows a corrective work item linked to a parent work item (via a reference field) to automatically close that parent when it moves to done: unstarted tasks in the parent are cancelled and the decision is recorded in the audit log.

Internationalization Conventions and Polymorphism

Translation Tables (_lang)

Any text visible to a visitor must reside in a sibling table suffixed _lang, never in the parent table.

• The suffix is exactly _lang (not _translation, _i18n, or _locale).
• The primary key is composite (id_<entity>, id_lang), with no auto-increment. In a multi-shop context, the key is extended to (id_<entity>, id_lang, id_shop) — no separate _shop_lang table.
• Strict separation: the parent table holds foreign keys, flags, dates, and enumerations; the _lang table holds text fields (title, description, meta_*) and all visible content.

This convention has a direct impact on the database adapter: _lang tables are excluded from the generated-identifier retrieval heuristic, as they do not have a simple primary key. On the front-end side, strings go through a translation function that reads the translation table; hard-coded strings are forbidden.

Polymorphism via parent_type / parent_id

When a feature applies to several types of parent entities, a single polymorphic table is used — never one table per parent type:

-- Example: polymorphic FAQ table
id_faq       integer  -- primary key
parent_type  varchar  -- 'cms' | 'category' | 'product'
parent_id    integer
position / active / date_add / date_upd
-- + _lang table (id_faq, id_lang, question, answer)

Exception: a 1:1 extension of a native e-commerce platform entity follows the <prefix>_<entity>_extra pattern, whose primary key is also a foreign key to the origin entity — no polymorphism in this case. Pure N-N junction tables follow an alphabetical naming convention with no _asso or _link suffix, and carry no _lang table.

No Business JSON in Columns

Business-content JSON columns are forbidden (payload_json, content_i18n, labels_json…). The only tolerance applies to ephemeral technical payloads (webhooks, logs, session state), documented by a column comment. In the cockpit, the JSON fields of work items and tasks are used exclusively for technical audit-trail in append-only mode — this is the tolerance, not the general rule. A schema verification tool (daily cron at 04:15 UTC) flags violations at P0 priority.

Resumption Pitfalls — Summary

1. The agent view is not the physical table. Reads go through the publicly exposed view; writes must target the underlying physical table. This principle applies to a dozen similar view/table pairs in the schema (agents, activity, heartbeat, relations, XP, XP history, automata, automaton-agent links, conducts, automaton logs, smart-automation logs, smart-automation rules).
2. Container ≠ database ≠ schema. The PostgreSQL container, the database, and the working schema are three distinct identifiers — confusing them produces silent connection errors.
3. The front-end adapter rejects any non-migrated tenant. The MySQL compatibility path has been removed; a tenant must be declared in the PostgreSQL activation list to be served.
4. The scope enumeration in the database is authoritative. The PostgreSQL CHECK constraint is broader than the written doctrine; when in doubt, query the constraint directly.
5. Renaming residue on task sequences. The task table has retained sequences and constraint names from its former name — do not rely on them to infer conventions.
6. Legacy MySQL SQL must be converted manually. The automatic converter does not cover all MySQL functions (GROUP_CONCAT, DATE_FORMAT, ON DUPLICATE KEY UPDATE…). Uncovered functions fail silently and require a hand-written replacement branch.