Modelagem

O schema vive em PostgreSQL 18+ (a função uuidv7() é nativa a partir dele), versionado por Flyway (V1..VN, congeladas por checksum — nunca editar uma aplicada, sempre uma nova). Duas famílias de tabela, por prefixo, que é contrato com o PowerSync:

bi_* — fatos, dimensões e configuração sincronizados para os clientes móveis via PowerSync (exigem GRANT SELECT ao powersync_role, decisão 0013). PK id UUID DEFAULT uuidv7(); a chave natural vira UNIQUE (uq_*).
xls_* — controle e auditoria locais ao backend, nunca sincronizados. PK BIGSERIAL (ou TEXT no lote). Não precisam de GRANT.

Regras para tabela nova¶

PK e chave natural. Toda bi_* usa id UUID NOT NULL DEFAULT uuidv7(): o PowerSync exige PK de coluna única TEXT/UUID, e BIGSERIAL ou PK composta não replicam (decisão 0008). A chave natural (código do ERP, CNPJ, ou composta como NF+produto+cliente+filial) vira CONSTRAINT uq_<tabela>_<sufixo> UNIQUE (…), e as FKs apontam para ela. Tabela sem chave natural de linha não faz upsert — é replace-por-período ou full-replace (bi_compra, bi_cota_petrobras). A única exceção à PK UUID é bi_configuracao, com id TEXT como chave semântica da KV.

GRANT ao powersync_role. O ALTER DEFAULT PRIVILEGES da V13 já dá SELECT a toda tabela nova em public, mas toda migration que cria uma bi_* repete o GRANT como cinto, logo após o CREATE TABLE (no-op se o role não existir ou se o default já cobriu):

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'powersync_role') THEN
        EXECUTE 'GRANT SELECT ON bi_nova_coisa TO powersync_role';
    END IF;
END $$;

Sem o GRANT, o PowerSync para com permission denied for table (42501), o cliente trava no sync e o nuke morre em SNAPSHOT_PRE. A conferência antes de redeployar o PowerSync (\dp <tabela> mostra powersync_role=r) e o fix manual em produção estão na decisão 0013. O GRANT não basta para o dado chegar ao cliente: a tabela ainda entra na publication e no sync_rules.yaml do repo do PowerSync (runbook do reset).

Coluna nova de conteúdo entra no upsert diff-aware — ver Persistência em lote no guia do código.

Tabelas e relacionamentos¶

