postgres (engine specialist)

The Postgres-specific layer. Cross-engine conventions live in database-conventions - load that hub first and do not restate it here. RLS basics and least-privilege logins live in data-security. The .NET/EF Core side lives in dotnet-data-access. This file is only what changes because the engine is Postgres.

Schema and types

• Keep every identifier lowercase snake_case and unquoted. Postgres folds unquoted identifiers to lowercase; a quoted mixed-case name ("firstName") must be quoted forever and breaks ORMs and tools. Inheriting mixed-case? Wrap a snake_case view as a compatibility layer.
• ADD CONSTRAINT IF NOT EXISTS does not exist in Postgres - it is a syntax error. Guard idempotent constraint DDL with a pg_constraint check:

do $$ begin
  if not exists (select 1 from pg_constraint
                 where conname = 'profiles_owner_unique' and conrelid = 'public.profiles'::regclass)
  then alter table public.profiles add constraint profiles_owner_unique unique (owner_id);
  end if;
end $$;