The Data Layer

The Synedre OS Data Layer

This page describes how the Synedre OS agentic harness reads and writes its data: a central PostgreSQL database, an adapter that translates SQL queries on the web interface side, Python classes on the agentic tooling side, a schema-as-code system for 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 lives in a single PostgreSQL schema, hosted in a dedicated container on the mothership VPS. The database and the schema carry distinct names — a point that often surprises newcomers: the container, the database, and the schema each have a different identifier, and the harness tables live in the specific schema, not in the public schema.

Access credentials (DB user, password) are carried by environment files and are never written in plain text in the code; the application raises an explicit error if they are absent.

Harness / multi-tenant boundary. This chapter documents exclusively the central schema of the Synedre OS harness (private agentic cockpit, single-database). The CodeMyShop tenant databases use the same connection adapter (see §4–5) but their data never crosses the harness schema: each tenant has its own database, its own schema, 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 coexist at runtime (row-level read/write — DML); a third operates at the structural level (schema evolution — DDL):

                   ┌──────────────────────────────────────┐
                   │         PostgreSQL — central database │
                   │         Synedre OS harness schema     │
                   └───────────────┬──────────────────────┘
                                   │
   ┌──────────────────┬────────────┴──────────────┬─────────────────────┐
   │  DML — runtime   │  DML — tooling            │  DDL — structure    │
   │ (A) Web          │ (B) Agentic Python         │ (C) Schema-as-code  │
   │     interface    │     tooling                │     (Drizzle ORM)   │
   │     (Nuxt)       │  execution via container   │  generated and      │
   │  TCP pool        │  (temporary SQL file       │  verified SQL       │
   │  postgres-js     │   or inline query)         │  migrations (§6)    │
   └──────────────────┴────────────────────────────┴─────────────────────┘

• Path A — the web interface (Nuxt/Nitro) accesses the database via a TCP connection pool (postgres-js). A central utility exposes the connection; a dedicated adapter handles compatibility with legacy queries (see §4–5).
• Path B — the agentic Python tooling executes queries through a container-based execution mechanism: writes go through a temporary SQL file that is copied and then executed; reads use an inline query. Neither path uses a stdin pipe.
• Path C — DDL is managed as schema-as-code (Drizzle ORM): this is the authoritative source for structural table evolution. It does not manipulate rows; it generates and applies SQL migrations (detailed in §6).

Table Family Snapshot (May 31, 2026)

Family	Volume (base tables)	Scope
Legacy private CodeMyShop / mothership tables	159	Migration debt — cockpit, agents, scars…
Synedre OS cockpit tables	69	Agents, projects, runs, negotiation…
Native PrestaShop tables	18	Historical debt — products, categories, translations…
Public PaaS tables (tenants)	≈ 1 in this schema	Almost absent from the harness schema; lives primarily in tenant DBs

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

The exact count evolves with each project; the figures above are the snapshot from May 31, 2026. The canonical truth query is documented in §7.

2. Table Families and Their Conventions

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

Family	Scope	Status
Public PaaS OSS tables	Community core — FAQ, welcome blocks, etc.	Target (in tenant DBs)
Synedre OS cockpit tables	Agents, projects, runs, negotiation, atomic tasks	Target
Legacy private CodeMyShop tables	Former mothership and cockpit scope	Migration debt
Native PrestaShop tables	Products, categories, translations…	Historical debt

Cross-Cutting Conventions

• Singular name — a table is named in the singular (aligned with the native PrestaShop convention).
• One table = one parent entity — no catch-all tables.
• Source files are named in kebab-case; Vue components in PascalCase.

Migration history. The public PaaS table family results from a one-time rename performed in v0.2.0, from the former legacy prefix (at the time when CodeMyShop was a PrestaShop extension). In the harness schema, the majority of tables remained under the old prefix — the debt has not yet been settled — while the new PaaS prefix is applied in the OSS tenant databases.

2.1 Family Cohabitation on a Single Logical Entity

A point that often surprises newcomers: a single logical entity — the project — is spread across both the legacy and cockpit families.

Table role	Family	Granularity
The project itself	Legacy private	1 row per project
Granular work items	Legacy private	N rows per project
Atomic tasks	Synedre OS cockpit	N rows per work item
Cockpit satellites (team, multi-session lock, QA…)	Synedre OS cockpit	N rows per project

