Dados

Modelo relacional commerce-vc1

PostgreSQL 16 · 76 tabelas em 14 schemas. Cada revenda é um tenant (reseller_id); valores monetários em centavos (inteiros). Trilha de auditoria append-only e anexos sensíveis em storage privado (LGPD).

DDL canônico: schema.dbml · explicações: 04-DATABASE.md

O que isso significa para você

Os diagramas mostram o que a plataforma guarda — não é SQL para rodar. A base comum (revenda, login, arquivos, auditoria) aparece embaixo; o fluxo comercial vai da tabela de preços até fatura e NF.

Visão geral dos schemas

Relações principais (linguagem simples)

Revenda → clientes: cada cliente final pertence a uma revenda; assinatura congela o plano vendido.
Proposta → contrato: o funil gera snapshot imutável; contrato assinado amarra assinatura e linha telefônica.
Uso → fatura: ligações tarifadas no ciclo mensal viram linhas de fatura congeladas; repasse só no modo VC1 fatura.
Fatura → NFCom: documento fiscal referencia a fatura; tentativas de API ficam no log.
Carteira pré-paga: saldo e movimentos são append-only — ajuste é novo lançamento, não edição.

Buscar tabela ou coluna

Filtra os cards colapsáveis abaixo (todas as 76 tabelas).

Preços VC1 e faixas (pricing)

Livro de preços VC1, faixas e clones por revenda.

Diagrama ER pricing — **Preços VC1 e faixas.** FK internas e refs tracejadas.

pricing.price_base_version_rows — IMMUTABLE snapshot of unit rates. CHECK min_commitment_cents > 0; CHECK rates >=

Coluna	Tipo
`id` PK	uuid
`price_base_version_id` → pricing.price_base_versions.id	uuid
`vc1_tier_id` → pricing.vc1_tiers.id	uuid
`min_commitment_cents`	bigint
`unit_did_cents`	bigint
`unit_channel_cents`	bigint
`unit_landline_cents_per_min`	numeric(12,4)
`unit_mobile_cents_per_min`	numeric(12,4)
`fidelity_default_months`	int
`created_at`	timestamptz

pricing.price_base_versions — IMMUTABLE after published_at. No updated_at. CHECK cadence 30/6 on parent.

Coluna	Tipo
`id` PK	uuid
`price_base_id` → pricing.price_bases.id	uuid
`version_number`	int
`effective_from`	timestamptz
`effective_until`	timestamptz
`published_at`	timestamptz
`published_by_user_id` → identity.users.id	uuid
`change_reason`	text
`created_at`	timestamptz

pricing.price_bases — VC1 price book. Versions are immutable; editor always inserts a new version.

Coluna	Tipo
`id` PK	uuid
`name`	varchar(128)
`code`	varchar(32)
`cadence_min_s`	int
`cadence_inc_s`	int
`is_active`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

pricing.reseller_tier_rows — Reseller may edit row-by-row after clone. Selling below cost sets below_cost=tru

Coluna	Tipo
`id` PK	uuid
`reseller_id` → pricing.reseller_tier_tables.id	uuid
`reseller_tier_table_id` → pricing.reseller_tier_tables.id	uuid
`vc1_tier_id` → pricing.vc1_tiers.id	uuid
`source_version_row_id` → pricing.price_base_version_rows.id	uuid
`markup_pct`	numeric(8,4)
`unit_did_cents`	bigint
`unit_channel_cents`	bigint
`unit_landline_cents_per_min`	numeric(12,4)
`unit_mobile_cents_per_min`	numeric(12,4)
`min_commitment_cents`	bigint
`below_cost`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz

pricing.reseller_tier_tables

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`source_price_base_version_id` → pricing.price_base_versions.id	uuid
`markup_pct`	numeric(8,4)
`is_current`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

pricing.vc1_tiers — Stable band identity. Rates live on price_base_version_rows. locked_tier_id poin

Coluna	Tipo
`id` PK	uuid
`price_base_id` → pricing.price_bases.id	uuid
`code`	varchar(32)
`name`	varchar(128)
`sort_order`	int
`created_at`	timestamptz
`deleted_at`	timestamptz

Planos comerciais (catalog)

Planos comerciais, destinos e pacotes sugeridos.

Diagrama ER catalog — **Planos comerciais.** FK internas e refs tracejadas.

catalog.destinations

Coluna	Tipo
`code` PK	destination
`name`	varchar(64)
`is_billable`	boolean
`blocked_by_default`	boolean
`created_at`	timestamptz

