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→PG SQL on the web interface side, Python entity classes on the agentic tooling side, a schema-as-code system for DDL and migrations, and the naming conventions that hold everything together. It is intended for engineers taking over the codebase.

1. The Central Database

All mothership data resides in a single PostgreSQL schema, hosted in a dedicated database, inside an isolated Docker container.

Recurring pitfall: the Docker container and the database carry distinct names. The harness tables live in the mothership's private schema, not in the public schema.

The connection requires several environment parameters: the host, the database name, the target schema, the database user, and the database password. The password is never stored in plaintext — it is carried by the repository's environment files, and an exception is raised if it is absent.

Harness / multi-tenant boundary

This chapter documents exclusively the Synedre OS harness schema (single-database, private agentic cockpit). CodeMyShop tenant databases use the same adapter (see §4–5), but their data never crosses the central schema: each tenant has its own database, its own schema, its own tables. Any reference to tenants in the following sections is contextual, not constitutive of the harness.

Three access paths

Two paths coexist at runtime (DML: reading/writing rows) and one at design time (DDL: evolving the structure):

                   ┌─────────────────────────────────────────┐
                   │  PostgreSQL — central database           │
                   │  mothership private schema               │
                   └───────────────┬─────────────────────────┘
                                   │
   ┌──────────────────┬────────────┴────────────┬────────────────────────┐
   │  DML runtime      │  DML tooling            │  DDL / structure        │
   │ (A) Web interface │ (B) Agentic Python      │ (C) Schema-as-code      │
   │  PG adapter       │  <entity>.py classes    │  <migration-tool>       │
   │  TCP pool         │  isolated SQL execution │  generate + migrations  │
   └───────────────────┴─────────────────────────┴────────────────────────┘

• Path A (web interface): a PostgreSQL adapter uses a TCP connection pool for all interface reads and writes.
• Path B (Python tooling): Python entity classes access the database via an isolated SQL execution mechanism inside the container. Writes go through a temporary SQL file; reads use inline queries. No path uses a stdin pipe.
• Path C (schema-as-code): DDL is defined in versioned code and is the authoritative source for structural table evolution. It does not manipulate data rows — only structure. Detailed in §6.

Table families — snapshot

The state of the database at the time this chapter was written is divided into four families:

Family	Number of tables (approx.)	Scope
CodeMyShop / mothership private legacy tables	~159	Historical debt to be migrated
Synedre OS cockpit tables	~69	Agents, worksites, runs, negotiation…
Native PrestaShop tables	~18	Historical debt: products, categories, translations, languages…
Public OSS PaaS tables	~1 (vestige)	The SEO queue table is empty in the central database; its actual writers live in tenant databases. A former error table has been reclassified within the cockpit scope.

In addition to these tables, 16 views exist in the central schema (see §3.4). Critical point: the view listing agents is built on the Synedre OS cockpit agent table — it is not an independent table.

Note: these counts change with every worksite. The up-to-date verification query is provided in §7.

2. Table Families (prefixes)

A table's prefix encodes its scope and ownership regime. Four families coexist:

Family	Scope	Status	Example roles
Public OSS PaaS tables	Core product + community	Target	FAQ, homepage blocks (in tenant databases)
Synedre OS cockpit tables	Internal agentic cockpit	Target	Worksite tasks, agents, runs, negotiation
CodeMyShop / mothership private legacy tables	Private legacy	Debt to be migrated	Worksites, scars, client VPS inventory
Native PrestaShop tables	PrestaShop heritage	Historical debt	Products, categories, translations, languages

Cross-cutting conventions

• Singular name for all tables — aligned with the native PrestaShop convention.
• One table = one parent entity; no catch-all tables.
• Source files in kebab-case; Vue components in PascalCase.

Prefix migration history

The public OSS PaaS prefix results from a rename carried out during the move to version 0.2.0, from the private legacy prefix (at the time when CodeMyShop was a PrestaShop extension). In the mothership schema, most tables remained under the legacy prefix — the debt has not yet been cleared — while the public OSS prefix is applied in tenant databases.

2.1 Family cohabitation within a single logical entity

A point that surprises engineers taking over the code: a single logical worksite entity is spread across two table families, legacy and cockpit.

Table role	Family	Description
Main worksite table	Private legacy	1 row per worksite
Granular work items table	Private legacy	N rows per worksite
Atomic tasks table	Synedre OS cockpit	N rows per work item
Satellite tables (team, multi-session lock, QA…)	Synedre OS cockpit	Ancillary cockpit objects

Detail revealing the rename history: the atomic tasks table was renamed during the prefix migration, 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 — a point of attention during any introspection or DDL generation.

