Conventions PostgreSQL — EgoDZB/claude-skills

Conventions PostgreSQL

Comment appliquer

Il existe deux modes. Décidez lequel s'applique avant d'écrire quoi que ce soit.

Mode A — vous choisissez les noms. L'utilisateur a demandé un schéma, une entité ou une migration sans dicter les noms. Appliquez directement les conventions ci-dessous.

Mode B — les noms existent déjà ou ont été dictés. L'utilisateur a spécifié des noms, ou du code, des migrations ou la base de données existants contiennent des noms qui s'écartent de ces conventions. Ne renommez jamais silencieusement. Signalez et demandez.

Format du rapport, dans la langue de l'utilisateur, avant d'effectuer des modifications :

Nommage :
À corriger absolument
- `"UserProfile"` (table) → `user_profiles` — le PascalCase entre guillemets doit être cité dans chaque requête pour toujours
- `created` timestamp DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') → `created_at timestamptz DEFAULT now()` — la valeur change lorsque le fuseau horaire de la session n'est pas UTC
Recommandé
- `users.user_email` → `users.email` — le nom de la table est déjà le contexte

Puis posez une question : tout appliquer, appliquer seulement les "À corriger absolument", ou garder tel quel. Attendez la réponse.

Règles pour les rapports :

• À corriger absolument = cause des bugs, casse les requêtes ou les outils : identifiants mixtes cités, mots réservés, noms de plus de 63 octets, timestamp sans fuseau horaire, mauvaises valeurs par défaut pour le temps, json/float/money pour des données nécessitant jsonb/numeric, index FK manquant, verrous en dehors d'une transaction.
• Recommandé = style et cohérence.
• Signalez uniquement les objets touchés par la tâche en cours. Si vous avez remarqué d'autres écarts ailleurs, ajoutez une ligne : "Également N écarts similaires dans — les lister ?"
• Objets dans des migrations appliquées ou une base de données active : un renommage est une modification cassante. Suggérez-le uniquement ; si approuvé, implémentez-le comme une nouvelle migration. Ne modifiez jamais une migration appliquée.
• Si la base de code utilise systématiquement une convention différente, celle-ci prévaut. Mentionnez la différence une fois, puis suivez la convention du projet sans signaler chaque occurrence.
• Les noms produits par la convention de nommage de l'ORM (par exemple EFCore.NamingConventions) sont acceptés tels quels.
• Une fois que l'utilisateur confirme un nom non conforme, utilisez-le et ne le soulevez plus dans cette session.

Règles générales

• snake_case, minuscules, jamais d'identifiants cités.
• Maximum 63 octets par identifiant ; les noms plus longs sont tronqués silencieusement. Raccourcissez de manière significative, pas en coupant.
• Tables au pluriel : users, orders, order_items.
• Colonnes au singulier, sans préfixe de table : users.email, pas users.user_email.
• Mots complets sauf abréviations courantes : id, url, ip, sku.
• N'utilisez jamais de mots réservés ou ambigus comme noms : user, order, group, table, limit, offset, desc, end, date, time, timestamp.
• Pas de préfixes de type ou d'objet : pas de tbl_users, str_name.
• Les booléens sont positifs : is_deleted, jamais is_not_deleted.

Colonnes standard

Objectif	Nom	Type
Clé primaire	id	bigint generated always as identity ou uuid v7
Créé / mis à jour	created_at, updated_at	timestamptz not null default now()
Suppression logique	deleted_at	timestamptz null
Audit	created_by, updated_by	FK vers users.id
Indicateurs	is_active, is_verified, has_children, can_edit	boolean not null default false
Statut	status	text + check, enum, ou table de référence
Date uniquement	birth_date, due_on	date
Validité	expires_at, valid_from, valid_to	timestamptz
Monnaie	amount, unit_price, total_amount + currency_code	numeric(19,4) + char(3), ou amount_minor bigint
Compteurs	items_count, retry_count	integer / bigint
Ordre	sort_order	integer
Clé métier	code, slug, external_id	text + unique
Tenant	tenant_id	FK
Concurrence	version ou système xmin	integer / xid
Données libres	metadata, payload, settings	jsonb