catalog.plan_quotas — CHECK separate ⇒ total=0 and (landline+mobile)>=0; pooled ⇒ total>0 and landline

Coluna	Tipo
`id` PK	uuid
`reseller_id` → catalog.plan_versions.id	uuid
`plan_version_id` → catalog.plan_versions.id	uuid
`included_dids`	int
`included_channels`	int
`franchise_mode`	plan_franchise_mode
`franchise_landline_seconds`	int
`franchise_mobile_seconds`	int
`franchise_total_seconds`	int
`created_at`	timestamptz

catalog.plan_trial_rules — Reseller defines trial per plan. duration_days=0 AND enabled=false ⇒ no trial.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → catalog.plan_versions.id	uuid
`plan_version_id` → catalog.plan_versions.id	uuid
`enabled`	boolean
`duration_days`	int
`franchise_landline_seconds`	int
`franchise_mobile_seconds`	int
`franchise_total_seconds`	int
`included_dids`	int
`included_channels`	int
`created_at`	timestamptz

catalog.plan_versions — IMMUTABLE. CHECK kind=package ⇒ sell_price_cents NOT NULL AND franchise_mode NOT

Coluna	Tipo
`id` PK	uuid
`reseller_id` → pricing.reseller_tier_rows.id	uuid
`plan_id` → catalog.plans.id	uuid
`version_number`	int
`kind`	plan_kind
`franchise_mode`	plan_franchise_mode
`overage_policy`	plan_overage_policy
`payment_mode`	payment_mode
`sell_price_cents`	bigint
`commitment_cents`	bigint
`locked_tier_id` → pricing.vc1_tiers.id	uuid
`reseller_tier_row_id` → pricing.reseller_tier_rows.id	uuid
`unit_did_cents`	bigint
`unit_channel_cents`	bigint
`unit_landline_cents_per_min`	numeric(12,4)
`unit_mobile_cents_per_min`	numeric(12,4)
`overage_landline_cents_per_min`	numeric(12,4)
`overage_mobile_cents_per_min`	numeric(12,4)
`cost_did_cents`	bigint
`cost_channel_cents`	bigint
`cost_landline_cents_per_min`	numeric(12,4)
`cost_mobile_cents_per_min`	numeric(12,4)
`fidelity_months`	int
`below_cost`	boolean
`snapshot`	jsonb
`published_at`	timestamptz
`created_at`	timestamptz

catalog.plans — Head entity. Commercial fields live on plan_versions (immutable).

Coluna	Tipo
`id` PK	uuid
`reseller_id` → catalog.plan_versions.id	uuid
`suggested_package_id` → catalog.suggested_packages.id	uuid
`code`	varchar(64)
`name`	varchar(128)
`kind`	plan_kind
`is_active`	boolean
`current_version_id` → catalog.plan_versions.id	uuid
`negative_margin_acknowledged_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

catalog.suggested_packages — VC1-owned templates. Clone copies into catalog.plans with reseller_id set.

Coluna	Tipo
`id` PK	uuid
`code`	varchar(32)
`name`	varchar(128)
`description`	text
`kind`	plan_kind
`template`	jsonb
`is_active`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

Funil e contratos (sales)

Funil comercial, propostas, trials e contratos.

Diagrama ER sales — **Funil e contratos.** FK internas e refs tracejadas.

sales.contracts — Replaces public.sales as the signed commercial artifact. Attestation lives on si

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`proposal_id` → sales.proposals.id	uuid
`customer_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`status`	contract_status
`issuer`	invoice_issuer
`snapshot`	jsonb
`effective_at`	timestamptz
`signed_at`	timestamptz
`cancelled_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

sales.proposal_events — Append-only. No updated_at / deleted_at.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → sales.proposals.id	uuid
`proposal_id` → sales.proposals.id	uuid
`event_type`	varchar(64)
`payload`	jsonb
`actor_type`	varchar(20)
`actor_user_id` → identity.users.id	uuid
`created_at`	timestamptz

sales.proposal_items — Line snapshot at emit. Frozen with parent snapshot jsonb.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → sales.proposals.id	uuid
`proposal_id` → sales.proposals.id	uuid
`kind`	invoice_line_kind
`quantity`	int
`unit_sell_cents`	bigint
`unit_cost_cents`	bigint
`label`	varchar(255)
`metadata`	jsonb
`created_at`	timestamptz

sales.proposals — Fork of public.proposals. Totals NEVER trusted from the browser — snapshot is se

Coluna	Tipo
`id` PK	uuid
`reseller_id` → catalog.plan_versions.id	uuid
`customer_id` → customers.customers.id	uuid
`seller_user_id` → identity.users.id	uuid
`public_code`	varchar(80)
`proposal_number`	varchar(32)
`stage`	proposal_stage
`kanban_column`	kanban_column
`profile_type`	profile_type
`document`	varchar(14)
`legal_name`	varchar(255)
`contact_name`	varchar(255)
`contact_email`	varchar(255)
`contact_phone`	varchar(20)
`plan_id` → catalog.plans.id	uuid
`plan_version_id` → catalog.plan_versions.id	uuid
`payment_mode`	payment_mode
`snapshot`	jsonb
`public_access_username`	varchar(32)
`public_access_password_hash`	varchar(255)
`public_access_attempts`	smallint
`public_access_blocked_until`	timestamptz
`pin_hash`	varchar(255)
`views_count`	int
`viewed_at`	timestamptz
`sent_at`	timestamptz
`accepted_at`	timestamptz
`expires_at`	timestamptz
`contracted_at`	timestamptz
`lost_reason`	text
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

sales.signature_envelopes

Coluna	Tipo
`id` PK	uuid
`reseller_id` → sales.contracts.id	uuid
`contract_id` → sales.contracts.id	uuid
`mesign_envelope_id`	varchar(64)
`document_sha256`	varchar(64)
`status`	varchar(32)
`sign_url`	text
`webhook_last_event`	varchar(64)
`completed_at`	timestamptz
`archived_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

