The Data Layer

This page describes how the Synedre OS agentic harness reads and writes its data: a single PostgreSQL database, an adapter that translates legacy MySQL→PostgreSQL SQL on the Nuxt side, Python Entity classes on the agentic tooling side, Drizzle ORM as schema-as-code for DDL and migrations, and the naming conventions (table prefixes, _lang i18n, polymorphism) that hold it all together. It is meant for engineers picking up the codebase.

The single database

All the mothership's data lives in one PostgreSQL schema: vaisseau_mere_ac, hosted in the ac_hub database, inside a dedicated Docker container.

Element	Value
Docker container	ac_postgres
Database	ac_hub (≠ ac_postgres)
Schema	vaisseau_mere_ac
User	<pg_user>
Password	carried by the environment, never in clear text (required, otherwise it throws)

Watch out for the recurring trap: the container is named ac_postgres but the database is named ac_hub. The ps_ac_* tables live in the vaisseau_mere_ac schema, not public.

Three access paths coexist — two at runtime (DML: read/write rows), one at design time (DDL: evolve the structure):

                  PostgreSQL  ac_postgres / ac_hub
                  schema  vaisseau_mere_ac
                              │
   ┌───────────────────┬──────┴────────────────┬──────────────────────┐
   │ DML runtime        │ DML tooling           │ DDL / structure       │
   │ (A) Nuxt/Nitro     │ (B) Python agentic    │ (C) Drizzle ORM       │
   │  useClientDb()     │  class *Entity        │  drizzle-kit generate │
   │  → PgAdapterClient │  subprocess docker    │  + SQL migrations     │
   │  postgres-js (TCP) │  exec psql            │                       │
   └────────────────────┴───────────────────────┴──────────────────────┘

The two DML paths talk to the same schema but through different transports: postgres-js (TCP pool) on the Nuxt side, docker exec psql on the Python side. The third path (C) handles no rows: it is the DDL as schema-as-code (Drizzle ORM), which is authoritative for the structural evolution of the ps_ac_*/cs_* tables.

Live state (counted via information_schema.tables):

Family	Tables	Scope
ps_ac_*	~159	Private CodeMyShop/mothership legacy (debt to migrate)
sy_*	~69	Synedre OS cockpit (agents, chantiers, runs, negotiations…)
ps_*	~18	Native PrestaShop (historical debt: ps_product, ps_category, ps_translation, ps_lang…)
cs_*	~1	Public OSS PaaS — almost absent from this schema (the cs_* mostly live in tenant databases)

Added to these are about fifteen views in vaisseau_mere_ac (critical point: ps_ac_agents is a view over sy_agents).

The exact count shifts with every chantier; the figures above are a snapshot. The ground-truth query is given below.

The table families

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

Prefix	Scope	Status	Examples
cs_*	Public OSS PaaS (core + community)	target	cs_faq, cs_homepage_block (in tenant databases)
sy_*	Synedre OS — internal agentic cockpit	target	sy_chantier_tache, sy_agents, sy_task_run, sy_negociation
ps_ac_*	Private CodeMyShop / mothership legacy	debt to migrate	ps_ac_chantier, ps_ac_cicatrices, ps_ac_client_vps
ps_*	Native PrestaShop	historical debt	ps_product, ps_category_lang, ps_translation

Cross-cutting conventions:

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

History: the cs_* prefix is the result of a one-off rename in v0.2.0 from ps_ac_* (back when CodeMyShop was a PrestaShop extension named "ac"). In the mothership schema, most tables stayed on ps_ac_* (the debt has not yet been cleared), while cs_* is applied in the OSS tenant databases.

Prefix coexistence within a single chantier entity

A surprise when picking up the code: a single logical "chantier" entity is split across both the legacy AND cockpit prefixes.