Historical artifact. The atomic tasks table was renamed during the migration to the cockpit family, but its primary key sequence and the PRIMARY KEY constraint retain the old family name. The rename affected the table name, not all dependent objects — to be corrected in the next DDL normalization project.

Data Model of Key Tables

The Projects Table

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

• codename — unique kebab-case identifier for the project (varchar 64, required)
• title — human-readable label (varchar 255, required)
• client_id — optional reference to a client; NULL indicates an internal project
• status — current state, default 'planning'
• priority — priority level, default 'P2'
• current_focus, deadline, notes, mission_letter, preprod_test_plan — steering text fields
• external_contacts — third-party contacts attached to the project
• ship_command — associated delivery command (varchar 255)
• scope — scope constrained by an enum: synedre, codemyshop-oss, codemyshop-enterprise, tenant, business, juridique, negociation, conseil (or NULL). This database-level enum is broader than the one documented in the root reference file, which does not list the values juridique, negociation, and conseil.
• auto_explode — boolean, default true
• mode_auto — boolean, default false
• max_cost_eur — budget ceiling in euros
• archived_at / archived_by — archival timestamp and author
• date_add / date_upd — creation and update timestamps (timestamptz, default now())

An active trigger on this table fires after any update to the status column and propagates the resolution to the inbound e-mail log entries linked to the project. This cascade mechanism ensures consistency between the project state and the items in its associated inbox.

The Tasks Table

Each project is broken down into tasks. The tasks table is linked to the project via a logical foreign key to the work items table. Key columns:

• title, status (default 'todo'), priority (default 'P2')
• assignee_codename — codename of the assigned agent (varchar 64)
• estimated_tokens / actual_tokens / actual_cost_usd — AI consumption metrics
• recommended_model — AI model recommended for this task (varchar 32)
• position — display order
• scope — constrained scope: synedre-internal, codemyshop-oss, codemyshop-enterprise, tenant-single, tenant-multi, infra, doctrine
• visual_intent (text, nullable) — 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 (text, nullable) — verification URL; NULL falls back to the project's staging environment

The visual_intent and visual_url columns were added via an idempotent migration (ADD COLUMN IF NOT EXISTS). They are recognised by the Python entity management layer but are not yet reflected in the TypeScript schema on the web application side — a deliberate divergence between the live database and the application schema, outside the scope of the automatic drift audit tool, which only reads the main schema directory.

The tasks table is also referenced by an inter-task dependency table, enabling the modelling of a scheduling graph.

The Learning Log (Scars and Victories)

This log records errors, lessons learned, and victories from the agent team. Each entry carries:

• agent_codename — codename of the agent concerned (required)
• error_type, description (required), root_cause, corrected_by
• severity — severity level: low, medium, high, critical
• kind — entry type: 'failure' (default) or 'victory' (created via the dedicated skill)
• resolved — resolution state (integer values: 0, 1, or 2)
• tags — array of keywords (text[])
• importance — score from 1 to 10
• recall_count, learnable — pedagogical reusability indicators

This log is for internal use only: it has no language column and is not exposed directly to end users.

Agents: A Compatibility View, Not a Physical Table

Major pitfall when taking over the codebase. The physical agents table is distinct from the historically exposed view. The view is a backwards-compatibility shim resulting from a prior consolidation migration.

The physical agents table contains the columns codename, nickname, role, group_name, active, workstation configuration fields (job_*), cognitive_frame, and heritage. The view exposed under the legacy name is a simple SELECT … FROM projection of this physical table. Any read through the legacy name works transparently; however, writes must target the physical table — views of this type are likely read-only. Verify before any UPDATE or INSERT through the legacy name.

The same view-shim mechanism covers approximately a dozen similar pairs: automata, agent activities, heartbeats, agent relationships, XP and their history, automata and their routines, their logs, and the intelligent steering tables. In total, sixteen views coexist in the database schema, distributed as follows:

• Twelve shim views — transparent redirection from the legacy namespace to the new one
• One event view — linked to the Atlas spawn mechanism
• Three SRE analytical views:
  • Daily aggregation of the learning log over 14 days, broken down by severity level and entry type
  • Summary of review runs over 14 days: total count, rollback count, rollback rate, number of blocking verdicts, last run
  • Warning frequency over 30 days: count by warning code, number of associated blocking verdicts, last occurrence