sales.trials

Coluna	Tipo
`id` PK	uuid
`reseller_id` → catalog.plans.id	uuid
`proposal_id` → sales.proposals.id	uuid
`customer_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`plan_id` → catalog.plans.id	uuid
`plan_version_id` → catalog.plan_versions.id	uuid
`status`	trial_status
`starts_at`	timestamptz
`ends_at`	timestamptz
`franchise_landline_seconds`	int
`franchise_mobile_seconds`	int
`converted_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

Clientes finais (customers)

Clientes finais, assinaturas e portabilidade.

Diagrama ER customers — **Clientes finais.** FK internas e refs tracejadas.

customers.addresses

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`customer_id` → customers.customers.id	uuid
`kind`	varchar(16)
`zip`	varchar(8)
`street`	varchar(255)
`number`	varchar(32)
`complement`	varchar(128)
`neighborhood`	varchar(128)
`city`	varchar(128)
`state`	char(2)
`ibge_city_code`	varchar(7)
`is_primary`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

customers.contacts — Replaces sales-mework360 people + customer_people (one person per role per custo

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`customer_id` → customers.customers.id	uuid
`role`	contact_role
`full_name`	varchar(255)
`email`	varchar(255)
`phone`	varchar(20)
`document`	varchar(11)
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

customers.customer_users

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`customer_id` → customers.customers.id	uuid
`user_id` → identity.users.id	uuid
`is_admin`	boolean
`created_at`	timestamptz
`deleted_at`	timestamptz

customers.customers — kind=reseller_self is the reseller own-usage subscriber used in VC1 minimum aggr

Coluna	Tipo
`id` PK	uuid
`reseller_id` → sales.proposals.id	uuid
`kind`	customer_kind
`status`	customer_status
`profile_type`	profile_type
`legal_name`	varchar(255)
`trade_name`	varchar(255)
`document`	varchar(14)
`email`	varchar(255)
`phone`	varchar(20)
`payment_mode`	payment_mode
`legacy_org_id`	bigint
`legacy_tronco_id`	bigint
`source_proposal_id` → sales.proposals.id	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

customers.portability_files

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.portability_requests.id	uuid
`customer_id` → customers.customers.id	uuid
`portability_request_id` → customers.portability_requests.id	uuid
`file_id` → files.objects.id	uuid
`retained_until`	date
`created_at`	timestamptz
`deleted_at`	timestamptz

customers.portability_requests

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`customer_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`numbers`	jsonb [not null, default: `'
`donor_operator`	varchar(128)
`status`	varchar(32)
`legacy_portability_id`	bigint
`submitted_at`	timestamptz
`completed_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

customers.subscriptions — did_count/channel_count are denormalized live totals; source of truth is telco.d

Coluna	Tipo
`id` PK	uuid
`reseller_id` → catalog.plan_versions.id	uuid
`customer_id` → customers.customers.id	uuid
`plan_id` → catalog.plans.id	uuid
`plan_version_id` → catalog.plan_versions.id	uuid
`status`	subscription_status
`payment_mode`	payment_mode
`overage_landline_cents_per_min`	numeric(12,4)
`overage_mobile_cents_per_min`	numeric(12,4)
`did_count`	int
`channel_count`	int
`trial_ends_at`	timestamptz
`started_at`	timestamptz
`cancelled_at`	timestamptz
`cancel_reason`	text
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

Linha telefônica (SIP/DID) (telco)

Contas SIP, DIDs, canais e bloqueios de originação.

Diagrama ER telco — **Linha telefônica (SIP/DID).** FK internas e refs tracejadas.

telco.block_events — Append-only audit of TelcoPort blockSubscriber / alterar_status_boqueio.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → telco.sip_accounts.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`sip_account_id` → telco.sip_accounts.id	uuid
`reason`	block_reason
`action`	varchar(16)
`actor_user_id` → identity.users.id	uuid
`connector_call_id` → integration.connector_calls.id	uuid
`created_at`	timestamptz

telco.channel_allocations — Current simultaneous call limit. History in channel_changes.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → telco.sip_accounts.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`sip_account_id` → telco.sip_accounts.id	uuid
`quantity`	int
`effective_from`	date
`created_at`	timestamptz
`updated_at`	timestamptz

telco.channel_changes — Append-only. Mid-cycle adds bill remaining days / days_in_cycle.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`kind`	channel_change_kind
`from_quantity`	int
`to_quantity`	int
`effective_on`	date
`pro_rata`	boolean
`actor_user_id` → identity.users.id	uuid
`created_at`	timestamptz

telco.dids

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.portability_requests.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`customer_id` → customers.customers.id	uuid
`e164`	varchar(16)
`city`	varchar(128)
`area_code`	varchar(4)
`activated_at`	date
`deactivated_at`	date
`legacy_did_id`	bigint
`portability_request_id` → customers.portability_requests.id	uuid
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

telco.sip_accounts — Mirror of SipPulse subscriber. Password never stored (ProvisioningPort / generat

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`customer_id` → customers.customers.id	uuid
`family`	sip_family
`username`	varchar(64)
`sip_domain`	varchar(255)
`accountcode`	varchar(128)
`call_limit`	int
`legacy_rate_plan_id`	int
`legacy_subscriber_ref`	varchar(128)
`blocked_at`	timestamptz
`block_reason`	block_reason
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

Tarificação de ligações (rating)

Ciclos, CDR, prefixos e chamadas tarifadas.

Tarificação de ligações. FK internas e refs tracejadas.

Abrir em tamanho real

rating.cdr_imports

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`source`	varchar(32)
`cursor_after_id`	bigint
`cursor_after_time`	timestamptz
`rows_read`	int
`rows_rated`	int
`rows_skipped`	int
`started_at`	timestamptz
`finished_at`	timestamptz
`error`	text
`created_at`	timestamptz

rating.cycles — Calendar month America/Sao_Paulo [period_start, period_end) for all resellers (D

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`period_start`	date
`period_end`	date
`status`	cycle_status
`closed_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

rating.destination_prefixes — Classifies B-number into landline_br / mobile_br / other. Prefix list A VERIFICA

Coluna	Tipo
`id` PK	uuid
`prefix`	varchar(16)
`destination`	destination
`country_iso2`	char(2)
`description`	varchar(128)
`created_at`	timestamptz
`deleted_at`	timestamptz

rating.rated_calls — PARTITION BY RANGE (started_at) monthly. IMMUTABLE (append-only). Commercial rat

Coluna	Tipo
`id`	uuid
`reseller_id` → rating.cycles.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`customer_id` → customers.customers.id	uuid
`sip_account_id` → telco.sip_accounts.id	uuid
`cycle_id` → rating.cycles.id	uuid
`usage_record_id`	uuid
`started_at`	timestamptz
`legacy_cdr_id`	bigint
`accountcode`	varchar(128)
`source_number`	varchar(32)
`destination_number`	varchar(32)
`destination`	destination
`raw_seconds`	int
`billed_seconds`	int
`sell_rate_cents_per_min`	numeric(12,4)
`cost_rate_cents_per_min`	numeric(12,4)
`sell_amount_cents`	numeric(14,4)
`cost_amount_cents`	numeric(14,4)
`switch_price_cents`	bigint
`switch_duration`	int
`switch_duration_block`	int
`service_type`	varchar(64)
`rated_at`	timestamptz
`created_at`	timestamptz

rating.usage_records — PARTITION BY RANGE (period_start) monthly. Grain: one row per subscription × des

Coluna	Tipo
`id`	uuid
`reseller_id` → rating.cycles.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`customer_id` → customers.customers.id	uuid
`cycle_id` → rating.cycles.id	uuid
`destination`	destination
`period_start`	date
`raw_seconds`	bigint
`billed_seconds`	bigint
`call_count`	int
`sell_cents`	bigint
`cost_cents`	bigint
`franchise_seconds_applied`	bigint
`overage_seconds`	bigint
`created_at`	timestamptz
`updated_at`	timestamptz

Faturamento (billing)

Crédito, faturas, repasses e ciclo de cobrança.

Diagrama ER billing — **Faturamento.** FK internas e refs tracejadas.

billing.commission_invoices — NF of commission issued BY the reseller TO VC1. Blocks payout until received.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.payouts.id	uuid
`payout_id` → billing.payouts.id	uuid
`number`	varchar(64)
`amount_cents`	bigint
`file_id` → files.objects.id	uuid
`received_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

billing.credit_ledger — Append-only. No UPDATE/DELETE. Unique idempotency prevents double debit of a CDR

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`wallet_id` → billing.credit_wallets.id	uuid
`customer_id` → customers.customers.id	uuid
`entry_type`	ledger_entry_type
`amount_cents`	numeric(14,4)
`unrecovered_cents`	numeric(14,4)
`balance_after_cents`	numeric(14,4)
`source_type`	varchar(32)
`source_id`	uuid
`idempotency_key`	varchar(128)
`created_at`	timestamptz

billing.credit_limits — Postpaid only. Exposure = DIDs+channels+minutes in the open cycle.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`customer_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`limit_cents`	bigint
`alert_percent`	smallint
`block_percent`	smallint
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

billing.credit_wallets — Prepaid SoT. CHECK balance_cents >= 0. Concurrency: SELECT ... FOR UPDATE (D-DB-

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`customer_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`balance_cents`	numeric(14,4)
`version`	int
`created_at`	timestamptz
`updated_at`	timestamptz

billing.cycle_runs

Coluna	Tipo
`id` PK	uuid
`reseller_id` → rating.cycles.id	uuid
`cycle_id` → rating.cycles.id	uuid
`phase`	varchar(32)
`status`	varchar(16)
`started_at`	timestamptz
`finished_at`	timestamptz
`stats`	jsonb
`error`	text
`created_at`	timestamptz

billing.dunning_events

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.invoices.id	uuid
`invoice_id` → billing.invoices.id	uuid
`kind`	dunning_kind
`payload`	jsonb
`fired_at`	timestamptz
`created_at`	timestamptz

billing.invoice_lines — IMMUTABLE. No updated_at. Insert-only; corrections via credit invoice.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.invoices.id	uuid
`invoice_id` → billing.invoices.id	uuid
`kind`	invoice_line_kind
`description`	varchar(255)
`quantity`	numeric(14,4)
`unit_sell_cents`	bigint
`amount_cents`	bigint
`cost_cents`	bigint
`destination`	destination
`snapshot`	jsonb
`created_at`	timestamptz

billing.invoices — CHECK NOT (party=customer AND issuer=reseller). party=customer + issuer=vc1 when

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`cycle_id` → rating.cycles.id	uuid
`party`	invoice_party
`customer_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`issuer`	invoice_issuer
`status`	invoice_status
`number`	varchar(32)
`subtotal_cents`	bigint
`commitment_cents`	bigint
`charged_cents`	bigint
`cost_cents`	bigint
`margin_cents`	bigint
`due_on`	date
`issued_at`	timestamptz
`paid_at`	timestamptz
`cancelled_at`	timestamptz
`mepay_invoice_id`	varchar(64)
`mepay_charge_id`	varchar(64)
`payment_link_url`	text
`replaces_invoice_id`	uuid
`snapshot`	jsonb
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

billing.limit_events — Append-only. Alert at 80% (reseller+VC1), block at 100%.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`credit_limit_id` → billing.credit_limits.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`threshold_percent`	smallint
`action`	limit_action
`exposure_cents`	bigint
`limit_cents`	bigint
`fired_at`	timestamptz
`created_at`	timestamptz

billing.payment_methods

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`party`	invoice_party
`customer_id` → customers.customers.id	uuid
`kind`	payment_method_kind
`mepay_ref`	varchar(64)
`label`	varchar(128)
`is_default`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

billing.payments — Only invoices actually paid count toward payout margin and (PADRÃO PROPOSTO) res

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.payment_methods.id	uuid
`invoice_id` → billing.invoices.id	uuid
`payment_method_id` → billing.payment_methods.id	uuid
`amount_cents`	bigint
`kind`	payment_method_kind
`mepay_charge_id`	varchar(64)
`paid_at`	timestamptz
`raw_payload`	jsonb
`created_at`	timestamptz

billing.payout_adjustments — Settlement ledger for post-payout refunds/chargebacks (D-DOM-73).

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.payouts.id	uuid
`payout_id` → billing.payouts.id	uuid
`source_type`	varchar(32)
`source_id`	uuid
`amount_cents`	bigint
`snapshot`	jsonb
`created_at`	timestamptz

billing.payout_lines — IMMUTABLE breakdown of the payout formula.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.invoices.id	uuid
`payout_id` → billing.payouts.id	uuid
`kind`	payout_line_kind
`invoice_id` → billing.invoices.id	uuid
`amount_cents`	bigint
`snapshot`	jsonb
`created_at`	timestamptz

billing.payouts — Only billing_mode=vc1_invoices. payout_raw=0 → status settled_zero (no commissio

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.invoices.id	uuid
`cycle_id` → rating.cycles.id	uuid
`status`	payout_status
`paid_margin_cents`	bigint
`shortfall_cents`	bigint
`negative_margin_cents`	bigint
`net_cents`	bigint
`netted_invoice_id` → billing.invoices.id	uuid
`commission_invoice_id` → billing.commission_invoices.id	uuid
`paid_at`	timestamptz
`paid_by_user_id` → identity.users.id	uuid
`transfer_ref`	varchar(64)
`snapshot`	jsonb
`created_at`	timestamptz
`updated_at`	timestamptz

billing.statements — Rated statement for reseller_invoices mode. VC1 does not issue NF to that custom

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.subscriptions.id	uuid
`cycle_id` → rating.cycles.id	uuid
`customer_id` → customers.customers.id	uuid
`subscription_id` → customers.subscriptions.id	uuid
`status`	statement_status
`subtotal_cents`	bigint
`charged_cents`	bigint
`cost_cents`	bigint
`snapshot`	jsonb
`issued_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

Notas fiscais (NFCom) (fiscal)

Documentos NFCom e tentativas de integração fiscal.

Diagrama ER fiscal — **Notas fiscais (NFCom).** FK internas e refs tracejadas.

fiscal.fiscal_attempts — Append-only. Do not store mePay API keys or third-party issuer secrets.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → fiscal.fiscal_documents.id	uuid
`fiscal_document_id` → fiscal.fiscal_documents.id	uuid
`endpoint`	varchar(64)
`http_status`	int
`request_hash`	varchar(64)
`response`	jsonb
`success`	boolean
`created_at`	timestamptz

fiscal.fiscal_documents — issued ONLY after mePay webhook fiscal.issued (or poll fallback) with 44-digit a

Coluna	Tipo
`id` PK	uuid
`reseller_id` → billing.invoices.id	uuid
`invoice_id` → billing.invoices.id	uuid
`status`	fiscal_document_status
`model`	varchar(8)
`issuer_cnpj`	varchar(14)
`mepay_invoice_id`	varchar(64)
`mepay_fiscal_request_id`	varchar(64)
`access_key`	varchar(44)
`protocol`	varchar(64)
`pdf_file_id` → files.objects.id	uuid
`xml_file_id` → files.objects.id	uuid
`authorized_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz

Notificações (notifications)

Templates, mensagens e opt-out por canal.

Diagrama ER notifications — **Notificações.** FK internas e refs tracejadas.

notifications.messages

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`customer_id` → customers.customers.id	uuid
`template_id` → notifications.templates.id	uuid
`channel`	notification_channel
`recipient`	varchar(255)
`status`	varchar(16)
`payload`	jsonb
`provider_message_id`	varchar(128)
`error`	text
`sent_at`	timestamptz
`created_at`	timestamptz

notifications.opt_outs

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`recipient`	varchar(255)
`channel`	notification_channel
`created_at`	timestamptz

notifications.templates

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`key`	varchar(64)
`channel`	notification_channel
`subject`	varchar(255)
`body`	text
`is_active`	boolean
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

Integrações e legado (integration)

Outbox, webhooks, conectores e reconciliação.

Diagrama ER integration — **Integrações e legado.** FK internas e refs tracejadas.

integration.connector_calls — Never store SOAP passwords or API tokens. LabGuarded* sets lab_guarded=true.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`port`	varchar(32)
`adapter`	varchar(64)
`method`	varchar(64)
`request_hash`	varchar(64)
`response_code`	int
`success`	boolean
`lab_guarded`	boolean
`error`	text
`duration_ms`	int
`created_at`	timestamptz

integration.inbox_webhooks — Dedupe: provider event_id when present; else sha256(envelope_id + event + payloa

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`source`	varchar(32)
`event_name`	varchar(128)
`idempotency_key`	varchar(128)
`status`	webhook_status
`headers`	jsonb
`payload`	jsonb
`processed_at`	timestamptz
`created_at`	timestamptz

integration.lab_allowlist — LabGuarded* adapters refuse writes outside this list. Demo WL project + demo SIP

Coluna	Tipo
`id` PK	uuid
`kind`	varchar(32)
`legacy_id`	bigint
`accountcode`	varchar(128)
`e164`	varchar(16)
`notes`	text
`created_at`	timestamptz
`deleted_at`	timestamptz

integration.legacy_id_map

Coluna	Tipo
`id` PK	uuid
`entity_type`	varchar(64)
`legacy_id`	bigint
`entity_uuid`	uuid
`legacy_table`	varchar(128)
`created_at`	timestamptz

integration.outbox

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`aggregate_type`	varchar(64)
`aggregate_id`	uuid
`event_name`	varchar(128)
`payload`	jsonb
`status`	outbox_status
`attempts`	int
`available_at`	timestamptz
`published_at`	timestamptz
`last_error`	text
`created_at`	timestamptz

integration.reconciliation_runs

Coluna	Tipo
`id` PK	uuid
`reseller_id` → rating.cycles.id	uuid
`cycle_id` → rating.cycles.id	uuid
`status`	reconciliation_status
`billed_seconds_sum`	bigint
`switch_duration_block_sum`	bigint
`sell_cents_sum`	bigint
`switch_price_cents_sum`	bigint
`drift_percent`	numeric(8,4)
`started_at`	timestamptz
`finished_at`	timestamptz
`report`	jsonb
`created_at`	timestamptz

Auditoria (audit)

Trilha append-only de auditoria administrativa.

Diagrama ER audit — **Auditoria.** FK internas e refs tracejadas.

audit.audit_log — Append-only. Financial/admin trail retention 5y; auth/access 12m (D-SEC-15).

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`actor_user_id` → identity.users.id	uuid
`impersonator_user_id` → identity.users.id	uuid
`impersonation_session_id` → identity.impersonation_sessions.id	uuid
`actor_scope`	actor_scope
`action`	varchar(128)
`auditable_type`	varchar(128)
`auditable_id`	uuid
`before`	jsonb
`after`	jsonb
`ip`	varchar(45)
`user_agent`	varchar(512)
`created_at`	timestamptz

Arquivos privados (files)

Objetos privados em storage (LGPD, marca, fiscal).

Diagrama ER files — **Arquivos privados.** FK internas e refs tracejadas.

files.objects — Private object storage only. Portability docs MUST set retained_until. Never pub

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`purpose`	file_purpose
`disk`	varchar(32)
`path`	varchar(512)
`mime_type`	varchar(128)
`byte_size`	bigint
`sha256`	varchar(64)
`original_name`	varchar(255)
`retained_until`	date
`created_by_user_id` → identity.users.id	uuid
`created_at`	timestamptz
`deleted_at`	timestamptz

Autenticação e permissões (identity)

Diagrama ER identity — **Autenticação e permissões.** FK internas e refs tracejadas.

identity.api_tokens — Replaces public.personal_access_tokens. Reseller public API lives on api.vc1.com

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`user_id` → identity.users.id	uuid
`name`	varchar(128)
`token_prefix`	varchar(12)
`token_hash`	varchar(64)
`abilities`	jsonb [not null, default: `'
`last_used_at`	timestamptz
`expires_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

identity.impersonation_sessions

Coluna	Tipo
`id` PK	uuid
`staff_user_id` → identity.users.id	uuid
`impersonated_user_id` → identity.users.id	uuid
`reseller_id` → tenancy.resellers.id	uuid
`expires_at`	timestamptz
`ended_at`	timestamptz
`created_at`	timestamptz

identity.permissions

Coluna	Tipo
`id` PK	uuid
`slug`	varchar(128)
`name`	varchar(255)
`created_at`	timestamptz

identity.role_permissions

Coluna	Tipo
`role_id` → identity.roles.id	uuid
`permission_id` → identity.permissions.id	uuid

identity.roles

Coluna	Tipo
`id` PK	uuid
`slug`	varchar(64)
`name`	varchar(128)
`actor_scope`	actor_scope
`created_at`	timestamptz
`updated_at`	timestamptz

identity.user_roles

Coluna	Tipo
`id` PK	uuid
`user_id` → identity.users.id	uuid
`role_id` → identity.roles.id	uuid
`reseller_id` → tenancy.resellers.id	uuid
`created_at`	timestamptz

identity.users — Login for VC1 Filament, reseller panel, customer /app. Replaces public.users (bi

Coluna	Tipo
`id` PK	uuid
`reseller_id` → customers.customers.id	uuid
`customer_id` → customers.customers.id	uuid
`email`	varchar(255)
`password_hash`	varchar(255)
`full_name`	varchar(255)
`actor_scope`	actor_scope
`is_active`	boolean
`email_verified_at`	timestamptz
`last_login_at`	timestamptz
`last_login_ip`	varchar(45)
`two_factor_secret`	text
`two_factor_confirmed_at`	timestamptz
`locale`	varchar(16)
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

Revendas (tenant) (tenancy)

Tenant revenda: domínios, marca, STFC e contrato VC1.

Diagrama ER tenancy — **Revendas (tenant).** FK internas e refs tracejadas.

tenancy.reseller_branding

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`trade_name`	varchar(255)
`logo_file_id` → files.objects.id	uuid
`primary_color`	varchar(7)
`secondary_color`	varchar(7)
`support_email`	varchar(255)
`support_phone`	varchar(32)
`sender_email`	varchar(255)
`sender_name`	varchar(128)
`extra`	jsonb
`created_at`	timestamptz
`updated_at`	timestamptz

tenancy.reseller_contracts — VC1 ↔ reseller commercial contract. Snapshot immutable after signed_at.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`price_base_version_id` → pricing.price_base_versions.id	uuid
`vc1_tier_id` → pricing.vc1_tiers.id	uuid
`commitment_cents`	bigint
`fidelity_months`	int
`billing_mode`	reseller_billing_mode
`snapshot`	jsonb
`signed_at`	timestamptz
`effective_from`	date
`effective_until`	date
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

tenancy.reseller_domains

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`host`	varchar(255)
`is_primary`	boolean
`verified_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

tenancy.reseller_settings

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`default_payment_mode`	payment_mode
`default_overage_policy`	plan_overage_policy
`alert_limit_percent`	smallint
`block_limit_percent`	smallint
`allow_negative_margin`	boolean
`typed`	jsonb
`created_at`	timestamptz
`updated_at`	timestamptz

tenancy.resellers — CHECK billing_mode=reseller_invoices ⇒ exists verified STFC license. CHECK slug

Coluna	Tipo
`id` PK	uuid
`slug`	varchar(63)
`legal_name`	varchar(255)
`trade_name`	varchar(255)
`document`	varchar(14)
`status`	reseller_status
`billing_mode`	reseller_billing_mode
`vc1_tier_id` → pricing.vc1_tiers.id	uuid
`price_base_version_id` → pricing.price_base_versions.id	uuid
`commitment_cents`	bigint
`fidelity_months`	int
`payment_mode`	payment_mode
`due_day`	smallint
`pending_billing_mode`	reseller_billing_mode
`billing_mode_effective_at`	date
`pending_billing_amendment_envelope_id`	uuid
`legacy_project_id`	bigint
`legacy_domain_sippulse`	varchar(255)
`payment_gateway_ref`	varchar(64)
`onboarded_at`	timestamptz
`suspended_at`	timestamptz
`terminated_at`	timestamptz
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

tenancy.stfc_licenses — Required for billing_mode=reseller_invoices. Verified by VC1 staff.

Coluna	Tipo
`id` PK	uuid
`reseller_id` → tenancy.resellers.id	uuid
`license_number`	varchar(64)
`document_file_id` → files.objects.id	uuid
`valid_from`	date
`valid_until`	date
`verified_at`	timestamptz
`verified_by_user_id` → identity.users.id	uuid
`notes`	text
`created_at`	timestamptz
`updated_at`	timestamptz
`deleted_at`	timestamptz

Retenção e privacidade

Tipo	Prazo	Notas
CDR / detalhe de chamada	~13 meses	Operação e disputa recente
Financeiro, fiscal, contratos	~5 anos	Obrigações legais
Logs de acesso	~12 meses	Segurança e LGPD

Ver segurança e LGPD →