Data Model — Main Tables

Worksites Table

The central worksites table uses a serial identifier as its primary key. Its notable columns are as follows:

• codename — unique kebab-case identifier for the worksite (varchar 64, required).
• title — human-readable label (varchar 255, required).
• client_id — optional reference to a tenant; NULL indicates an internal worksite.
• status — worksite lifecycle, default value 'planning'.
• priority — priority level, default value 'P2'.
• current_focus, deadline, notes, mission_letter, preprod_test_plan — free-text steering fields.
• external_contacts — external contacts linked to the worksite.
• ship_command — associated delivery command.
• scope — worksite perimeter; the database constraint accepts the following values: synedre, codemyshop-oss, codemyshop-enterprise, tenant, business, juridique, negociation, conseil (or NULL). This set is broader than the one described in certain internal reference documents, which do not list the legal and commercial values.
• auto_explode — boolean, enables automatic decomposition into tasks (default true).
• mode_auto — boolean, enables autonomous steering (default false).
• max_cost_eur — budget ceiling in euros.
• archived_at / archived_by — archiving timestamp and author.
• date_add / date_upd — creation and update timestamps (default now()).

A trigger fires after each status update: when a worksite transitions to the resolved state, it automatically propagates that change to the processing queue items attached to it.

Tasks Table

Each task is attached to a worksite via a logical foreign key. The main columns are:

• title, status (default 'todo'), priority (default 'P2').
• assignee_codename — codename of the assigned agent or automation (varchar 64).
• estimated_tokens / actual_tokens / actual_cost_usd — AI consumption tracking.
• recommended_model — recommended AI model for executing the task.
• position — order within the list.
• scope — task perimeter; possible values: synedre-internal, codemyshop-oss, codemyshop-enterprise, tenant-single, tenant-multi, infra, doctrine.
• visual_intent — description of what should be visible on screen after the change; NULL indicates a non-visual task. This field feeds the automated visual verification engine.
• visual_url — rendering verification URL; NULL falls back to the worksite's staging environment.

These two visual columns were added idempotently (ADD COLUMN IF NOT EXISTS). They are recognized by the Python-side persistence layer, but not yet declared in the corresponding TypeScript schema — an intentional divergence, as the migration is managed manually outside the scope of the schema/ORM drift detection tool.

An inter-task dependency graph is maintained in a dedicated table, making it possible to express that task B cannot start until task A has been completed.

Scar Log

The error and lessons-learned journal (the scars) records each incident or victory encountered by agents. Its main columns are:

• agent_codename — agent concerned (required).
• error_type, description, root_cause, corrected_by — incident anatomy.
• severity — severity level: low, medium, high, critical.
• kind — entry type: 'failure' by default, or 'victory' recorded via the dedicated skill.
• resolved — resolution state (0, 1, or 2).
• tags — array of free-form tags.
• importance — score from 1 to 10.
• recall_count — number of times the scar has been recalled into context.
• learnable — boolean indicating whether the lesson can be extracted to train future behaviors.

Agent Registry: Physical Table and Compatibility Views

Important notice for any handover. The physical agents table is distinct from the view of the same name exposed to legacy application layers. This view is a backward-compatibility shim: it transparently redirects reads to the underlying physical table, in a manner entirely invisible to querying code.

The physical table contains the columns codename, nickname, role, group_name, active, task configuration fields (job_*), a cognitive frame (cognitive_frame), and a heritage field (heritage).

This view-shim mechanism is generalized across about a dozen pairs: each legacy application reference points to its actual physical table without any modification to the calling code. In total, the database contains 16 views, including:

• 12 backward-compatibility shim views covering agents, automations, activity, heartbeat, relations, experience, behaviors, logs, and smart-automation.
• One Atlas spawn event tracking view.
• Three operational analytical views:
  • Scar aggregation view — aggregates the last 14 days by tenant, day, severity, and type (failure/victory), with a count per group.
  • Rollback tracking view — over the last 14 days, per tenant: total number of runs, number of rollbacks, rollback rate as a percentage, number of blocking verdicts, date of last run.
  • Warning frequency view — denormalizes over 30 days the array of warning codes from review runs, with their frequency, the number of associated blocking verdicts, and the date of last occurrence.

Unified error view. A unified error view (union of front-end errors and server errors) is declared in the enterprise error-tracking module manifest and queried by the API. However, it does not exist in the mothership environment because the front-end error table is not instantiated there — only the server error table is present. The corresponding migration was therefore unable to recreate it in this context. The actual view count in this environment is indeed 16, not 17. This view does exist in dedicated production environments where both data sources are available. Because a PostgreSQL view references a table by OID (rather than by name), it automatically followed any renames without manual intervention.