Error-tracking view not instantiated. A unified error view is referenced in the error-tracking module manifest and called by the API code, but it does not exist in the live database: one of its two sources (PostHog errors) is not instantiated in the current schema. Only the server-error source is present. A prior migration was therefore unable to recreate this view. The actual view count is 16, not 17.

---

Runtime Database Access — Multi-Tenant

Client Resolution and Adapter Selection

The database access module exposes a main function that, given an incoming HTTP request, identifies the relevant tenant and returns a ready-to-use connection adapter.

Tenant resolution follows a three-rule cascade, in priority order:

1. A client identifier explicitly set in the process runtime configuration (each client VPS may define it statically).
2. A match between the request hostname and a tenant-to-database mapping table, enabling multiple tenants to be served from a single application instance.
3. A fallback to the internal identifier of the mothership (ac-hub).

Once the tenant is identified, a second function determines whether the PostgreSQL connection is enabled for that tenant. Two whitelists coexist: mothership-internal tenants (a list hardcoded in the source) and PostgreSQL-enabled tenants dynamically activated via an environment variable. If the tenant belongs to neither list, the function raises an exception — the MySQL path was permanently removed during a prior migration phase.

Global PostgreSQL Lock

Critical point of vigilance. A global lock, controlled by the environment variable PG_ENABLED_DOMAINS, gates PostgreSQL access for all tenants without exception. If this variable does not contain the wildcard *, even a tenant present in the internal whitelist will be rejected. In production, the global switch is in place (*) and the lock is open. However, clearing this variable constitutes a full rollback to MariaDB for the entire system — an operation to be handled with extreme caution.

This mechanism is a legacy of the progressive migration strategy: in the early phases, only certain modules were enabled on PostgreSQL (PG_ENABLED_DOMAINS=cms,inventory); the global switch (=*) was applied only at the final cutover phase.

Tenant-to-Database Mapping Table

The mapping table is built dynamically from environment variables following the convention NUXT_TENANT_DB_<CODENAME_IN_UPPERCASE> (hyphens in the codename being replaced by underscores). The value of each variable encodes, in order: the database name, host, port, user, and password — separated by commas. A utility function exposes the list of known tenant codenames, used in particular to propagate global secrets to each tenant database.

The "one tenant = one canonical codename" convention applies to approximately fifteen surfaces of the system (configuration, routing, database, logs, etc.). The client resolution function is one of the key surfaces.

---

The PostgreSQL Adapter

The PostgreSQL adapter exposes a query / get / run interface identical to the legacy MySQL interface, enabling transparent substitution. It relies on the postgres-js library and performs on-the-fly conversion of legacy MySQL SQL.

The Connection Pool

A connection singleton is instantiated with the following parameters (read from environment variables):

• Host, port, user, and database name — with internal default values if variables are absent
• Database password — required: the adapter raises an immediate exception if this variable is absent
• Pool: 20 maximum connections, 60 s idle timeout, 1 800 s maximum lifetime, 15 s connection timeout

All queries are executed within the PostgreSQL schema dedicated to the mothership.

The SQL Translation Layer

Each SQL query is passed through a rewriting pipeline before execution. Transformations are applied in the following order:

#	Transformation	Detail
1	Backticks → double quotes	Conversion of MySQL identifier delimiters to PostgreSQL syntax
2	Schema qualification	Tables prefixed by internal namespaces are automatically prefixed with the PostgreSQL schema name after the keywords FROM, JOIN, INTO, UPDATE, TABLE
2b/2c	DATE_SUB / DATE_ADD	Rewritten as PostgreSQL interval arithmetic, for the units DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, on both literals and placeholders
2d	TIMESTAMPDIFF	Converted to FLOOR(EXTRACT(EPOCH FROM …) / divisor) according to the requested unit
3	IFNULL → COALESCE	PostgreSQL does not recognise IFNULL
4	INSERT IGNORE → ON CONFLICT DO NOTHING	No-op if ON CONFLICT is already present
5	Auto-quoting of camelCase aliases	AS fooBar → AS "fooBar" to preserve case (PostgreSQL lowercases unquoted identifiers). Exception: native PostgreSQL types are left as-is to avoid interfering with CAST(x AS TEXT) expressions
6	? placeholders → $1, $2, …	Positional conversion by a character-by-character parser that ignores ? characters appearing inside quoted strings

