Second Brain

Base de dados — PostgreSQL

D2U.NET · Implementacao · Arquitetura · Migracao-Legado

Decisão jun/2026: PostgreSQL 16 + schema d2u normalizado (4NF).
Repo: Docs_New/database/postgresql-schema-design.md

Resumo
Motor PostgreSQL 16
Schema d2u
Tabelas core 16 (multi-tenant)
Tabelas fase 2+ payments, audit, rules, templates, contractors, xml_transcript*, …
Legado MySQL 43 tabelas — ETL via D2U.Tools.Etl
ORM EF Core 9 + Npgsql
Migrations PostgresInitialCreate + fase 2 + Onda9ContentAndContractors + MraOrganizations (2026-06-12)

Porquê mudar

  • user_client legado: 75 colunas → clients + client_billing_preferences

  • 4 tabelas ficheiros → job_files + file_kind

  • BLOB policies Java → company_policies + JSONB

  • job_assignments — 4NF para atribuições

  • user_mra_organization — MRA ↔ organização (legado user_mra_organization; migration MraOrganizations)

Compatibilidade externa

APIs mobile, DWR, XML-RPC, webhooks e Outside inalteradas — enums ordinais preservados na camada domínio.

Local (Docker)

cd C:\Projetos\D2U.NET\D2U.NET\d2u.net
docker compose up -d
dotnet ef database update --project src/D2U.Persistence --startup-project src/D2U.Web
Host localhost:5432
DB d2u_dev
User/Pass d2u / d2u

ETL MySQL → PostgreSQL ✅ (código)

Item Detalhe
Tool tools/D2U.Tools.Etl/
CLI migrate, validate, copy-files
Core 15 migradores (companies, users, clients, jobs, …)
Fase 2 5 migradores opcionais (templates, contractors, specializations, …)
Script tools/etl/mysql-to-postgres.ps1
Guia Docs_New/implementation/cutover-etl.md

Pendente operacional: dry-run staging + validar mapeamento colunas fase 2.

Docs repo

Doc Conteúdo
postgresql-schema-design.md Design 4NF completo + ER
strategy-comparison.md Decisão vs MySQL/SQL Server
schema-mapping.md Legado → PostgreSQL
migrations-plan.md EF migrations
cutover-etl.md Janela de virada