Les suffixes ont une signification : _at instant, _on/_date date calendaire, _id référence, _count compteur, _url, _path, _hash.

Clés et contraintes

Objet	Modèle	Exemple
Clé primaire	pk_<table>	pk_orders
Clé étrangère	fk_<table>_<ref_table>_<column>	fk_orders_customers_customer_id
Contrainte unique	uq_<table>_<cols>	uq_users_email
Clé alternative (EF Core)	ak_<table>_<cols>	ak_products_sku
Vérification	ck_<table>_<rule>	ck_orders_amount_positive
Exclusion	ex_<table>_<cols>	ex_bookings_room_id_period
Index	ix_<table>_<cols>	ix_orders_customer_id_created_at
Index unique	ux_<table>_<cols>	ux_users_email
Index partiel	ix_<table>_<cols>_<condition>	ix_users_email_not_deleted
Index par méthode	ix_<table>_<col>_<method>	ix_products_tags_gin
Déclencheur	trg_<table>_<action>	trg_orders_set_updated_at
Fonction de déclencheur	verbe + objet	set_updated_at()
Séquence	Par défaut PostgreSQL	orders_id_seq

• Colonne FK : <table référencée, singulier>_id, ou un nom de rôle lorsque le rôle compte : author_id, sender_id, recipient_id, parent_id.
• Le modèle FK inclut la colonne, ainsi plusieurs FK vers une même table restent uniques : fk_messages_users_sender_id, fk_messages_users_recipient_id.
• PostgreSQL n'indexe pas les colonnes FK. Chaque FK nécessite un index sauf si un index composite commence déjà par celle-ci. EF Core les crée par convention ; Drizzle, Prisma, TypeORM, Knex et sqlc ne le font pas.

Relations

Type	Forme	Exemple
Un-à-plusieurs	FK sur l'enfant	order_items.order_id → orders.id
Un-à-un	FK + unique, ou PK = FK	user_profiles.user_id
Plusieurs-à-plusieurs	Jonction <a>_<b>, PK composite	user_roles (user_id, role_id), pk_user_roles
Plusieurs-à-plusieurs avec attributs	Nom d'entité significatif	memberships, enrollments
Auto-référence	parent_id ou rôle	categories.parent_id, employees.manager_id

Les paires polymorphes (commentable_type + commentable_id) ne peuvent pas avoir de FK. Préférez des colonnes FK nullables séparées avec une vérification qu'exactement une est définie, ou des tables séparées.

Schémas, vues, fonctions, types

Objet	Modèle	Exemple
Schéma	domaine ou module	auth, billing, catalog, audit
Vue	v_<name>	v_active_users
Vue matérialisée	mv_<name>	mv_daily_sales
Fonction / procédure	verbe + objet	get_user_balance, calculate_order_total
Type enum	<entité>_<attribut>	order_status
Valeurs enum	snake_case minuscules	pending, paid, refunded
Rôle base de données	<app>_<accès>	shop_app_rw, shop_app_ro, shop_migrator

Les valeurs enum ne peuvent pas être supprimées sans recréer le type. Pour les ensembles qui changent, préférez text + check ou une table de référence.

Types et valeurs par défaut

• timestamptz pour les instants, défaut now(). CURRENT_TIMESTAMP AT TIME ZONE 'UTC' sur une colonne timestamptz est un bug.
• text au lieu de varchar(n) sauf si la longueur est une règle métier ; préférez check (char_length(col) <= n) pour cela.
• jsonb, jamais json.
• numeric pour l'argent ; jamais real, double precision ou money.
• Les nouvelles colonnes d'identité utilisent generated always as identity, pas serial/bigserial.
• Les clés primaires UUID utilisent v7 : PostgreSQL 18 uuidv7(), .NET 9 Guid.CreateVersion7(), package Node uuid v7(), Go github.com/google/uuid uuid.NewV7(). Les clés v4 aléatoires fragmentent les index B-tree.
• gen_random_uuid() est intégré depuis PostgreSQL 13 ; uuid-ossp et pgcrypto ne sont pas nécessaires pour cela.
• citext ou un index unique lower(email) pour l'unicité insensible à la casse.