Parameter safety. The placeholder transformation only renames markers within the SQL text. The actual binding of values is delegated to postgres-js via its native parameterised query mechanism — no value is interpolated into the SQL string. The parameter array is passed through as-is, without modification. There is therefore no SQL injection risk on bound parameters.

Cases not handled automatically (the calling code must handle these manually): ON DUPLICATE KEY UPDATE, LAST_INSERT_ID(), GROUP_CONCAT, FIND_IN_SET, DATE_FORMAT, CURDATE(). When an endpoint requires one of these, a dedicated conditional branch is added on the caller side.

Interface and MySQL insertId Emulation

The adapter interface exposes three methods:

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

The run() method emulates the MySQL insertId: for an INSERT without a RETURNING clause or ON CONFLICT, it automatically appends RETURNING id_<entity> following the primary key naming convention. This logic is disabled for tables with a composite primary key (an explicit list in the source code) as well as for all tables suffixed _lang or _shop, which do not have a unique id_<table> column.

Drizzle ORM — schema-as-code for table structure

The paths described in the previous sections (TypeScript access on the Nuxt side, Python access on the tooling side) perform DML: they read and write rows against a table structure assumed to already be in place. Neither of them creates or 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 hand-written) and then applied to the database, never the other way around.

The dialect was MariaDB before the migration to PostgreSQL, which took effect during an earlier decoupling effort. The relevant dependencies are drizzle-orm and drizzle-kit at their current versions.

Drizzle 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	Three globs covering the OSS core, cockpit modules, and enterprise packs	Locations of TypeScript declarations
out	Generated SQL migrations folder, inside the OSS core	Destination for produced .sql files
schemaFilter	Dedicated application schema	Restricts introspection/diff to our schema, ignores public and native schemas
DB credentials	Carried by environment variables (host, port, user, DB password, database name)	Never written in plaintext in the repository
strict / verbose	true / true	Confirmation before push, verbose output

⚠️ The default port configured here targets the TCP exposure of the database on the host machine (host mapping), not the container's internal port. drizzle-kit is designed to point at a TCP-exposed database, not to access the container's internal process directly.

drizzle-kit commands

No dedicated npm scripts are exposed in the project manifest. Commands are run directly via npx:

Command	Effect
npx drizzle-kit generate	Compares TS schemas against the known state and writes a new SQL migration to the output folder
npx drizzle-kit migrate	Applies pending migrations to the database targeted by the credentials
npx drizzle-kit introspect	Reverse-engineers an existing database into TypeScript files (useful for onboarding a legacy table not yet declared)

TypeScript schemas

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

Scope	Contents	Audited by the drift detector
OSS core + store tables	Historical tables of the e-commerce platform (approximately 85 files, including re-exports)	✅ Yes
Mother-ship cockpit modules	Agents, projects, tasks, invoicing, drill… (approximately 47 files)	❌ No
Enterprise packs	Advanced business extensions (approximately 14 files)	❌ No

Important note: a typing reference file defines the module registry table, but it falls outside the three active globs — it serves as an illustration and typing reference, not as an active DDL input for Drizzle. Cockpit tables are declared inside the modules (scope 2); their migrations go through a separate system (see below), not through the Drizzle DDL.

The drift detector only reads the OSS core scope. Columns declared in cockpit modules or enterprise packs do not trigger a blocking drift — which is why recently added columns can coexist in the database and in the entity layer without being present in the module's Drizzle schema.

What TypeScript schemas type: physical column name, PostgreSQL type, notNull, default/defaultNow, primaryKey (simple or composite), unique, indexes. Business types are refined via $type<...>() (TS typing with no DB constraint). Example declaration for a module registry table:

export const appSchema = pgSchema('application_schema_name')

export type Runtime      = 'ps' | 'nuxt'
export type ModuleStatus = 'active' | 'disabled' | 'deprecated'

export const moduleRegistryTable = appSchema.table('registry_table_name', {
  idModuleRegistry: serial('id_module_registry').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<ModuleManifest | null>(),
  // … schema hash, last migration date, dateAdd, dateUpd
}, (t) => ({
  kRuntimeStatus: index('idx_runtime_status').on(t.runtime, t.status)
}))

export type RegistryRow    = typeof moduleRegistryTable.$inferSelect
export type RegistryInsert = typeof moduleRegistryTable.$inferInsert

