Migrar para PostgreSQL respeitando Clean Architecture

[wallanpsantos]

Título: Migrate from MongoDB to PostgreSQL for financial domain reliability
Labels: critical, migration, clean-architecture, database
Prioridade: CRÍTICA - Base para tudo

Problema Atual (Ponto 1 do Feedback):

• MongoDB inadequado para domínio financeiro
• Falta de ACID robusto, constraints e auditoria
• Maior complexidade para garantir consistência

Solução Proposta:

-- PostgreSQL Schema
CREATE TABLE wallets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id VARCHAR(255) NOT NULL UNIQUE,
    balance DECIMAL(19,4) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    version BIGINT NOT NULL DEFAULT 1,
    
    CONSTRAINT positive_balance CHECK (balance >= 0),
    CONSTRAINT valid_currency CHECK (currency ~ '^[A-Z]{3}$')
);

CREATE TABLE wallet_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wallet_id UUID NOT NULL REFERENCES wallets(id),
    transaction_type VARCHAR(20) NOT NULL,
    amount DECIMAL(19,4) NOT NULL,
    currency CHAR(3) NOT NULL,
    balance_after DECIMAL(19,4) NOT NULL,
    description TEXT,
    timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
    correlation_id UUID,
    
    CONSTRAINT positive_amount CHECK (amount > 0)
);

-- Índices para performance
CREATE INDEX idx_wallets_user_id ON wallets(user_id);
CREATE INDEX idx_transactions_wallet_timestamp ON wallet_transactions(wallet_id, timestamp);
CREATE INDEX idx_transactions_correlation ON wallet_transactions(correlation_id);

Tarefas:

• Configurar PostgreSQL e dependências

  <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
  </dependency>

• Criar entidades JPA no dataprovider

  // wallet-dataprovider
  @Entity
  @Table(name = "wallets")
  public class WalletEntity {
      @Id
      private String id;
      
      @Column(name = "user_id", unique = true, nullable = false)
      private String userId;
      
      @Column(precision = 19, scale = 4)
      private BigDecimal balance;
      
      @Version
      private Long version; // Para optimistic locking
  }

• Implementar Repository JPA (COM @transactional)

  // wallet-dataprovider - AQUI fica @Transactional
  @Repository
  @Transactional
  public class PostgreSQLWalletRepositoryImpl implements WalletRepository {
      
      @Transactional
      @Override
      public Wallet save(Wallet wallet) {
          // Lógica transacional aqui
      }
      
      @Transactional
      @Override
      public void saveWalletAndTransaction(Wallet wallet, WalletTransaction transaction) {
          // Operação atômica - salvar wallet + transação na mesma TX
      }
  }

• Criar scripts de migração

• Configurar Flyway/Liquibase

• Testes de integração com PostgreSQL

Arquivos a criar:

wallet-dataprovider/
├── postgresql/
│   ├── entity/
│   │   ├── WalletEntity.java
│   │   └── WalletTransactionEntity.java
│   ├── repository/
│   │   ├── WalletJpaRepository.java
│   │   └── PostgreSQLWalletRepositoryImpl.java (COM @Transactional)
│   └── mapper/
│       └── WalletEntityMapper.java
├── migration/
│   └── V1__create_wallet_tables.sql