Modelo de dados

Schema source-of-truth: src/lib/db/schema.ts (Drizzle). Postgres+pgvector self-hosted (container vek1-postgres no VPS Hermes). Aplicado via bun run db:push --force — sem SQL migrations versionadas.

Importante: db:push --force dropa indexes não-Drizzle (hnsw vetorial, unique constraints custom). Sempre re-aplicar scripts/recreate-indexes.ts + init/01-init.sql (do vek1-api) após push destrutivo. Ver schema-migrator agent.

Tabelas Better Auth (auth core)

• user — id text PK, name, email unique, email_verified, image?, timestamps
• session — id, user_id FK→user, expires_at, token unique, ip_address, user_agent
• account — id, user_id FK, provider_id, account_id, OAuth tokens (access/refresh/id), password (credential provider), scope
• verification — id, identifier, value, expires_at (PKCE, reset-password, email-verify)

Tabelas app (multi-tenant)

company_profiles — tenant raiz

• id text PK (= Better Auth user.id, CUID/UUID-ish)
• name, slug unique, logo_url?, bucket_name?, settings jsonb
• Criada via endpoint /internal/company/ensure no signup (não tem mais trigger SQL)

stores — vector stores / catálogos

• id uuid, company_id text FK→company_profiles ON DELETE CASCADE
• name, slug, description?, status text default 'active' ('active'|'inactive'|'deleted')
• allocated_size_gb int default 1 (1..1000), used_size_bytes int default 0
• embedding_dimension int default 1024 (alinhado com bge-m3 — não mais 1536 da OpenAI)
• similarity_function text default 'cosine'

agents

• id uuid, store_id FK→stores, name, type text, description?, active bool default false
• spending_limit double, stripe_product_id text? (não utilizado ainda)
• evolution_instance_id text? — nome da instância Evolution (vek1-{slug}-{storeId})
• Persona: system_prompt text?, voice_tone text default 'balanced'
• Channels: website_enabled bool default true, whatsapp_enabled bool default false, whatsapp_phone_number text?
• Limits: rate_limit_per_hour int?

agent_knowledge_base — N:N

• agent_id FK→agents, document_id FK→documents, created_at. PK composto.

documents — KB + embeddings

• id uuid, store_id FK→stores, product_id? FK→products ON DELETE SET NULL
• title, content text, file_name?, file_size?, mime_type?, category?, tags text[]
• embedding vector(1024) — bge-m3
• metadata jsonb
• Index hnsw (embedding vector_cosine_ops) — re-criado por scripts/recreate-indexes.ts