Table	Prefix	Role
ps_ac_chantier	ps_ac_	the chantier (1 row per chantier)
ps_ac_chantier_travail	ps_ac_	the granular travaux (N per chantier)
sy_chantier_tache	sy_	the atomic tasks (N per travail)
sy_chantier_agent, sy_chantier_lock, sy_chantier_qa_run…	sy_	cockpit satellites (team, multi-session lock, QA)

A telling detail of the rename: the table is named sy_chantier_tache, but its sequence and primary key keep the old name (cs_chantier_tache_id_tache_seq, cs_chantier_tache_pkey). The rename touched the table name, not all the dependent objects.

Data model of the key tables

ps_ac_chantier

Primary key id_chantier (serial). Notable columns:

codename            varchar(64)  NOT NULL   -- unique kebab-case
title               varchar(255) NOT NULL
client_id           varchar(32)             -- NULL = internal chantier
status              varchar(9)   NOT NULL  default 'planning'
priority            varchar(2)   NOT NULL  default 'P2'
current_focus       text
deadline            date
notes / mission_letter / preprod_test_plan  text
external_contacts   text
ship_command        varchar(255)
scope               varchar(32)             -- CHECK enum
auto_explode        boolean NOT NULL default true
mode_auto           boolean NOT NULL default false
max_cost_eur        numeric(8,2)
archived_at / archived_by
date_add / date_upd timestamptz NOT NULL default now()

Live CHECK constraint on scope: synedre | codemyshop-oss | codemyshop-enterprise | tenant | business | juridique | negociation | conseil (or NULL). This DB enum is broader than the one documented in the root doctrine.

Live trigger: tr_chantier_done_cascade_inbox AFTER UPDATE OF status → function cascade_resolved_inbox_on_chantier_done() (cascade towards ps_ac_inbox_emails).

sy_chantier_tache

Primary key id_tache. Key columns: id_travail (logical FK to ps_ac_chantier_travail), title, status (default todo), priority (default P2), assignee_codename, estimated_tokens/actual_tokens/actual_cost_usd, recommended_model, position, scope. CHECK on scope: synedre-internal | codemyshop-oss | codemyshop-enterprise | tenant-single | tenant-multi | infra | doctrine. Referenced by sy_tache_dep (inter-task dependency graph).

ps_ac_cicatrices

The journal of errors/lessons (and victories). Primary key id_cicatrice. Columns: agent_codename NOT NULL, error_type, description NOT NULL, root_cause, corrected_by, severity (CHECK low|medium|high|critical), kind (default failure; victory via the /victoire skill), resolved (smallint, CHECK ∈ {0,1,2}), tags text[], importance (1-10), recall_count, learnable. No _lang column (internal, non-user-facing content).

Agents: ps_ac_agents is a VIEW over sy_agents

A major pickup trap. The physical agents table is sy_agents (primary key id_agent, columns codename, nickname, role, group_name, active, job_*, cognitive_frame, heritage…). ps_ac_agents is not a table: it is a view (SELECT … FROM sy_agents), a backward-compatibility shim left over from the agents/automates migration.

The root doctrine still cites SELECT … FROM ps_ac_agents WHERE active=1, and the code validates assignees against ps_ac_agents — both work because the view is transparent for reads. The same view-shim mechanism covers a dozen other pairs:

ps_ac_agents            → sy_agents          ps_ac_automates         → sy_automates
ps_ac_agent_activity    → sy_agent_activity  ps_ac_automate_agents   → sy_automate_agents
ps_ac_agent_heartbeat   → sy_agent_heartbeat ps_ac_automate_conduites→ sy_automate_conduites
ps_ac_agent_relations   → sy_agent_relations ps_ac_automate_logs     → sy_automate_logs
ps_ac_agent_xp[_history]→ sy_agent_xp[...]   ps_ac_smartautomation_* → sy_smartautomation_*

On top of the 12 view-shims sit a few analytical views:

• v_sre_cicatrices_daily — aggregates ps_ac_cicatrices from the last 14 days per (tenant, day, severity, kind) with count(*).
• v_sre_rollbacks — over the review runs (14 d) per tenant: total runs, rollback count, rollback_rate_pct, number of NO_GO verdicts, last run.
• v_sre_warnings_freq — unnest of the warnings array of the review runs (30 d): frequency per warning code, number of NO_GO, last occurrence.
• cs_errors_unified — normalized union of front-end and back/server errors under a common source schema.

These views are read-only; writes must target the physical sy_* table. Check before any UPDATE ps_ac_agents.

Runtime access (Nuxt/Nitro) — multi-tenant

resolveClientId(event) → useClientDb(event)

useClientDb(event)
   └─ resolveClientId(event)            → string clientId
        1. runtimeConfig.clientId (≠ 'ac-hub')  ← each tenant VPS sets it
        2. hostname matching in CLIENT_DB_MAP   ← multi-tenant on a single Nuxt
        3. default 'ac-hub'
   └─ useClientDbById(clientId)
        └─ shouldDispatchToPg(clientId) ?
             yes → buildPgAdapter(clientId)
             no  → throw  (mysql2 path removed)

resolveClientId prioritises useRuntimeConfig(event).clientId. Failing that, it derives a keyword from the hostname and matches it against the keys of CLIENT_DB_MAP. Final fallback: 'ac-hub'.

useClientDbById(clientId) returns an adapter only if shouldDispatchToPg() is true — that is, isDomainOnPg('*') AND the tenant is in the allow-list of migrated tenants. Otherwise it throws: the mysql2 path has been removed.

Global gate isDomainOnPg('*'). It is a priority gate: shouldDispatchToPg() returns false for everyone (even a listed tenant) until a global activation flag is set. A holdover from the progressive rollout of the PostgreSQL migration: per-domain opt-in at first, global activation at cutover. In production the cutover is done, so the gate is true.

CLIENT_DB_MAP (per-tenant DB config)

buildClientDbMap() reads, for each tenant, its connection parameters from a dedicated environment variable (its name derived from the tenant codename). getKnownTenantClientIds() returns the keys, used to broadcast global secrets to each tenant database.

The "1 tenant = 1 codename everywhere" convention (about fifteen surfaces) requires resolveClientId() to return the canonical codename.

The postgres-js adapter

Role: expose a query/get/run interface identical to the old mysql2 useClientDb, but dispatch to postgres-js (the postgres library) while converting the legacy MySQL SQL on the fly.

The pool

getPgClient() instantiates a postgres({...}) singleton:

Host, port, user, database and password are read from the environment (no hardcoded production values; the password is mandatory, otherwise instantiation fails). The pool itself is tuned as follows:

max=20, idle_timeout=60, max_lifetime=1800, connect_timeout=15

PG_SCHEMA = 'vaisseau_mere_ac' (constant).

convertMysqlToPg(sql) — the translation

Rewrite pipeline applied to every query, in order:

#	Transformation	Detail
1	Backticks → double quotes	`col` → "col"
2	Schema-qualify	after FROM/JOIN/INTO/UPDATE/TABLE, ps_xxx and cs_xxx → vaisseau_mere_ac.ps_xxx
2b/2c	DATE_SUB/DATE_ADD + INTERVAL N UNIT → PG arithmetic	handles DAY/MONTH/YEAR/HOUR/MINUTE/SECOND, both literal AND placeholder
2d	TIMESTAMPDIFF(unit,a,b) → FLOOR(EXTRACT(EPOCH FROM (b-a))/divisor)	SECOND/MINUTE/HOUR/DAY
—	IFNULL(a,b) → COALESCE(a,b)	PG has no IFNULL
—	INSERT IGNORE INTO … → INSERT … ON CONFLICT DO NOTHING	skipped if ON CONFLICT already present
—	Auto-quote aliases AS fooBar → AS "fooBar"	preserves case. Exception: native PG types (TEXT, INTEGER…) left as-is so as not to break CAST(x AS TEXT)
3	Placeholders ? → $1, $2, …	positional conversion, ignoring ? inside quoted strings (char-by-char parser)