Key takeaways:

• MariaDB ENUMs have been ported to varchar(N) + $type<Union>(): the constraint is enforced at the TypeScript level, not as a native PostgreSQL ENUM type.
• Multilingual tables with composite primary keys translate to primaryKey({ columns: [t.idFaq, t.idLang] }).
• The exported $inferSelect/$inferInsert types provide row types consumable on the code side. The Nuxt runtime goes through its own adapter (direct DML access), not through the Drizzle query builder — here Drizzle is used solely for DDL and typing.

Generated migrations

The OSS core migrations folder currently contains three idempotent migrations: addition of logistics columns, import mapping, and addition of a phone column on the customer table for B2B/C registration. The Drizzle journal file (meta/_journal.json) shows an empty entry list.

⚠️ Structural fact: the automatic application tracking of drizzle-kit migrate is not the operational path. The SQL files in the output folder are hand-written as idempotent (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS) and applied manually on each customer database. In practice, Drizzle and the TypeScript schemas constitute the declarative source of truth; actual application remains a manual per-database operation, never auto-propagated.

Second migration path (cockpit tables): mother-ship cockpit tables — declared inside modules, outside the drift detector's scope — use a separate system of manual SQL migrations, spread across two folders:

• A folder dedicated to the mother-ship schema (synedre.com, Odyssée, documentation, AI routing…). Files are named by date and subject, all idempotent.
• A root folder for migrations applied directly to the local database (cockpit tables outside the mother-ship). This folder also hosts archiving sub-folders (applied/, _applied/) that track migrations already executed on the primary target.

Application is manual in both cases — these migrations do not go through the automatic drift application mechanism described below.

The TS ↔ live database drift detector

Since migration application is manual and multi-tenant, an ALTER applied on one database but forgotten on another could silently reach production. An audit script (approximately 315 lines) closes this gap.

How it works:

• The parsing function reads TypeScript files from the OSS core scope and extracts, via regex, the list of tables and their columns.
• The database query function reads information_schema.columns for the relevant application schema, restricted to core-scope tables, against each target database.
• The per-target diff distinguishes: blocking = table or column declared in TS but absent from the database (missing migration); info = present in the database but not in TS.
• False positives are filtered by an exclusions file: mother-ship-only tables to ignore on customer databases, and OSS store tables removed from the central database during an earlier decoupling effort.

Exit codes: 0 — no blocking drift; 1 — blocking drift detected; 2 — execution error.

The audit can be run against a single database (default mode, backward-compatible), a named tenant, all tenants, or with verbose output.

Deployment gate: the audit is wired as a blocking step in the deployment pipeline. Before pushing an artifact, the pipeline verifies that the target tenant's database has all the columns expected by the code. If drift is detected, the deployment stops. This is a pre-deployment check; no dedicated periodic cron job exists.

The automatic DDL drift applicator

A second script (approximately 553 lines) complements the audit: where the audit reports drift, this script fixes it.

Core principles:

• No DROP ever (neither table nor column). Only missing items are added.
• Idempotent: re-runnable with no effect if the database is already up to date.
• Single transaction with 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 the TypeScript schemas.

The script reuses the auditor's definitions (target list, schema parsers, exclusion loading) to maintain a single source of truth.

Internal pipeline:

1. Parsing TypeScript schemas into TableDef/ColumnDef structures (PG types, nullable, default, check).
2. Computing the TS ↔ live database drift: missing tables → CREATE TABLE; missing columns → ADD COLUMN.
3. Generating the idempotent SQL block.
4. Executing in a transaction against the target database (only if the --apply flag is passed).

Usage mode	Effect
Dry-run on a tenant	Displays the SQL without executing it (default behavior)
Apply on a tenant	Applies the SQL in a transaction against the target database
Dry-run on all tenants	Inspects all databases without making any changes

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

The applicator is wired into the unified deployment path, conditioned by an activation flag (DRIFT_AUTO_APPLY, disabled by default — explicit gate). Since the consolidation of deployment scripts during a recent effort, all tenants go through this unified path; the old per-tenant deployment scripts have been removed, closing the historical gap where some tenants did not benefit from automatic remediation.

Authoritative source for structural changes

TypeScript schema files           ← DECLARATIVE SOURCE OF TRUTH for DDL
   │  (1) edit the TS
   ▼