Writing via shim views. These views are most likely read-only. Any write operation (INSERT, UPDATE) must target the underlying physical table, not the view. This must be verified systematically before any modification to the agent registry.

Database Access at Runtime — Multi-Tenant Architecture

Tenant Resolution and Adapter Selection

The database routing system dynamically resolves the tenant on each incoming request. The mechanism operates in two steps:

1. Tenant resolution — three strategies are attempted in order:
   1. The Nuxt process runtime configuration (runtimeConfig.clientId) — each tenant VPS defines this value statically in its environment.
   2. Matching the request hostname against an internal configuration table — this path allows multiple tenants to be served from a single Nuxt deployment.
   3. Fallback value: the identifier of the mothership itself.
2. Adapter selection — once the tenant has been identified, the system checks whether it is authorized to access PostgreSQL (see the global gate below). If so, a PostgreSQL adapter is instantiated for that tenant. Otherwise, an exception is raised: the MySQL/MariaDB access path was permanently removed during a prior migration phase.

Global PostgreSQL Activation Gate

Critical point for any operator. A global environment flag controls PostgreSQL access for all tenants, including the mothership's internal tenants. If the corresponding environment variable does not contain the wildcard character *, no tenant — not even an internal tenant — can access PostgreSQL. The system raises an exception for all DB requests.

This mechanism is a legacy of the progressive activation phase (domain-by-domain opt-in), during which the variable explicitly listed the enabled modules. The global switch was made in production (value *). An operator who clears this variable triggers a complete PostgreSQL access outage with no further warning.

Per-Tenant Connection Configuration

The connection configuration table is built dynamically at startup from environment variables following the convention NUXT_TENANT_DB_<IDENTIFIER>, where the tenant identifier is transcribed in uppercase with hyphens replaced by underscores. The value of each variable follows the format:

<database>[,<host>[,<port>[,<user>[,<password>

A utility function exposes the list of known tenant identifiers, used in particular to propagate global secrets to each tenant database during maintenance operations.

The convention "one tenant = one canonical codename" is applied consistently across all system surfaces (environment configuration, hostname resolution, application routing, etc.). The tenant resolution function described above is one of these surfaces and must always return this canonical codename.

The Database Adapter: SQL Compatibility Layer

This component exposes a unified query / get / run interface — identical to the former MySQL layer — while dispatching internally to the PostgreSQL driver, converting legacy MySQL SQL on the fly. It is the central piece enabling persistence engine migration without rewriting callers.

Connection Pool

On first call, a singleton pool is instantiated with the following parameters:

• Host, port and target database read from dedicated environment variables; the database password is mandatory — the process throws immediately if it is absent.
• Maximum pool size: 20 simultaneous connections.
• Idle timeout: 60 s; maximum connection lifetime: 1 800 s; connection timeout: 15 s.
• All tables are automatically schema-qualified for the target PostgreSQL schema (see below).

MySQL → PostgreSQL Translation Pipeline

Every SQL query passes through a rewrite pipeline applied in the following order:

#	Transformation	Detail
1	Backticks → double quotes	`col` becomes "col" (PG identifier syntax).
2	Automatic schema qualification	After FROM, JOIN, INTO, UPDATE, TABLE: table prefixes from the e-commerce catalogue and hub are prefixed with the target PostgreSQL schema name.
2b/2c	DATE_SUB / DATE_ADD + INTERVAL N UNIT	Rewritten as PostgreSQL date arithmetic; supports DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, both literals and placeholders.
2d	TIMESTAMPDIFF(unit, a, b)	Converted to 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 …	Rewritten as INSERT … ON CONFLICT DO NOTHING (unless an ON CONFLICT clause is already present).
—	Auto-quoting of aliases AS fooBar	Becomes AS "fooBar" to preserve case (PostgreSQL lowercases unquoted identifiers). Exception: native PostgreSQL types (TEXT, INTEGER, etc.) are left as-is to avoid breaking CAST(x AS TEXT) expressions.
3	Placeholders ? → $1, $2, …	Positional conversion via a character-by-character parser that ignores ? inside quoted strings.

Important — rewrite vs. binding separation: placeholder transformation only renames markers in the SQL text (? → $N). Actual value binding is delegated to the PostgreSQL driver, which receives the parameter array unchanged through its parameterised API. No value is interpolated into the SQL string: this translation layer therefore introduces no SQL injection risk.

SQL Functions Not Handled Automatically

The following constructs are not automatically rewritten and must be ported manually on the caller side, via a dedicated PostgreSQL branch in the relevant endpoint:

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

Unified Interface and Insert ID Emulation

The adapter exposes three methods:

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

The run() method emulates MySQL's insertId: for a simple INSERT without a RETURNING or ON CONFLICT clause, it automatically appends RETURNING id_<entity> (catalogue primary key naming convention: the column follows the table name stripped of its prefix).

This mechanism is disabled for tables with a composite primary key, which do not have a unique id_<table> column. Excluded tables include several catalogue relation tables (customer groups, category–product links, carrier zones, accessories, category cross-references), as well as any table whose suffix indicates a per-language or per-shop variant.

Database Adapter: SQL Compatibility Layer

This component exposes a unified query / get / run interface — identical to the former MySQL layer — while dispatching internally to the PostgreSQL driver, converting legacy MySQL SQL on the fly. It is the central piece enabling engine migration without rewriting callers.

Connection Pool

On first call, a singleton pool is instantiated with the following parameters:

• Host, port and target database read from dedicated environment variables; the database password is mandatory — the process throws immediately if it is absent.
• Maximum pool size: 20 simultaneous connections.
• Idle timeout: 60 s; maximum connection lifetime: 1 800 s; connection timeout: 15 s.
• All tables are automatically schema-qualified for the target PostgreSQL schema (see below).

MySQL → PostgreSQL Translation Pipeline

Every SQL query passes through a rewrite pipeline applied in the following order:

#	Transformation	Detail
1	Backticks → double quotes	`col` becomes "col" (PG identifier syntax).
2	Automatic schema qualification	After FROM, JOIN, INTO, UPDATE, TABLE: table prefixes from the e-commerce catalogue and hub are prefixed with the target PostgreSQL schema name.
2b/2c	DATE_SUB / DATE_ADD + INTERVAL N UNIT	Rewritten as PostgreSQL date arithmetic; supports DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, both literals and placeholders.
2d	TIMESTAMPDIFF(unit, a, b)	Converted to 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 …	Rewritten as INSERT … ON CONFLICT DO NOTHING (unless an ON CONFLICT clause is already present).
—	Auto-quoting of aliases AS fooBar	Becomes AS "fooBar" to preserve case (PostgreSQL lowercases unquoted identifiers). Exception: native PostgreSQL types (TEXT, INTEGER, etc.) are left as-is to avoid breaking CAST(x AS TEXT) expressions.
3	Placeholders ? → $1, $2, …	Positional conversion via a character-by-character parser that ignores ? inside quoted strings.

Important — rewrite vs. binding separation: placeholder transformation only renames markers in the SQL text (? → $N). Actual value binding is delegated to the PostgreSQL driver, which receives the parameter array unchanged through its parameterised API. No value is interpolated into the SQL string: this translation layer therefore introduces no SQL injection risk.

SQL Functions Not Handled Automatically

The following constructs are not automatically rewritten and must be ported manually on the caller side, via a dedicated PostgreSQL branch in the relevant endpoint:

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

Unified Interface and Insert ID Emulation

The adapter exposes three methods:

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

The run() method emulates MySQL's insertId: for a simple INSERT without a RETURNING or ON CONFLICT clause, it automatically appends RETURNING id_<entity> (catalogue primary key naming convention: the column follows the table name stripped of its prefix).

This mechanism is disabled for tables with a composite primary key, which do not have a unique id_<table> column. Excluded tables include several catalogue relation tables (customer groups, category–product links, carrier zones, accessories, category cross-references), as well as any table whose suffix indicates a per-language or per-shop variant.

Drizzle ORM — Schema as DDL Source of Truth

The two data access paths described previously (the Nuxt interface side and the Python tooling side) operate exclusively in DML: they read and write rows into a table structure assumed to already be in place. Neither one creates nor alters tables. The structure — table creation, columns, types, indexes, constraints — is governed by a third path: Drizzle ORM, used in schema-as-code mode.

TypeScript schemas are the declarative source of truth for table structure; CREATE/ALTER SQL is generated or written by hand, then applied to the database — never the other way around.

The dialect was MariaDB prior to a structural migration carried out in April 2026; it is now postgresql. The dependencies are drizzle-orm and drizzle-kit at their current minor versions.

Central Configuration

The configuration file at the root of the repository defines the following parameters:

Key	Value	Note
dialect	'postgresql'	Replaces the former MariaDB dialect
schema	3 globs covering the OSS core, cockpit modules, and enterprise packs	Locations of TypeScript declarations
out	Folder for generated SQL migrations (OSS core)	Files written or generated by drizzle-kit
schemaFilter	Restricts introspection to the application-specific schema	Ignores native PostgreSQL schemas
DB Credentials	Host, port, user, password, database name — carried by environment variables	Never stored in plain text in the repository
strict / verbose	true / true	Confirmation before push; detailed output

Port note: the Drizzle configuration targets by default a TCP exposure port different from the internal port used by the application adapters. Drizzle-kit is designed to connect to a database exposed over TCP from the host machine, not to run directly inside a container.

Available Commands

No dedicated npm scripts are exposed in the project manifest; commands are run directly via npx:

Command	Effect
npx drizzle-kit generate	Compares TypeScript schemas against the known state and writes a new SQL migration
npx drizzle-kit migrate	Applies pending migrations to the configured database
npx drizzle-kit introspect	Reverse-engineers an existing database to produce TypeScript schema files

TypeScript Schema Layout

Each schema file declares one or more tables using Drizzle primitives (pgSchema(...).table(...)). Declarations are distributed across three scopes corresponding to the three configuration globs:

Scope	Contents	Covered by drift audit
OSS Core	Application foundation tables (approximately 85 files, including re-exports to the runtime database)	✅ Yes
Cockpit Modules	Cockpit tables: agents, projects, scars, billing, exercises… (approximately 47 files)	❌ No
Enterprise Packs	Business extensions (approximately 14 files)	❌ No

Important note: a schema file describing the module registry exists in the repository but falls outside the three active globs. It serves as a typing reference and illustration, not as an effective DDL entry point. Likewise, cockpit tables declared in the modules (scope 2) follow a separate migration path (see below) and do not trigger a blocking drift alert — which explains why columns added recently (for example to store the visual intent of a task) can coexist in the database and in the business entities without appearing in the OSS core audit.

What TypeScript schemas express: physical column name, PostgreSQL type, NOT NULL constraint, default value, simple or composite primary key, uniqueness, indexes. Business types are refined via $type<...>() for TypeScript-level typing without a database-side constraint. Illustrative example of a module registry table:

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

export const moduleRegistry = appSchema.table('<module-registry-table>', {
  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'),
  manifest: text('manifest').$type<ModuleManifest | null>(),
  // … schema hash, last migration date, timestamps
}, (t) => ({
  idxRuntimeStatus: index('idx_runtime_status').on(t.runtime, t.status)
}))

export type Row    = typeof moduleRegistry.$inferSelect
export type Insert = typeof moduleRegistry.$inferInsert

Key maintenance points:

• MariaDB ENUM types have been ported to varchar(N) + $type<Union>(): the constraint is enforced at the TypeScript level, without a native PostgreSQL ENUM type.
• Tables with a composite primary key (e.g. translation tables) use primaryKey({ columns: [t.entityId, t.langId] }).
• The exported $inferSelect/$inferInsert types are consumable by application code; the Nuxt runtime nonetheless goes through the low-level adapter (§ above), not through the Drizzle query builder. Drizzle here serves only for DDL and static typing.

SQL Migrations and Their Application

OSS Core Migrations

The OSS core migrations folder currently contains nine migrations numbered sequentially, covering changes such as an import mapping, the addition of a phone field for B2B/C registration, stock alerts, mega-menu context management, header links, and a dated file for the mega-menu header context. A journal file (meta/_journal.json) tracks known migrations.

Structural fact: the drizzle-kit journal contains an empty list of entries. The automatic tracking of drizzle-kit migrate is therefore not the operative path. The SQL files are in practice written by hand in an idempotent manner (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS) and applied manually per client via a direct connection to each database. In practice: TypeScript schemas constitute the declarative source of truth; actual application remains a psql per client, never propagated automatically on its own.

Cockpit Migrations (Separate Path)

Cockpit tables — those declared in the modules (scope 2) and not audited by the drift detector — use a separate system of manual SQL migrations, distributed across two locations in the repository:

• Cockpit-exclusive migrations (synedre.com, Odyssée, documentation, AI routing…): named by date and subject, all idempotent. Recent examples: addition of visual intent columns on tasks, creation of the AI routing table with its initial seed, external documentation review table, public documentation mirror.
• Migrations applied directly to the local database: cockpit tables outside the previous scope (reflexes, reflex thresholds, automaton scope). This folder also hosts archival subdirectories (applied/ and _applied/) of migrations already executed on the primary target.

Application is in both cases manual via a direct connection to the target database. These migrations do not go through the automatic drift application function or through drizzle-kit migrate.

The DDL Drift Detector

Since migration application is manual and multi-client, an ALTER applied to the primary database but forgotten on a client's database would reach production silently. A dedicated audit tool closes this gap.

How It Works

The audit tool operates in three steps:

1. TypeScript schema parsing — reads files from the OSS core scope and extracts, via regular expression, the list of declared tables and columns (after stripping block comments).
2. Live structure read — queries information_schema.columns of each targeted client, filtered on the application schema and relevant table families, via a direct connection to each database.
3. Comparison and report — identifies discrepancies: blocking = table or column declared in TypeScript but absent from PostgreSQL (missing migration); informational = present in the database but absent from the TypeScript schema.

False positives are filtered by a versioned ignore file that distinguishes two categories: tables present only on the central cockpit (to be ignored on clients) and OSS store foundation tables removed from the cockpit database during a decoupling initiative (to be ignored on that target only).

Execution modes:

# Audit of the current client (backward-compatible)
python3 <drift-audit-tool>

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

# Audit of all clients
python3 <drift-audit-tool> --all --verbose

Exit codes: 0 no blocking drift, 1 blocking drift detected, 2 runtime or connection error.

Integration in the Deployment Pipeline

The audit is wired as a blocking step in the deployment pipeline: before pushing an artifact, the pipeline verifies that the target client's database structure matches the TypeScript schemas. If drift is detected, the deployment stops immediately. There is no dedicated periodic cron job — the audit is a pre-deployment check.

Automatic DDL Drift Application

A complementary tool corrects drift instead of merely reporting it. It shares with the audit tool the definition of target clients and the TypeScript schema parsing logic, in order to maintain a single source of truth.

Core Principles

• No DROP ever — neither tables nor columns. Only missing items are added.
• Idempotent — safely replayable with no effect if the database is already up to date.
• Single transaction — BEGIN … COMMIT with immediate halt 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 constraints, and checks derived from TypeScript schemas.

Internal Pipeline

1. Deep TypeScript schema parsing — produces typed structures describing each table and column (PostgreSQL type, nullability, default value, indexes). An important fix was applied following a production incident: a multi-column Drizzle index with a trailing comma produced an empty token after splitting, generating a zero-length identifier rejected by PostgreSQL, which caused an automatic rollback and aborted the deployment. The fix filters out empty tokens and discards any index whose column list is empty after cleanup.
2. Drift computation — difference between TypeScript state and live state: missing tables → CREATE TABLE; missing columns → ADD COLUMN.
3. Idempotent SQL generation — SQL block ready to be applied.
4. Transactional application — executed against the target client only in --apply mode.

Usage Modes

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

Exit codes: 0 no drift or successful application; 1 drift detected in dry-run or application error; 2 parsing or connection error.

Integration in the Unified Deployment Pipeline

Automatic application is enabled by default (DRIFT_AUTO_APPLY=1) since a June 2026 fix that reversed the previous default value. The effective opt-in remains the presence of a drift: key in the client configuration file (deploy.yaml): if absent, the application function is never called.

Legacy per-client deployment scripts were removed during a consolidation initiative (following an incident where silent drift had caused 500 errors across an entire storefront): all clients now go through the same unified deployment path, which carries both functions — blocking audit followed by automatic application.

Full deployment pipeline sequence (execution order):

1. DDL drift check (blocking)
2. Automatic DDL drift application
3. Background hooks
4. i18n route generation from the database
5. Nuxt build
6. i18n seed
7. Source maps
8. Artifact packaging
9. Background git push
10. Artifact upload
11. Remote reload (PM2 graceful or Docker)
12. Client cron installation on VPS (optional, if declared in configuration)
13. Background hook wait
14. Health check
15. SSR cache warm-up in background (non-blocking, after health check; can be disabled via environment variable)
16. Completion banner

Authoritative Source for Structural Changes

TypeScript schema (OSS core)   ← declarative SOURCE OF TRUTH
   │  (1) edit the TypeScript
   ▼
Idempotent SQL migration
   │  (2a) manual application to each impacted client
   │  (2b) automatic application via the deployment pipeline
   ▼
Live PostgreSQL (primary database + client databases)
   ▲
   └─ (3) drift audit verifies TypeScript == live,
          blocks deployment if a discrepancy is detected

Maintenance rule: to evolve the structure of a core foundation table, first edit the TypeScript schema, generate or write the idempotent migration, apply it to each affected database (manual or automatic path), then re-run the audit — the pipeline replays it on the next deployment regardless. Never modify the live database without reflecting the change in TypeScript: the audit would flag it and block the next deployment.

Conversely, business entities on the Python tooling side never create columns: their field list is a whitelist of columns assumed to already exist in the database, not a structure declaration.

Agentic Access from Python — the Entity Pattern

The Python layer of the cockpit relies on a hierarchy of around forty classes, all inheriting from a common base class. Each subclass represents a business concept — project, work order, task, agent, doctrine, billing — and exposes a generic CRUD that the subclass's business rules refine.

Transport to the Database

Unlike the Nuxt façade, which opens a TCP pool to the database, the Python layer communicates with the database via system calls to the PostgreSQL command-line interface, executed inside the database container. Three helpers handle this transport:

Helper	Usage	Note
Temp-file write	All SQL writes	The SQL is written to a uniquely named temporary file (PID + UUID) to avoid collisions between concurrent processes, copied into the container, then executed with immediate halt on error.
Tabular read	SELECT queries whose columns contain no newlines	SQL passed directly on the command line; output is tab-separated.
CSV read	SELECT queries on long-text columns	Same mechanism, CSV mode (RFC 4180); NULL values are normalized to empty strings.

All helpers prefix the SQL with a search-path directive pointing to the database's main schema. Connection parameters (target container, database name, user, password, schema) are read from environment variables at Python layer startup.

The Entity Base Class

The generic CRUD is parameterized by three class attributes:

• Target table: name of the PostgreSQL table in the main schema.
• Primary key: name of the PK column for that table.
• Column whitelist: only these columns are accepted in INSERT and UPDATE, preventing injection of any unintended column.

Behavior of the main methods:

• create(data): validates the data, forces the client_id field value if the column is in the whitelist, filters against the whitelist, then executes an INSERT … RETURNING <pk> with automatic timestamping of the date_add and date_upd columns.
• update(pk, data): filters against the whitelist, updates date_upd.
• find / find_one / exists / delete: read and delete helpers.

Internal SQL escaping handles Python booleans as SQL TRUE/FALSE, and serializes dictionaries and lists to JSON before insertion. Subclasses override the validate(data, mode) method to add their business rules: blocking violations raise a ValidationError; non-blocking warnings are collected and surfaced to the caller.

Atomic Project Creation with Skeleton

The atomic creation method of the project manager guarantees that no orphaned project can exist in the database: the project, the first work order, and at least one task are inserted in a single transaction.

ChantierEntity().create_with_skeleton(
    codename, title,
    first_travail={
        "codename": "<work-order-codename>",
        "title": "<work-order-title>",
        # priority, current_phase, ...
    },
    first_taches=[                        # doctrine v3 — recommended
        {
            "title": "<task-title>",
            "assignee_codename": "<agent-codename>",
            # priority, estimated_tokens, scope, ...
        }
    ],
    first_tache=None,                     # singular — backward compatibility
    client_id=None,
    priority="P2",
    scope=None,
    current_focus=None,
    notes=None,
)
# Returns: {id_chantier, id_travail, id_tache, id_taches, codenames, warnings}

The following fields are transparently propagated to every task in the skeleton: priority, description, estimated_tokens, estimated_h, position, recommended_model, scope, visual_intent, visual_url.

Blocking Validations

• The codename must conform to kebab-case (4 to 64 characters) and be unique in the database.
• The priority must belong to {P0, P1, P2, P3}.
• Each task must have a non-empty title and assignee codename; the designated agent must exist in the active agent registry.
• Multi-agent recruitment: when the scope field indicates a tenant-type perimeter, the method requires at least two distinct agents to be assigned. This rule prevents siloed work on multi-domain projects.

Non-Blocking Warnings

• The absence of an estimated_tokens field on a task generates a warning but does not block creation.

Transactional Flow

The method opens a single transaction (BEGIN … COMMIT). It first inserts the project, then the work order (retrieving the project identifier by codename), then each task (retrieving the work order identifier). The immediate-halt-on-error option guarantees a full rollback if any insertion fails. When the recommended_model field is absent from a task, it is computed automatically by the model recommendation heuristic.

Task Management — Estimation, Model, Skills, and Tools

Creation and Automatic Estimation

The create() override in the task manager automatically fills in the token estimate when it is absent, by calling a dedicated estimator. It also validates that the scope field value belongs to the set of allowed values, and emits a non-blocking warning if the assigned agent is not part of the project's production team.

Model Recommendation Heuristic

The recommend_model_for(tokens, priority, fail_recurrent) method automatically selects the AI model best suited to a task:

• Most powerful model if the priority is P0, if the task has accumulated at least two failed iterations, or if the estimated volume exceeds 8,000 tokens.
• Intermediate model if the volume exceeds 1,500 tokens.
• Lightweight model in all other cases.

Attaching Skills and Tools

Attaching a skill to a task performs a lookup in the skill registry by name (natural key), then inserts the link while silently ignoring duplicates. If the skill is unknown to the registry, it is recorded in a pending-validation proposals table, and the method returns False to signal the partial failure.

Attaching a tool follows the same pattern: lookup by slug in the tool registry, link insertion, and in the case of an unknown tool, creation of a pending proposal.

Automatic Status Cascades

Status updates on tasks and work orders trigger automatic cascades that propagate progress upward through the hierarchy:

Task → done / cancelled
  (all tasks in the work order are in a terminal state, with ≥1 done)
   └─ The cascade engine checks whether a QA team is recruited on the project
        ├─ YES: the QA verdict is applied before closing the work order
        └─ NO: the work order moves directly to the 'done' state

Work order → done / cancelled
  (all work orders in the project are in a terminal state, with ≥1 done)
   └─ The cascade engine evaluates the context
        ├─ All completed work orders are in the 'discovery' phase
        │     → auto-split: an LLM generates the implementation work orders
        │       instead of promoting the project
        ├─ Otherwise → project moves to the 'test' state (preprod, awaiting review)
        │               the cascade stops at this level
        └─ Non-blocking warning if the preprod test plan
              or the production deployment command are absent

A complementary mechanism handles resolution work orders: when a subsidiary work order marked as resolving a parent work order transitions to the done state, the pending parent work order is automatically closed, its unstarted tasks are cancelled, and the decision is recorded in the work order's audit log.

Internationalization Conventions and Polymorphism

Translation Tables (_lang)

Any text visible to an end visitor is stored in a sibling table suffixed _lang, never in the parent table. Naming rules are strict:

• The suffix is exactly _lang — variants such as _translation, _i18n, or _locale are forbidden.
• The primary key is composite: (id_<entity>, id_lang), with no auto-increment. For multi-shop contexts, it extends to (id_<entity>, id_lang, id_shop) — no separate _shop_lang table is created.
• Separation of concerns is strict: 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 adaptation layer: _lang tables are excluded from the returned primary-key identification heuristic, as they do not have a simple PK. On the interface side, strings flow through the translation function t('domain.key', 'fallback'), which reads the central translation table; any string hard-coded in source code is a P0 violation.

Polymorphism by Parent Type

When a feature applies to multiple parent entity types — for example a FAQ attachable to a CMS page, a category, or a product — a single polymorphic table is created, never one table per parent type:

-- Example: polymorphic FAQ table
id_faq       integer  -- primary key
parent_type  varchar  -- 'cms' | 'category' | 'product'
parent_id    integer  -- identifier of the parent entity
position     integer
active       boolean
date_add     datetime
date_upd     datetime
-- + associated _lang table: (id_faq, id_lang, question, answer)

Documented exception: a 1:1 extension of a native framework entity follows the <prefix>_<entity>_extra pattern, where the primary key is also the foreign key to the original entity. This pattern avoids polymorphism for strictly bijective relationships. Pure N-N join tables are named by combining the two entities in alphabetical order, with no _asso or _link suffix, and no associated _lang table.

Business JSON Columns Prohibited

Columns whose names end in _json and that would store business content (payload_json, content_i18n, labels_json…) are forbidden by naming conventions. The only tolerance covers ephemeral technical payloads — webhooks, logs, session state — explicitly documented by a column comment in the database.

In the cockpit, existing JSON columns on work-order and task tables serve exclusively for technical audit in append-only mode (context, decisions, findings): this is the permitted tolerance, not the general rule. A nightly scheduled automated audit detects and flags violations of this convention at P0 severity.

Common Pitfalls When Taking Over the Codebase

1. Shim views: about a dozen tables visible in the cockpit's public API are in fact views pointing to physical tables in a different schema. Reading from the view is correct; writes must go directly to the underlying physical table. Affected pairs include agents, agent activity, heartbeats, inter-agent relationships, experience points, state machines, and their logs.
2. Three distinct levels: the database container, the database itself, and the main schema are three independent objects with different names. Confusing these levels is a frequent source of errors.
3. Mandatory PG migration: the Nuxt façade raises an exception for any client whose database has not been migrated to PostgreSQL. The MySQL compatibility path has been removed. A client must appear in the list of enabled PostgreSQL clients for the adapter to function.
4. The scope constraint takes precedence: the enumeration of allowed values for the scope field is defined by a CHECK constraint in the database, which may be broader than the doctrine written in the documentation. In case of divergence, the database constraint applies.
5. Rename residues: the task table has retained sequence and constraint names from a prior rename — these identifiers do not reflect the table's current name.
6. MySQL→PG SQL conversion: legacy SQL written in MySQL dialect goes through an automatic converter. Functions not covered by this converter (GROUP_CONCAT, DATE_FORMAT, ON DUPLICATE KEY UPDATE…) fail silently in production and require manual rewriting in an explicit PostgreSQL branch.