leads — NEW (PR #48)

• id uuid, store_id FK→stores
• channel text ('whatsapp' | 'website'), external_id text (telefone limpo p/ WhatsApp; user.id p/ web)
• Perfil: name?, email?, phone_number?, phone_confirmed bool
• Endereço estruturado: street_address, address_number, complement, neighborhood, postal_code, city, state
• custom_attributes jsonb default {}, interests jsonb default [], preferences jsonb default {}, notes?
• Memória do agente: profile_summary text?, profile_embedding vector(1024)?, profile_updated_at?
• Métricas: status text default 'new', total_messages, total_purchases, total_spent_cents, first_contact_at, last_contact_at
• Unique composto: (store_id, channel, external_id) — re-aplicado por scripts/recreate-indexes.ts

lead_events

• id uuid, lead_id FK→leads, agent_id? FK→agents, type text, data jsonb?, created_at

messages_history

• id uuid, user_id text (telefone ou user.id), store_id?, agent_id?, lead_id? FK→leads
• agent_type text, role text (user|assistant|tool|debug), content text, metadata jsonb?, timestamp

products

• id uuid, store_id FK→stores, name, description?, price double, stock int
• product_integration_id text? (id externo p/ ERP/PDV sync), custom_props jsonb?

product_files

• id uuid, company_id FK→company_profiles, product_id FK→products
• filename (key MinIO), original_filename, file_size, file_type, file_url, bucket_name
• content text? (texto extraído), status text default 'pending', upload_date

token_usage

• id uuid, company_id FK→company_profiles, model, operation_type
• prompt_tokens, completion_tokens, cost_cents, request_id?, metadata jsonb?

Orders + payments (NEW — PR #50)

orders

• id uuid, store_id FK→stores, lead_id?, agent_id?
• number int (sequencial humano por store, set por app code)
• status text default 'draft' — lifecycle:
  • draft → awaiting_proof → proof_received → confirmed → shipping → fulfilled
  • * → cancelled (manual)
  • proof_received → awaiting_proof (loja rejeita comprovante)
• payment_method text? (pix_manual | pix_gateway | cash_on_delivery)
• payment_provider text? (abacate_pay | manual), payment_external_id?, payment_br_code?, payment_br_code_image?, payment_link_url?, payment_expires_at?, paid_at?
• Comprovante manual (foto/PDF do PIX, recebido via Evolution webhook): proof_file_url?, proof_mime_type?, proof_received_at?, proof_external_id?
• shipping_at?, confirmed_at?, fulfilled_at?, cancelled_at?
• Valores em cents: subtotal_cents, shipping_cents, discount_cents, total_cents, currency text default 'BRL'
• Cliente: customer_name, customer_phone, customer_email, customer_tax_id, shipping_address jsonb
• notes?, source text default 'agent' (agent | manual)

order_items

• id, order_id FK→orders, product_id? FK→products, name, description?, price_cents, quantity int default 1, subtotal_cents, custom_props jsonb?

order_events (audit lifecycle)

• id, order_id, type text (created|confirmed|payment_initialized|payment_paid|payment_failed|fulfilled|cancelled|note), actor text (agent|user|system|webhook), data jsonb?

payment_attempts

• id, order_id, provider, external_id?, status (initialized|paid|failed|expired), amount_cents, payload jsonb?

store_payment_settings (1:1 com store)

• store_id unique
• abacate_pay_api_key text?, abacate_pay_dev_mode bool, abacate_pay_webhook_secret?
• pix_enabled bool, pix_expires_in_minutes int default 60
• cash_on_delivery_enabled bool default true
• manual_pix_key?, manual_pix_recipient? (fallback sem gateway)
• default_tax_id?, payment_instructions text?

Stock sync — ERP/PDV bidirecional (NEW — PR #62)

store_stock_sync_settings (1:1 com store)

• outbound_url?, outbound_secret? (vek1 → ERP: notifica decrement/restore)
• inbound_secret? (ERP → vek1: sync inventário)
• outbound_enabled bool, inbound_enabled bool

stock_movements (audit)

• id, store_id, product_id?, delta int, reason text (order_created|order_cancelled|inbound_sync|manual), order_id?, external_id?, payload jsonb?

Audit log — NEW (PR #64)

audit_log (append-only)

• id, actor_user_id?, store_id?, action text (login|logout|order.create|product.update|token.rotate|...), target_type text, target_id?, ip?, ua?, request_id?, meta jsonb?, at timestamp
• Escrito exclusivamente pelo vek1-api via audit_service.log_audit

Diagrama (resumo)

better_auth.user
   │ 1:1
   ▼
company_profiles ──┐ logo_url + bucket_name (MinIO key prefix)
   │ 1:N           │
   ▼               │
stores ──┐         │
   │ 1:N│ 1:1      │
   │    │          │
   │    ▼          │
   │  store_payment_settings
   │  store_stock_sync_settings
   │ 1:N
   ▼
agents ──── N:N ──── documents (agent_knowledge_base)
   │ 1:N           │ N:1 (opt)
   │               ▼
   │             products ─── 1:N ─── product_files
   │ 1:N         │ 1:N
   │             ▼
   │           stock_movements
   ▼
leads ──── 1:N ──── lead_events
   │                  │
   │ 1:N              │
   ▼                  │
messages_history     orders ── 1:N ── order_items
                       │ 1:N             order_events
                       ▼                 payment_attempts
                     order_events
                     payment_attempts

token_usage    audit_log    (independent telemetry)

Indexes críticos (não-Drizzle, recriados manualmente)

• documents: hnsw (embedding vector_cosine_ops) — pgvector busca
• leads: hnsw (profile_embedding vector_cosine_ops) — busca semântica entre leads (futuro)
• leads: unique (store_id, channel, external_id)
• orders: index (store_id, status, created_at desc) — listagem
• messages_history: index (lead_id, timestamp desc), (agent_id, timestamp desc)
• audit_log: index (actor_user_id, at desc), (target_type, target_id, at desc)

scripts/recreate-indexes.ts re-aplica tudo. Sempre rodar após db:push.

Funções/RPC

Vivem no init/01-init.sql do repo vek1-api (não no vek1):

• match_documents_test(query_embedding vector(1024), match_threshold float, match_count int, p_agent_id uuid?) — busca semântica cosine; filtra por agent_knowledge_base se agent_id fornecido
• Trigger auto_updated_at() em várias tabelas

RLS

Não usa RLS Postgres. Autorização é app-level via:

• vek1-api assert_owns_store(actor_user_id, store_id) / assert_owns_order(...) / etc. — JOIN com stores.company_id
• X-Actor-User-Id header propagado de vek1 → vek1-api via apiClient

Storage (MinIO)

• Endpoint público: https://mimic-cdn.kodama.solutions
• Bucket único: vek1 (anonymous download policy)
• User: vek1 (policy vek1-rw = readwrite no bucket)
• Key prefix: companies/{user.id}/products/{productId}/{ts}.{ext} (logos: companies/{user.id}/logo/{ts}.{ext})
• File size limit: 10MB (configurável)
• MIME aceitos: PDF, CSV, DOCX, imagens (jpeg/png/gif/webp)