Idempotent SQL migration
   │  (2a) Manual application per impacted database
   │  (2b) Automatic drift applicator (flag DRIFT_AUTO_APPLY=1)
   ▼
Live PostgreSQL database (mother-ship + customer databases)
   ▲
   └─ (3) Drift detector: verifies TS == live, blocks deployment on mismatch

Change rule: to evolve a table in the core scope, first edit the TypeScript schema, generate or hand-write the idempotent migration, apply it on each database (manual or automatic path), then re-run the audit — the deployment pipeline will replay it regardless. Never modify the live database without reflecting the change in the TypeScript schema: the audit would flag it as blocking drift on the next deployment.

Conversely, the Python Entity classes used by the tooling never create columns: their field list is a whitelist of columns assumed to already exist in the database, not a structure declaration.

Agentic Access (Python) — the Entity Pattern

The mother-ship Python code does not access the database through a network connection pool the way the frontend runtime does. It goes through a subprocess mechanism that delegates SQL execution to the database container. A shared base class centralises this transport and exposes a generic CRUD layer from which roughly forty specialised business classes inherit.

Low-level transport

Three helpers on the base class cover the common use cases:

Helper	Usage	Notes
Safe write	INSERT / UPDATE / DELETE	The SQL is written to a uniquely named temporary file (PID + UUID to avoid multi-process collisions), copied into the container, then executed with immediate halt on error (ON_ERROR_STOP=1). No stdin pipe is used.
Tabular read	SELECT on simple columns	SQL passed on the command line, result tab-separated. Should be avoided on TEXT columns containing newlines.
CSV read	SELECT on multi-line TEXT columns	CSV mode (RFC 4180); NULL values are returned as empty strings.

Every query automatically prefixes the session with the appropriate application schema. Connection parameters (target container, database name, user, DB password, schema) are read from the process environment variables — no credentials are hard-coded.

The Entity class — generic CRUD

The base class exposes a CRUD layer parameterised by three class attributes:

• Table name — the target physical table.
• Primary key — name of the PK column, used in RETURNING and WHERE clauses.
• Field whitelist — only columns declared here are accepted in INSERT/UPDATE; any column absent from the list is silently ignored.

Standard behaviour of each method:

• Create: business validation → automatic injection of the canonical client_id if the column is in the whitelist → whitelist filtering → INSERT … RETURNING <pk> with NOW() timestamp on date_add and date_upd.
• Update: whitelist filtering + update of date_upd.
• Search / existence check / delete: standard implementations.

Internal SQL escaping natively handles booleans (TRUE/FALSE), dictionaries, and lists (serialised as JSON).

Sub-classes override the validate(data, mode) method to place business rules: blocking violations raise a ValidationError; non-blocking warnings are collected and returned to the caller without interrupting the operation.

Atomic creation of a worksite with its skeleton

To prevent orphaned worksites (with no job or task), the worksite component's creation method creates the following in a single BEGIN … COMMIT transaction:

1. The worksite itself.
2. A first job attached to the worksite (lookup by codename).
3. One or more initial tasks attached to the job (lookup by job identifier).

The ON_ERROR_STOP=1 option guarantees a full rollback if any one of the inserts fails.

Blocking validations

• The codename must conform to kebab-case format (4 to 64 characters) and be unique.
• Priority must belong to the set {P0, P1, P2, P3}.
• Each task must have a non-empty title and assignee; the assignee must exist in the agent registry.
• Multi-agent staffing: for any worksite whose scope concerns a client tenant, at least two distinct agents must be assigned — a rule derived from the cross-supervision doctrine.

Automatic behaviours

• The fields priority, description, estimated_tokens, estimated_h, position, recommended_model, scope, visual_intent, and visual_url are transparently propagated from the worksite to each task.
• If recommended_model is absent from a task, it is automatically computed by the model-selection heuristic (see below).

Task Entity — estimation, recommended model, skills and tools

Automatic estimation

When a task is created, if the token volume is not supplied, a dedicated estimator is called automatically. A non-blocking warning is emitted if the estimate remains missing.

AI model selection heuristic

The model recommendation method follows a cascade of criteria:

• Most capable model if the priority is P0, or if the task has failed repeatedly (≥ 2 failed iterations), or if the estimated volume exceeds 8,000 tokens.
• Mid-tier model if the volume is between 1,500 and 8,000 tokens.
• Lightweight model for tasks below 1,500 tokens with no aggravating factor.