Transformation #3 only renames the markers (? → $N) in the SQL text. The actual value binding is NOT textual: postgres-js binds the positional params via sql.unsafe(pgSql, params). The params array is left unchanged — no value is interpolated into the SQL string (no injection risk on params).

Not handled automatically (the caller must port it by hand): ON DUPLICATE KEY UPDATE, LAST_INSERT_ID(), GROUP_CONCAT, FIND_IN_SET, DATE_FORMAT, CURDATE(). When an endpoint needs one, a dedicated onPg() branch is added.

The PgAdapterClient interface and the RETURNING heuristic

interface PgAdapterClient {
  clientId: string
  query<T>(sql, params?): Promise<T[]>
  get<T>(sql, params?): Promise<T | null>          // [0] ?? null
  run(sql, params?): Promise<{ affectedRows, insertId }>
}

run() emulates the MySQL insertId: if the query is an INSERT INTO ps_xxx without RETURNING or ON CONFLICT, it appends RETURNING id_<entity> (PS convention: strip ps_, and also ac_ for ps_ac_*). Safeguards: tables with a composite PK are excluded — COMPOSITE_PK_TABLES (ps_customer_group, ps_category_product, ps_carrier_zone, ps_accessory, ps_ac_category_cross) + any table suffixed _lang/_shop, since these have no unique id_<table> column.

Drizzle ORM — schema-as-code for DDL

The two preceding paths (postgres-js on the Nuxt side, Python on the tooling side) do DML: they read and write rows against a structure assumed to be already in place. Neither creates nor alters a table. The structure (DDL — CREATE TABLE, columns, types, indexes, constraints) is governed by a third path: Drizzle ORM, used in schema-as-code mode. The TypeScript schemas are the declared source of truth for the structure of the ps_ac_*/cs_* tables; the CREATE/ALTER SQL is generated (or written) then applied to the DB, never the other way around.

History: the dialect was MariaDB before the PostgreSQL switch (drop MariaDB → dialect: 'postgresql'). Dependencies: drizzle-orm + drizzle-kit.

drizzle.config.ts