erDiagram
    bi_cliente  ||--o{ bi_movimento : "cod_cliente"
    bi_vendedor ||--o{ bi_movimento : "cod_vendedor"
    bi_filial   ||--o{ bi_movimento : "cod_filial"
    bi_produto  ||--o{ bi_movimento : "cod_produto"
    bi_vendedor ||--o{ bi_meta : "cod_vendedor"
    bi_fornecedor ||--o{ bi_compra : "cnpj_fornecedor"
    xls_conjunto_lote ||--o{ xls_processamento : "conjunto_id (lógico)"

    bi_movimento {
        uuid id PK
        integer nf
        text cod_produto FK
        text cod_cliente FK
        text cod_filial FK
        text cod_vendedor FK
        text segmento
    }
    bi_cliente {
        uuid id PK
        text cod_cliente UK
    }
    bi_vendedor {
        uuid id PK
        text cod_vendedor UK
    }
    bi_filial {
        uuid id PK
        text cod_filial UK
    }
    bi_produto {
        uuid id PK
        text cod_produto UK
        text segmento
    }
    bi_meta {
        uuid id PK
        text cod_vendedor FK
        date data
    }
    bi_posto {
        uuid id PK
        text cnpj UK
    }
    bi_trr {
        uuid id PK
        text cnpj UK
    }
    bi_custo_inventario {
        uuid id PK
        date data UK
    }
    bi_configuracao {
        text id PK
    }
    bi_fornecedor {
        uuid id PK
        text cnpj UK
    }
    bi_compra {
        uuid id PK
        text cnpj_fornecedor FK
        date data_nf
    }
    bi_cota_petrobras {
        uuid id PK
        date competencia
        text polo
        text produto
    }
    xls_processamento {
        bigint id PK
        varchar checksum_sha256 UK
        varchar conjunto_id
    }
    xls_conjunto_lote {
        varchar conjunto_id PK
    }
    xls_nuke_replication {
        bigint id PK
    }

FKs físicas só entre os bi_*. bi_movimento referencia as dimensões (bi_cliente/bi_vendedor/bi_filial/bi_produto) por suas UNIQUE de código natural, e bi_meta referencia bi_vendedor — Postgres aceita FK apontando para coluna UNIQUE. Não há FK ligando xls_processamento aos fatos: um upload mescla os bi_* do seu período (UPSERT diff-aware), não os "possui". A ligação xls_processamento.conjunto_id → xls_conjunto_lote é lógica (sem constraint, para ser retrocompatível). bi_posto, bi_trr, bi_custo_inventario, bi_configuracao e bi_cota_petrobras são independentes; as xls_* são controle/auditoria.

Fluxo de dados¶

flowchart TD
    erp[ERP / job OnPetro] -->|.zip multipart| up["POST /api/xls/processar"]
    up -->|binário| store[("Garage / arquivo_bytes")]
    up -->|registra upload| ctrl[("xls_processamento")]
    up --> det[ArquivoDetector]
    det -->|1 de 9 tipos| proc["{Tipo}Processor"]
    proc --> upsert["BulkUpserter diff-aware + DELETE seletivo"]
    proc --> comprasp["ComprasProcessor: upsert fornecedor + DELETE período + INSERT"]
    proc --> cotap["CotaPetrobrasProcessor: full-replace (deletarTudo + INSERT)"]
    upsert --> mov[("bi_movimento")]
    upsert --> dims[("bi_cliente / vendedor / filial / produto")]
    upsert --> outras[("bi_meta / bi_posto / bi_trr / bi_custo_inventario")]
    comprasp --> compras[("bi_fornecedor / bi_compra")]
    cotap --> cota[("bi_cota_petrobras")]
    upsert -->|linhas_efetivas / linhas_removidas| ctrl
    mov -->|PowerSync| cli[Clientes móveis]
    dims -->|PowerSync| cli
    outras -->|PowerSync| cli
    compras -->|PowerSync| cli
    cfg[("bi_configuracao")] -->|PowerSync| cli

Detalhe de cada tabela¶

Dicionário por tabela (coluna, tipo, chave, nulo?, desde, nota). A chave natural, os índices e os CHECK vêm em bullets abaixo de cada tabela. Desde vazio = coluna original (V1 para os bi_* de domínio, V2 para xls_processamento).

`bi_movimento` — fato de vendas (RELATORIO18 / MARGEM_*) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`nf`	integer		NOT NULL		parte da chave natural
`data`	date		NOT NULL		data da venda
`uf`	text				UF de destino da venda
`municipio`	text				município de destino
`bandeira`	text
`categoria`	text
`cif`	char(1)				CIF/FOB
`quantidade`	decimal(15,3)				litros
`preco`	decimal(15,2)
`taxa_adm`	decimal(10,2)
`frete`	decimal(10,2)
`margem`	decimal(10,2)				complementada por MARGEM_*
`prazo`	integer				dias
`cod_cliente`	text	FK			→ `bi_cliente` · parte da chave natural
`cod_vendedor`	text	FK			→ `bi_vendedor`
`cod_filial`	text	FK			→ `bi_filial` · parte da chave natural
`cod_produto`	text	FK			→ `bi_produto` · parte da chave natural
`segmento`	text			V20	denormalizado de `bi_produto.segmento` (role-gating PowerSync)

Chave natural (UK) uq_bi_movimento_natural: nf, cod_produto, cod_cliente, cod_filial. É a chave do UPSERT diff-aware.
FKs (recriadas em V7 apontando para as UNIQUE de código das dimensões): cod_cliente, cod_vendedor, cod_filial, cod_produto.
Índices (V1): data, cod_filial, cod_vendedor, cod_produto, cod_cliente; segmento (V20 — a sync rule PowerSync filtra por ele).

`bi_cliente` — dimensão cliente · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`cod_cliente`	text	UK	NOT NULL		`uq_bi_cliente_cod`
`cnpj`	text
`razao_social`	text
`municipio`	text
`uf`	text
`classe`	text				classe comercial

`bi_vendedor` — dimensão vendedor · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`cod_vendedor`	text	UK	NOT NULL		`uq_bi_vendedor_cod`
`nome`	text

`bi_filial` — dimensão filial · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`cod_filial`	text	UK	NOT NULL		`uq_bi_filial_cod`
`municipio`	text				da filial (cadastro manual, `docs/privado/seed-filiais.sql`)
`uf`	text				idem

Os processadores só garantem o cod_filial (FilialRepository.ensureExistsBatch, INSERT … ON CONFLICT DO NOTHING, necessário pela FK de bi_movimento) sem tocar em municipio/uf: os relatórios XLSX só carregam o código da filial. As colunas UF/Município desses relatórios são do destino da venda e vão para bi_movimento.

`bi_produto` — dimensão produto · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`cod_produto`	text	UK	NOT NULL		`uq_bi_produto_cod`
`nome`	text
`segmento`	text			V20	`combustivel`/`lubrificante`/`NULL`, derivado do `nome`

segmento — classificado de nome por Segmento.doNome (dono único da regra) na pipeline, inline e diff-aware (entra no WHERE … IS DISTINCT FROM dos upserts); as linhas anteriores à coluna vieram do backfill da V20. Pré-requisito do role-gating PowerSync: a sync rule corta bi_movimento por segmento e não faz JOIN, por isso o valor é denormalizado na linha. Regra: combustivel ← ^ONU \d+; lubrificante ← MAXON OIL ou ON LUB; senão NULL (Arla/Ureia, fora do COMERCIAL). Distinto do filtro "só combustível" do ComprasProcessor (startsWith("ONU"), domínio compra/fornecedor) — regras separadas de propósito, não unificar. Decisão 0023.

`bi_meta` — metas por vendedor/período (META / META_FERNANDO) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`data`	date		NOT NULL		parte da chave natural
`litros`	decimal(15,3)
`margem`	decimal(10,2)
`cod_vendedor`	text	FK	NOT NULL		→ `bi_vendedor` · parte da chave natural

Chave natural (UK) uq_bi_meta_natural: cod_vendedor, data.
Índices (V1): data, cod_vendedor.

`bi_posto` — cadastro de postos (POSTOS) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`cnpj`	text	UK	NOT NULL		`uq_bi_posto_cnpj`
`razao_social`	text
`municipio`	text
`uf`	text
`distribuidor`	text				vinculação a distribuidor
`data`	date

`bi_trr` — cadastro de TRRs (TRR) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`cnpj`	text	UK	NOT NULL		`uq_bi_trr_cnpj`
`razao_social`	text
`municipio`	text
`uf`	text
`data`	date

`bi_custo_inventario` — custo/inventário (MARGEM_CONSOLIDADA) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V7	`DEFAULT uuidv7()`
`data`	date	UK	NOT NULL		`uq_bi_custo_inventario_data`
`valor`	decimal(15,2)
`taxa_aplicacao`	decimal(15,2)
`total_juros`	decimal(15,6)
`total_venda_simulada`	decimal(15,2)
`total`	decimal(15,2)

`bi_configuracao` — KV global sincronizada · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	text	PK	NOT NULL	V12	a chave É o id (semântica)
`valor`	text		NOT NULL	V12
`tipo`	varchar(16)		NOT NULL	V12	CHECK (ver abaixo)
`descricao`	text			V12
`sistema`	boolean		NOT NULL	V12	default `FALSE`
`atualizado_em`	timestamptz		NOT NULL	V12	default `NOW()`

CHECK chk_bi_configuracao_tipo: tipo ∈ {STRING, INT, BOOL, TIMESTAMP, JSON}.
Seed (V12): ultimo_nuke (TIMESTAMP, sistema=TRUE — sinal de reset, contrato com o cliente Flutter) e slow_query_timeout_ms (INT, 10000, editável).
id é a própria chave semântica (não surrogate; renomear = delete+insert).
sistema=TRUE = só o backend escreve: a UI mostra a linha read-only e o POST responde 403. Chave criada pela tela nasce sistema=FALSE; chave de sistema só nasce por migration. Consumo e serialização na etapa 07.

`bi_fornecedor` — dimensão fornecedor de compra (COMPRAS) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V16	`DEFAULT uuidv7()`
`cnpj`	text	UK	NOT NULL	V16	chave natural
`razao_social`	text			V16
`uf`	text			V16

Chave natural uq_bi_fornecedor_cnpj: cnpj UNIQUE. Upsert diff-aware (ON CONFLICT (cnpj) DO UPDATE … IS DISTINCT FROM).
Populada só com fornecedores de linhas combustível (ONU); fornecedores de aditivo/envelope, filtrados fora, não entram (seriam dimensão órfã).

`bi_compra` — fato item-de-NF de compra combustível (COMPRAS) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V16	`DEFAULT uuidv7()`
`data_nf`	date			V16	data da NF (define o período)
`numero_nota`	text			V16	parte antes do "/" de `Nº Nota`
`serie`	text			V16	parte após o "/" (ex. `802962/1` → série `1`)
`cnpj_fornecedor`	text	FK		V16	→ `bi_fornecedor(cnpj)`
`produto`	text			V16	descrição ONU normalizada (trim + espaços)
`codigo_onu`	text			V16	ex. `1202` extraído de `"ONU 1202, …"`
`uf`	text			V16
`municipio`	text			V16
`quantidade`	numeric			V16	cru (sem arredondar)
`valor_bruto`	numeric			V16	cru; custo médio é derivado no cliente

Sem chave natural de linha (PK surrogate puro): a candidata (cnpj, numero_nota, produto) colide em dados reais (NF 802962/1, 803823/1, 806243/1 aparecem 2×) e o arquivo não traz discriminador de linha. Decisão 0017.
Idempotência/correção = replace-por-período: numa transação, DELETE FROM bi_compra WHERE data_nf BETWEEN <min> AND <max> (intervalo do arquivo) + INSERT de todas as linhas. Sem chave natural não há upsert diff-aware; o skip do reenvio idêntico vem do checksum SHA-256 do xls_processamento. Premissa: um arquivo é o conjunto completo do seu período (se um mês vier partido em 2, o 2º apaga o 1º).
Índices: (produto, data_nf) e (cnpj_fornecedor).

`bi_cota_petrobras` — cota mensal de volume por polo/produto (COTA_PETROBRAS) · sincronizada¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	uuid	PK	NOT NULL	V19	`DEFAULT uuidv7()`
`competencia`	date		NOT NULL	V19	1º dia do mês (`YYYY-MM-01`)
`polo`	text		NOT NULL	V19	polo de distribuição (forward-fill da célula mesclada)
`produto`	text		NOT NULL	V19	canônico: `Diesel S10` / `Diesel S500` / `Gasolina` / `Diesel R5`
`volume_m3`	numeric		NOT NULL	V19	volume da cota em m³

Sem chave natural de linha (PK surrogate puro): a planilha é a matriz inteira (uma aba por ano); (competencia, polo, produto) poderia repetir sob correções.
Idempotência/correção = full-replace: DELETE FROM bi_cota_petrobras + INSERT da matriz inteira, numa transação. Guard de vazio: arquivo sem produto canônico com número não apaga (nunca há caso legítimo de esvaziar via upload). Premissa: ≤1 planilha de cota por lote (o DELETE full-table não é seguro sob concorrência). Decisão 0019.
Parsing (CotaPetrobrasProcessor): itera abas cujo nome casa ^\d{4}$ (ignora Planilha1), mapeia os 12 meses canônicos por header normalizado (descarta JANEIRO.2024/TOTAL PRODUTO/ANO/ruído) e emite linha só quando o produto é canônico (Diesel S10, Diesel S500, Gasolina ou Diesel R5) e a célula é número. Produto não-canônico com valor → LOG.warn (sinal de produto novo/renomeado), não silêncio.
Índices: (competencia) e (polo, produto).

`xls_processamento` — controle do pipeline · local¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	bigint	PK	NOT NULL		`BIGSERIAL`
`recebido_em`	timestamp		NOT NULL		default `NOW()`
`checksum_sha256`	varchar(64)	UK	NOT NULL		SHA-256 do binário (dedup)
`nome_arquivo`	varchar(255)
`arquivo_bytes`	bytea				object storage (etapa 04)
`tipo_arquivo`	varchar(20)				tipo detectado (`ArquivoDetector`)
`status`	varchar(20)		NOT NULL		default `PENDENTE`
`periodo_inicial`	date				vem do conteúdo
`periodo_final`	date				vem do conteúdo
`iniciado_em`	timestamp
`concluido_em`	timestamp
`tempo_ms`	bigint
`erro_mensagem`	text
`registros_processados`	integer				só a entidade principal do arquivo
`log_arquivo`	varchar(500)
`conjunto_id`	varchar(64)			V4	lote (lógico → `xls_conjunto_lote`)
`objeto_bucket`	varchar(63)			V11	object storage
`objeto_chave`	varchar(512)			V11	object storage
`objeto_tamanho_bytes`	bigint			V11	object storage
`linhas_enviadas`	integer			V15	linhas pedidas, somando TODAS as tabelas tocadas (etapa 02)
`linhas_efetivas`	integer			V9	INSERT/UPDATE que escreveram, mesmo escopo de `linhas_enviadas` (etapa 02)
`linhas_removidas`	integer			V9	DELETE seletivo (etapa 02)
`enviado_por_id`	text			V17	quem enviou pela página `/xls/importar` (id do usuário do bi-comercial)
`enviado_por_nome`	text			V17	nome desse usuário
`fk_tentativas`	integer		NOT NULL	V18	default `0`; falhas por violação de FK (`23503`) de um item de lote — retry de FK transiente (cap. 5)

UNIQUE uq_xls_processamento_checksum (checksum_sha256) — dedup de upload.
Índices (V3): status, recebido_em DESC; parcial conjunto_id (V4).
Object storage (V11): objeto_* + arquivo_bytes (etapa 04).
Métricas de idempotência (V9 + V15): linhas_enviadas/linhas_efetivas/ linhas_removidas — sem backfill; registros pré-deploy ficam NULL e a view exibe "—" (etapa 02). linhas_enviadas e linhas_efetivas estão no mesmo escopo (todas as tabelas tocadas), e é a diferença entre as duas que dá as linhas inalteradas — as que o WHERE ... IS DISTINCT FROM filtrou, sem gerar WAL nem checkpoint PowerSync. Não derivar isso de registros_processados: essa coluna conta só a entidade principal do arquivo (ex.: os movimentos do RELATORIO18), e misturar os escopos produzia "inalteradas" negativa.
status ∈ {PENDENTE, PROCESSANDO, SUCESSO, ERRO, IGNORADO} — IGNORADO quando o tipo foi detectado mas o período é ≤ a data mínima (10/08/2025): o processador pula (RELATORIO18/MARGEM_), sem 4xx. Tipo não* reconhecido é 400 na recepção (não persiste).

`xls_conjunto_lote` — coordenação de lote · local¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`conjunto_id`	varchar(64)	PK	NOT NULL	V4	`pk_xls_conjunto_lote`
`total`	int		NOT NULL	V4	nº de arquivos do lote
`duplicados`	int		NOT NULL	V4	default `0`
`criado_em`	timestamp		NOT NULL	V4	default `NOW()`
`notificado_em`	timestamp			V4	quando o resumo foi enviado
`nao_processados`	int		NOT NULL	V14	default `0` — entradas rejeitadas/em andamento

Fecha e notifica quando (duplicados + finalizados + nao_processados) >= total.
nao_processados (V14) contempla o lote-por-ZIP: entradas que não geram processamento nem duplicata-sucesso, para o resumo do Telegram sempre sair.

`xls_nuke_replication` — auditoria do nuke · local¶

Coluna	Tipo	Chave	Nulo?	Desde	Nota
`id`	bigint	PK	NOT NULL	V8	`BIGSERIAL`
`iniciado_em`	timestamp		NOT NULL	V8	default `NOW()`
`concluido_em`	timestamp			V8
`status`	varchar(20)		NOT NULL	V8	default `PENDENTE`; CHECK (ver abaixo)
`motivo`	text			V8
`iniciado_por`	varchar(100)			V8
`tempo_ms`	bigint			V8
`erro_mensagem`	text			V8
`step_atual`	varchar(30)			V8
`steps_completados`	jsonb		NOT NULL	V8	default `[]`
`detalhes`	jsonb			V8
`atividade_atual`	text			V10	escrita ao vivo p/ polling da UI

CHECK chk_nuke_status: status ∈ {PENDENTE, EM_ANDAMENTO, SUCESSO, ERRO}.
Unique parcial uq_xls_nuke_replication_em_andamento sobre ((1)) WHERE status = 'EM_ANDAMENTO': no máximo um nuke em andamento (2º disparo → 409).
Índice iniciado_em DESC.

Modelagem

Regras para tabela nova¶

Tabelas e relacionamentos¶

Fluxo de dados¶

Detalhe de cada tabela¶

bi_movimento — fato de vendas (RELATORIO18 / MARGEM_*) · sincronizada¶

bi_cliente — dimensão cliente · sincronizada¶

bi_vendedor — dimensão vendedor · sincronizada¶

bi_filial — dimensão filial · sincronizada¶

bi_produto — dimensão produto · sincronizada¶

bi_meta — metas por vendedor/período (META / META_FERNANDO) · sincronizada¶

bi_posto — cadastro de postos (POSTOS) · sincronizada¶

bi_trr — cadastro de TRRs (TRR) · sincronizada¶

bi_custo_inventario — custo/inventário (MARGEM_CONSOLIDADA) · sincronizada¶

bi_configuracao — KV global sincronizada · sincronizada¶

bi_fornecedor — dimensão fornecedor de compra (COMPRAS) · sincronizada¶

bi_compra — fato item-de-NF de compra combustível (COMPRAS) · sincronizada¶

bi_cota_petrobras — cota mensal de volume por polo/produto (COTA_PETROBRAS) · sincronizada¶

xls_processamento — controle do pipeline · local¶

xls_conjunto_lote — coordenação de lote · local¶

xls_nuke_replication — auditoria do nuke · local¶