Attaching skills and tools

Two methods allow a task to be qualified:

• Skill: lookup in the skill registry by name (natural key) → insertion into the join table with an ON CONFLICT … DO NOTHING clause. If the skill is unknown, a proposal is recorded in pending status and the method returns False.
• Tool: same logic via the tool registry (lookup by slug). Unknown tool → pending proposal + return False.

Status cascades

Status updates on tasks and jobs trigger automatic cascades to higher levels:

Task → done / cancelled
  (condition: all tasks in the job are in a terminal state,
              with at least one in done)
  └─ Cascade to the Job
       ├─ A QA team is staffed?
       │    └─ Yes → the QA verdict is applied (validation run)
       └─ No → Job moves to done

Job → done / cancelled
  (condition: all jobs in the worksite are terminal,
              with at least one in done)
  └─ Cascade to the Worksite
       ├─ Discovery-only safeguard:
       │    all jobs completed in discovery phase
       │    → automatic explosion (an LLM agent generates the implementation jobs)
       │    instead of promoting the worksite
       └─ Otherwise → Worksite moves to test status (pre-prod, awaiting review)
            └─ Non-blocking warning if the pre-prod test plan
               or the delivery command are missing

Automatic resolution of blocked jobs

A corrective job can be linked to a parent job on hold. When the corrective job moves to done, the parent job is automatically closed: its status changes from paused to done, its remaining tasks move to cancelled, and the decision is recorded in the parent job's audit log.

Internationalisation conventions and polymorphism

Translation tables (_lang)

Any text visible to a visitor lives 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 PK becomes (id_<entity>, id_lang, id_shop) — no separate _shop_lang table is created.
• Strict separation of concerns: the parent table carries foreign keys, flags, dates, and enumerations; the _lang table carries text fields (title, description, meta_*, and any visitor-facing text).

This convention has a direct impact on the query adapter: _lang tables are excluded from the primary-key retrieval heuristic because they do not have a simple PK. On the frontend side, all strings go through the translation function; no UI string may be hard-coded in the source code.

Polymorphism via parent_type / parent_id

When a feature applies to several parent entity types, a single polymorphic table is created — never one table per parent type. The parent_type column carries a textual discriminant ('cms', 'category', 'product'…) and parent_id carries the identifier in the corresponding table.

Documented exception: a 1:1 extension of a native entity adopts the extra-table pattern (PK = FK to the native entity), without polymorphism. Pure N:N join tables are named in alphabetical order of the two entities, with no _asso or _link suffix, and with no associated _lang table.

No business JSON in columns

JSON content columns (payload_json, content_i18n, labels_json…) are prohibited for structured business content. The only tolerance applies to ephemeral technical payloads (webhooks, logs, session state) explicitly documented with a column comment.

In the agentic cockpit, JSON columns are reserved for append-only technical audit trails (decision context, findings, iteration history) — this is the tolerated exception, not the general rule. An automated schema audit (scheduled nightly loop) detects and flags violations at P0 priority.

Common integration pitfalls

1. Shim views vs physical tables: several entity names exposed in the API are in fact views that read from physical tables under a different name. For reads, the view is sufficient; for writes, the underlying physical table must be targeted. This pattern applies to about a dozen pairs (agents, agent activity, heartbeat, relations, XP, XP history, automata, automaton agents, pipelines, automaton logs, smart-rule logs).
2. Container ≠ database ≠ schema: the database container, the logical database name, and the application schema are three distinct levels. Conflating them produces silent resolution errors.
3. Non-migrated PostgreSQL tenant: the query adapter raises an immediate error for any tenant not declared in the list of enabled PostgreSQL tenants. The MySQL fallback path has been removed.
4. scope enumeration: the CHECK constraint in the database is the single source of truth for the allowed values of the scope field — it may be broader than what the written documentation states.
5. Rename residue: the task table has retained its sequences and constraints named after the old prefix, a residue of a partial rename. This is not an error — it is a known state.
6. Legacy MySQL SQL: an automatic converter transforms MySQL queries to PostgreSQL, but several constructs are not covered (GROUP_CONCAT, DATE_FORMAT, ON DUPLICATE KEY UPDATE…). These cases fail silently and require a manually written native PostgreSQL branch.