Key	Value	Note
dialect	'postgresql'	ex-MariaDB
schema	3 globs: codemyshop/core/server/db/schema-pg/*.ts, mothership-app/modules/*/server/db/*.ts, codemyshop/enterprise/*/modules/*/server/db/*.ts	where the TS declarations live
out	codemyshop/core/server/db/migrations	folder of generated SQL migrations
schemaFilter	['vaisseau_mere_ac']	restricts introspection/diff to our schema
dbCredentials	connection parameters read from the environment	values carried by the environment, never in clear text
strict / verbose	true / true	confirmation before push, detailed output

This config's default port differs from that of the postgres-js adapter / the Python pattern (which target the ac_postgres container directly). drizzle-kit is designed to point at a TCP-exposed DB, not to go through docker exec.

drizzle-kit commands

Command	Effect
npx drizzle-kit generate	diff TS schemas ↔ known state → writes a new SQL migration in out/
npx drizzle-kit migrate	applies pending migrations to the dbCredentials DB
npx drizzle-kit introspect	reverse-engineer an existing DB → TS files (useful to adopt a not-yet-declared legacy table)

The TS schemas

Each file declares one or more tables via pgSchema('vaisseau_mere_ac').table('<table_name>', { ... }). The inventory lives in codemyshop/core/server/db/schema-pg/ (including one-line bridge re-exports towards codemyshop/runtime-base/server/db/schema-pg/ from the OSS extraction), plus a mothership-side file that defines the real mothership table.

What the TS schemas type: physical column name, PG type, notNull, default/defaultNow, primaryKey (simple or composite), unique, indexes. Business types are refined via $type<...>() (TS typing with no DB constraint). Example ps_ac_module_registry (runtime source of truth for PS/Nuxt per module and per tenant):

export const vaisseauMereAcSchema = pgSchema('vaisseau_mere_ac')
export type Runtime      = 'ps' | 'nuxt'
export type ModuleStatus = 'active' | 'disabled' | 'deprecated'

export const moduleRegistryVaisseau = vaisseauMereAcSchema.table('ps_ac_module_registry', {
  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>(),
  // … schemaHash, lastMigratedAt, dateAdd, dateUpd
}, (t) => ({ kRuntimeStatus: index('idx_runtime_status').on(t.runtime, t.status) }))

export type ModuleRegistryPgRow    = typeof moduleRegistryVaisseau.$inferSelect
export type ModuleRegistryPgInsert = typeof moduleRegistryVaisseau.$inferInsert

• The MariaDB ENUMs were ported to varchar(N) + $type<Union>() (constraint at the TS level, no PG ENUM type).
• The composite-PK _lang convention translates to primaryKey({ columns: [t.idFaq, t.idLang] }).
• The exported $inferSelect/$inferInsert give the row types consumable in code (but the Nuxt runtime goes through the postgres-js adapter, not the Drizzle query builder — here Drizzle serves only DDL and typing).

The generated migrations and the actual application

A fact that structures the pickup: the migration journal (meta/_journal.json) holds an empty entries list. Automatic application tracking via drizzle-kit migrate is therefore not the operative path. The SQL files in out/ are in fact hand-written idempotent (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS) and applied manually per tenant via a psql stdin against each affected database. In practice: Drizzle / the TS schema = declarative source of truth; the real application remains a per-tenant psql -f/stdin, never auto-propagated.

Anti-drift safeguard TS ↔ live DB

Because migration application is manual and multi-tenant, an ALTER applied on one database but forgotten on another would ship to production silently. An audit automate closes this hole:

• it reads codemyshop/core/server/db/schema-pg/*.ts and extracts {table → {columns}} by regex, stripping comments first.
• it queries each tenant's information_schema.columns (WHERE table_schema='vaisseau_mere_ac' AND (table_name LIKE 'ps\_ac\_%' OR LIKE 'cs\_%')), via local docker exec for the mothership, via SSH + docker exec for remote tenants.
• Diff per tenant: blocking = table or column declared in TS but absent from PG (missing ALTER); info = present in PG but not in TS.
• False positives filtered by an ignore file: mothership-only tables to ignore on tenants, and OSS store tables moved out of the mothership database (ignored only on the mothership target).

Exit codes: 0 no blocking drift, 1 blocking drift, 2 execution error.

Deployment gate. The audit is wired blocking into the deploy pipeline: with strict mode on by default, the deploy stops before pushing an artifact whose code expects columns the tenant DB does not have. It is a pre-deploy check, not a periodic cron.

What is authoritative for structural evolution

schema-pg/<entity>.ts        ← declarative SOURCE OF TRUTH of the DDL (ps_ac_*/cs_*)
   │  (1) edit the TS
   ▼
SQL migration out/ (manual, idempotent)
   │  (2) psql -f / stdin on EACH affected tenant
   ▼
PG live (mothership + each tenant)
   ▲
   └─ (3) the audit checks TS == live, blocks the deploy on drift

Pickup rule: to evolve a ps_ac_*/cs_* table, edit the TS schema first, generate/write the idempotent migration, apply it per tenant, then re-run the audit (and the deploy replays it anyway). Never ALTER the live DB without reflecting it in the TS. Conversely, the Python Entity classes never create a column: their fields tuple is a whitelist of columns assumed already present, not a structure declaration.

Agentic access (Python) — the Entity pattern

Base file: synedre/ac_entities/base.py. Around forty *Entity classes inherit from it (chantier.py, cicatrice.py, agent.py, doctrine.py, client_vps.py…).

Transport: docker exec psql

Unlike the Nuxt runtime (TCP pool), the Python talks to the DB via subprocess docker exec … psql. Three helpers:

Helper	Usage	Note
_run_sql_write(sql)	writes	writes the SQL to a temp file, docker cps it into the container (unique pid+uuid path to avoid a multi-process race), then psql -f <file> with ON_ERROR_STOP=1 — no stdin pipe
_run_sql_read(sql)	tab-separated reads	inline SQL via psql -c; do not use on TEXT columns with newlines
_run_sql_csv(sql)	reads with newlines	inline SQL via psql -c, --csv mode (RFC 4180), NULL → ''

All prefix SET search_path TO vaisseau_mere_ac, public;. DB_NAME = PG_SCHEMA (backward-compat alias: in PG the legacy "table prefix" becomes the schema).

The Entity class

class Entity:
    table: str          # e.g. "ps_ac_chantier"
    pk: str             # e.g. "id_chantier"
    fields: tuple       # whitelist of columns accepted in INSERT/UPDATE
    canonical_client = "ac-hub"

• create(data): validate() → force client_id = canonical_client if the column is whitelisted → filter on fields → INSERT … (cols, date_add, date_upd) VALUES (…, NOW(), NOW()) RETURNING <pk>.
• update(pk, data): filter on fields, add date_upd=NOW().
• find/find_one/exists/delete. SQL escaping via _esc() — booleans → TRUE/FALSE, dict/list → json.dumps.

Subclasses override validate(data, mode) for business rules (blocking ValidationError + non-blocking warnings).

ChantierEntity.create_with_skeleton(...)

Creates atomically chantier + first travail + ≥1 task(s), to forbid orphan chantiers. Signature (excerpt):

ChantierEntity().create_with_skeleton(
    codename, title, *,
    first_travail: dict,                 # {codename, title, [priority, current_phase, ...]}
    first_taches: list[dict] | None,     # doctrine v3 — preferred
    first_tache: dict | None,            # singular — backward compat
    client_id=None, priority="P2", scope=None, current_focus=None, notes=None,
) -> {id_chantier, id_travail, id_tache, id_taches, codenames, warnings}

Blocking validations (raise ValidationError):

• codename kebab-case (4-64 chars) and unique.
• priority ∈ {P0,P1,P2,P3}.
• each task: non-empty title + assignee_codename, and the assignee must exist in ps_ac_agents (the view).
• multi-agent recruitment: if scope starts with tenant, require ≥2 distinct assignees.

Non-blocking warnings: estimated_tokens missing on a task.

Execution: a single BEGIN; … COMMIT; transaction — INSERT chantier (ps_ac_chantier), then travail (ps_ac_chantier_travail, looking up id_chantier via SELECT … WHERE codename=), then each task (sy_chantier_tache, looking up id_travail). ON_ERROR_STOP=1 guarantees rollback if an INSERT fails. recommended_model is auto-computed per task if absent.

TacheEntity — estimation, model, skills/tools

• create() override: if estimated_tokens is absent, it calls the token estimator; validates scope ∈ VALID_SCOPES; warns if the assignee is not in the chantier's production team (sy_chantier_agent, non-blocking safeguard).
• recommend_model_for(tokens, priority, fail_recurrent): heuristic — top-tier model if P0 or recurrent failure (≥2 iterations test_result='fail') or ≥8000 tokens; mid-tier model if ≥1500; otherwise a light model.
• attach_skill(id_tache, skill_name): lookup sy_skill.name → INSERT sy_tache_skill (ON CONFLICT DO NOTHING). If the skill is unknown → INSERT sy_skill_proposal (status pending) and return False.
• attach_tool(id_tache, tool_slug): lookup sy_chantier_tool.slug → INSERT sy_tache_tool. If unknown → sy_skill_proposal + False.

Status cascades (orchestrating Active Record)

The update() methods of TacheEntity and TravailEntity carry an automatic cascade:

task → done/cancelled (all tasks of the travail terminal, ≥1 done)
   └─ TacheEntity._cascade_to_travail
        ├─ QA team recruited (role IN ('validation','lead_validation')) ? → qa_run() applies the verdict
        └─ else → travail = 'done'