Concurrence et motifs de requêtes

• Concurrence optimiste, EF Core + Npgsql : une propriété uint configurée avec IsRowVersion() correspond à xmin.
• Concurrence optimiste, Node et Go : version integer not null default 0, mis à jour avec where id = $1 and version = $2 et version = version + 1 ; zéro lignes affectées signifie un conflit. Dans sqlc, utilisez :execrows pour obtenir le nombre de lignes affectées.
• select ... for update et for update skip locked uniquement dans une transaction explicite ; en autocommit, le verrou est libéré immédiatement.
• Verrous consultatifs : pg_try_advisory_xact_lock dans une transaction. pg_advisory_lock au niveau session uniquement sur une connexion dédiée maintenue ouverte jusqu'au déverrouillage ; avec le pooling, le verrouillage et le déverrouillage peuvent atterrir sur des connexions différentes.
• Les listes grandes ou infinies utilisent la pagination par clé : where (created_at, id) < ($1, $2) order by created_at desc, id desc limit $3. offset uniquement pour les petites listes limitées.
• Les privilèges pour les tables futures nécessitent alter default privileges, pas seulement grant ... on all tables.

Configuration ORM pour snake_case

Pile	Configuration
EF Core + Npgsql	EFCore.NamingConventions, UseSnakeCaseNamingConvention()
Drizzle	casing: 'snake_case'
Prisma	@map sur les champs, @@map sur les modèles
TypeORM	SnakeNamingStrategy depuis typeorm-naming-strategies
Knex / Objection	knexSnakeCaseMappers()
Go + sqlc	aucun ; les noms Go sont générés automatiquement depuis snake_case

Si un projet utilise un ORM sans snake_case configuré, signalez-le comme À corriger absolument avant la première migration ; après l'existence de migrations, signalez-le comme Recommandé avec le coût de la migration.

Go + sqlc

sqlc est axé SQL d'abord : le schéma provient des fichiers de migration, donc chaque règle ci-dessus s'applique directement à eux.

• Pointez schema dans sqlc.yaml vers le répertoire des migrations (les formats goose, golang-migrate, dbmate et atlas sont supportés ; les migrations descendantes sont ignorées). Ne conservez jamais un fichier de schéma manuscrit séparé à côté des migrations.
• Les tables au pluriel en snake_case donnent des structures Go au singulier : order_items → OrderItem. L'enum order_status → type OrderStatus avec constantes OrderStatusPending.
• Utilisez sql_package: pgx/v5. Remplacez uuid → github.com/google/uuid.UUID et, si souhaité, timestamptz → time.Time au lieu des wrappers pgtype.
• Corrigez les initialismes avec rename (url: URL, ip: IP) au lieu de renommer les colonnes.
• Les noms de requêtes sont en PascalCase verbe + objet, avec le type de résultat :

Objectif	Exemple
Une ligne	-- name: GetUserByEmail :one
Plusieurs lignes	-- name: ListOrdersByCustomer :many
Insertion	-- name: CreateOrder :one + returning *
Mise à jour	-- name: UpdateOrderStatus :execrows
Suppression	-- name: DeleteSession :exec
Chargement en masse	-- name: CreateOrderItems :copyfrom

• Nommez les paramètres avec @name ou sqlc.arg(name), et sqlc.narg(name) pour ceux nullables ; le $1 positionnel produit des champs Go de style Column1.
• Utilisez sqlc.embed(table) pour les jointures au lieu de sélectionner des colonnes avec des alias ad-hoc.
• N'utilisez jamais select * dans les requêtes qui doivent survivre aux modifications de schéma, sauf returning * sur les écritures.