travail → done/cancelled (all travaux of the chantier terminal, ≥1 done)
   └─ TravailEntity._cascade_to_chantier
        ├─ discovery-only safeguard: all done in phase 'discovery' → auto-explode instead of promoting
        ├─ else → chantier = 'test' (preprod, awaiting review) — cascade at most 1 step
        └─ non-blocking warn if preprod_test_plan / ship_command missing

Additional step: a "travail-bis" with resolves_travail_id NOT NULL that goes done automatically closes the parent paused travail (paused→done, todo tasks→cancelled, append decisions_json).

Ground-truth query (counting the families)

docker exec ac_postgres psql -U <pg_user> -d ac_hub -c "\dt vaisseau_mere_ac.*"
docker exec ac_postgres psql -U <pg_user> -d ac_hub -tA -c \
  "SELECT pg_get_viewdef('vaisseau_mere_ac.ps_ac_agents'::regclass, true);"

i18n conventions and polymorphism

These conventions hold for cs_* (OSS) and are followed by the ps_ac_*/ps_* legacy.

_lang tables

Any text visible to a visitor lives in a sibling table <entity>_lang, never in the parent table.

• Suffix exactly _lang (not _translation, _i18n, _locale).
• Composite PK (id_<entity>, id_lang), no auto-increment; multi-shop → (id_<entity>, id_lang, id_shop), never a separate _shop_lang table.
• Strict separation: the parent carries FK/flags/dates/enums; the _lang carries title, description, meta_* and all user-visible text.

Live example ps_ac_faq_lang: (id_faq, id_lang, question varchar, answer text).

This convention has a direct impact on the adapter: _lang tables are excluded from the RETURNING id_<entity> heuristic because they have no simple PK. On the front end, strings go through t('domain.key', 'fallback') (reads ps_translation); a P0 rule forbids hardcoded i18n.

Polymorphism parent_type / parent_id

When a feature applies to several parent entities (FAQ for CMS and category and product), one polymorphic table — never one per parent.

-- live: ps_ac_faq
id_faq       integer PK
parent_type  varchar       -- 'cms' | 'category' | 'product'
parent_id    integer
position / active / date_add / date_upd
-- + ps_ac_faq_lang (id_faq, id_lang, question, answer)

Exception: a 1:1 extension of a native PrestaShop entity → the cs_<ps_entity>_extra pattern (PK = id_<ps_entity>, also FK to ps_<ps_entity>), with no polymorphism. Live examples: ps_ac_customer_extra, ps_ac_cms_extra, ps_ac_employee_extra. Pure N-N tables: cs_<a>_<b> in alphabetical order, with no _asso/_link suffix and no _lang.

No business JSON in a column

Content *_json columns are forbidden (payload_json, content_i18n, labels_json…). The only tolerance: ephemeral technical payloads (webhooks, logs, session state) documented by COMMENT ON COLUMN. In the cockpit, ps_ac_chantier_travail.context_json/decisions_json/discoveries_json and various sy_* use JSON for a technical audit trail (append-only), not for user-facing content — that is the tolerance, not the rule. A schema audit (cron) flags violations as P0.

Pickup pitfalls

1. ps_ac_agents is a view over sy_agents — read via the view, write via the physical sy_* table. Same for 11 other pairs (12 ps_ac_* → sy_* view-shims in total).
2. Container ac_postgres ≠ database ac_hub ≠ schema vaisseau_mere_ac.
3. The Nuxt adapter throws for any tenant not migrated to PG (mysql2 path removed). A tenant must be in the allow-list of migrated tenants.
4. The scope enum in the DB is broader than the written doctrine — the CHECK constraint is authoritative.
5. sy_chantier_tache kept its sequence/PK named cs_chantier_tache_* (rename residue).
6. Legacy MySQL SQL goes through convertMysqlToPg(); the uncovered functions (GROUP_CONCAT, DATE_FORMAT, ON DUPLICATE KEY UPDATE…) break silently and require a manual onPg() branch.