# FARO-SQL-001 · Migraciones Base MVP

**Código:** FARO-SQL-001
**Nombre:** Migraciones Base MVP FARO Connect
**Versión:** v1.0
**Estado:** Base técnica inicial
**Prioridad:** P1 · Bloqueante para piloto ejecutable
**Motor recomendado:** PostgreSQL 15+
**Formato:** SQL versionado
**Uso:** Backend · Data engineer · CTO · socio técnico · implementación piloto
**Conecta con:**

* FARO-DOC-001 · Script Workshop Discovery
* FARO-DOC-002 · Runbook de Implementación Piloto
* FARO-PL-016 · Inventario de Fuentes de Datos
* FARO-SQL-002 · Multiempresa, roles y RLS
* FARO-CFG-001 · Reglas MVP YAML
* FARO-TEST-001 · Tests KPIs MVP
* FARO-DEMO-001 · Dataset Demo Integral

---

# 1. Objetivo

El objetivo de FARO-SQL-001 es definir la estructura base de datos necesaria para que FARO Connect pueda ejecutar el flujo MVP completo:

```text id="lhpdb7"
Empresa
→ Fuentes
→ Ingesta
→ RAW
→ Staging
→ Maestros
→ Facts
→ KPIs
→ Señales
→ Reglas
→ Tensiones
→ Acciones
→ Evidencia
→ Score
→ Reporte
→ Auditoría
```

Este SQL no busca cubrir toda la versión Enterprise ni Neural. Busca dejar una base sólida, extensible y defendible ante un técnico.

La regla es simple: **si no está en base, no existe; si no tiene trazabilidad, no se puede confiar; si no conecta con acción, no es FARO.**

---

# 2. Principios técnicos

## 2.1 Principios de diseño

1. **Multiempresa desde el día uno.**
2. **Todo dato operativo debe tener `company_id`.**
3. **RAW nunca se modifica.**
4. **Staging transforma, pero no borra la historia.**
5. **Facts alimentan KPIs, no pantallas sueltas.**
6. **KPIs generan señales.**
7. **Señales alimentan reglas.**
8. **Reglas generan tensiones.**
9. **Tensiones generan acciones.**
10. **Acciones requieren responsable, vencimiento y evidencia.**
11. **Score debe ser auditable.**
12. **IA puede explicar, pero no inventar datos.**
13. **Todo cambio relevante debe quedar auditado.**
14. **El MVP debe poder correr con CSV/Excel antes de integraciones complejas.**

---

# 3. Estructura sugerida del paquete de migraciones

```text id="7e60zw"
migrations/
  V001__extensions_and_schemas.sql
  V002__common_functions_and_audit.sql
  V003__companies_org_users_roles.sql
  V004__data_sources_and_ingestion_raw.sql
  V005__master_data.sql
  V006__staging_tables.sql
  V007__fact_tables.sql
  V008__kpis_signals_rules.sql
  V009__tensions_actions_evidence.sql
  V010__score_reports.sql
  V011__indexes_constraints.sql
  V012__seed_minimal_catalogs.sql
```

Para Flyway:

```text id="mjnah4"
V001__nombre.sql
V002__nombre.sql
...
```

Para Sqitch, la lógica puede separarse por cambios, pero se recomienda conservar el mismo orden conceptual.

---

# 4. Convenciones

## 4.1 Nombres

| Tipo         | Convención                               | Ejemplo                      |
| ------------ | ---------------------------------------- | ---------------------------- |
| Tablas       | plural en inglés                         | `companies`, `kpi_snapshots` |
| PK           | `{tabla_singular}_id`                    | `company_id`, `action_id`    |
| FK           | nombre de tabla referenciada             | `company_id`, `branch_id`    |
| Timestamps   | `created_at`, `updated_at`, `deleted_at` |                              |
| Estados      | `status`                                 | `active`, `closed`, `error`  |
| Calidad dato | `dq_status`, `dq_errors`                 |                              |
| Auditoría    | `created_by`, `updated_by`               |                              |

## 4.2 Tipos base

| Campo            | Tipo recomendado |
| ---------------- | ---------------- |
| IDs              | UUID             |
| Montos           | numeric(18,4)    |
| Porcentajes      | numeric(9,6)     |
| Fechas           | date             |
| Timestamps       | timestamptz      |
| Payload flexible | jsonb            |
| Códigos          | text             |
| Estados          | text + CHECK     |
| Booleanos        | boolean          |

## 4.3 Estados generales

```text id="5js6ll"
active
inactive
draft
pending
processing
completed
failed
cancelled
archived
```

---

# 5. V001 · Extensions and Schemas

```sql id="v001"
-- ============================================================
-- FARO-SQL-001 · V001__extensions_and_schemas.sql
-- Base PostgreSQL extensions and schemas
-- ============================================================

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

-- Optional but recommended for future similarity / embeddings.
-- Enable only if available in the environment.
-- CREATE EXTENSION IF NOT EXISTS vector;

CREATE SCHEMA IF NOT EXISTS faro;
CREATE SCHEMA IF NOT EXISTS audit;

COMMENT ON SCHEMA faro IS 'Core FARO Connect application schema';
COMMENT ON SCHEMA audit IS 'Audit and traceability schema';
```

---

# 6. V002 · Common Functions and Audit

```sql id="v002"
-- ============================================================
-- FARO-SQL-001 · V002__common_functions_and_audit.sql
-- Common functions, timestamps, audit log
-- ============================================================

CREATE OR REPLACE FUNCTION faro.set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE IF NOT EXISTS audit.audit_log (
  audit_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NULL,
  actor_user_id uuid NULL,
  entity_name text NOT NULL,
  entity_id uuid NULL,
  action text NOT NULL CHECK (
    action IN (
      'insert',
      'update',
      'delete',
      'login',
      'logout',
      'ingest',
      'process',
      'calculate',
      'evaluate',
      'approve',
      'reject',
      'close',
      'reopen',
      'escalate',
      'export'
    )
  ),
  before_data jsonb NULL,
  after_data jsonb NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  ip_address inet NULL,
  user_agent text NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_audit_log_company_created
ON audit.audit_log (company_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_audit_log_entity
ON audit.audit_log (entity_name, entity_id);

COMMENT ON TABLE audit.audit_log IS 'Central audit trail for relevant FARO actions';
```

---

# 7. V003 · Companies, Organization, Users and Roles

```sql id="v003"
-- ============================================================
-- FARO-SQL-001 · V003__companies_org_users_roles.sql
-- Companies, branches, areas, users, roles, permissions
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.companies (
  company_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_code text NOT NULL UNIQUE,
  legal_name text NOT NULL,
  display_name text NOT NULL,
  country_code text NOT NULL DEFAULT 'AR',
  industry_code text NULL,
  subindustry_code text NULL,
  tax_id text NULL,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'pilot', 'archived')),
  timezone text NOT NULL DEFAULT 'America/Argentina/Mendoza',
  currency_code text NOT NULL DEFAULT 'ARS',
  settings jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL
);

CREATE TRIGGER trg_companies_updated_at
BEFORE UPDATE ON faro.companies
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.branches (
  branch_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  branch_code text NOT NULL,
  name text NOT NULL,
  branch_type text NOT NULL DEFAULT 'commercial'
    CHECK (branch_type IN ('commercial', 'warehouse', 'office', 'factory', 'service', 'other')),
  country_code text NOT NULL DEFAULT 'AR',
  province text NULL,
  city text NULL,
  address text NULL,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'archived')),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, branch_code)
);

CREATE TRIGGER trg_branches_updated_at
BEFORE UPDATE ON faro.branches
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.areas (
  area_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  area_code text NOT NULL,
  name text NOT NULL,
  parent_area_id uuid NULL REFERENCES faro.areas(area_id),
  area_type text NOT NULL DEFAULT 'operational'
    CHECK (area_type IN ('direction', 'commercial', 'finance', 'operations', 'stock', 'purchasing', 'hr', 'admin', 'it', 'other', 'operational')),
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'archived')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, area_code)
);

CREATE TRIGGER trg_areas_updated_at
BEFORE UPDATE ON faro.areas
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.users (
  user_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  email citext NOT NULL,
  full_name text NOT NULL,
  phone text NULL,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'invited', 'blocked', 'archived')),
  default_branch_id uuid NULL REFERENCES faro.branches(branch_id),
  default_area_id uuid NULL REFERENCES faro.areas(area_id),
  external_ref text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  last_login_at timestamptz NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, email)
);

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON faro.users
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.roles (
  role_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NULL REFERENCES faro.companies(company_id),
  role_code text NOT NULL,
  name text NOT NULL,
  scope text NOT NULL DEFAULT 'company'
    CHECK (scope IN ('system', 'company', 'branch', 'area')),
  description text NULL,
  is_system_role boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, role_code)
);

CREATE TRIGGER trg_roles_updated_at
BEFORE UPDATE ON faro.roles
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.permissions (
  permission_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  permission_code text NOT NULL UNIQUE,
  module_code text NOT NULL,
  action_code text NOT NULL,
  description text NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS faro.user_roles (
  user_role_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  user_id uuid NOT NULL REFERENCES faro.users(user_id),
  role_id uuid NOT NULL REFERENCES faro.roles(role_id),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  area_id uuid NULL REFERENCES faro.areas(area_id),
  assigned_at timestamptz NOT NULL DEFAULT now(),
  assigned_by uuid NULL REFERENCES faro.users(user_id),
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'revoked')),
  UNIQUE (company_id, user_id, role_id, branch_id, area_id)
);

CREATE TABLE IF NOT EXISTS faro.role_permissions (
  role_permission_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  role_id uuid NOT NULL REFERENCES faro.roles(role_id),
  permission_id uuid NOT NULL REFERENCES faro.permissions(permission_id),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (role_id, permission_id)
);

CREATE TABLE IF NOT EXISTS faro.raci_assignments (
  raci_assignment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  entity_type text NOT NULL CHECK (
    entity_type IN ('kpi', 'tension', 'action', 'report', 'source', 'process')
  ),
  entity_code text NOT NULL,
  user_id uuid NULL REFERENCES faro.users(user_id),
  role_id uuid NULL REFERENCES faro.roles(role_id),
  raci_type text NOT NULL CHECK (raci_type IN ('R', 'A', 'C', 'I')),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  area_id uuid NULL REFERENCES faro.areas(area_id),
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive')),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, entity_type, entity_code, user_id, role_id, raci_type)
);
```

---

# 8. V004 · Data Sources, Ingestion and RAW

```sql id="v004"
-- ============================================================
-- FARO-SQL-001 · V004__data_sources_and_ingestion_raw.sql
-- Data sources, fields, ingestion batches, raw records
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.data_sources (
  source_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  source_code text NOT NULL,
  name text NOT NULL,
  source_type text NOT NULL CHECK (
    source_type IN ('erp', 'pos', 'crm', 'excel', 'csv', 'api', 'database', 'bank', 'whatsapp', 'email', 'manual', 'other')
  ),
  system_name text NULL,
  owner_area_id uuid NULL REFERENCES faro.areas(area_id),
  owner_user_id uuid NULL REFERENCES faro.users(user_id),
  format_type text NOT NULL DEFAULT 'unknown'
    CHECK (format_type IN ('csv', 'xlsx', 'json', 'xml', 'pdf', 'api', 'db', 'manual', 'unknown')),
  update_frequency text NOT NULL DEFAULT 'manual'
    CHECK (update_frequency IN ('realtime', 'hourly', 'daily', 'weekly', 'monthly', 'manual', 'unknown')),
  access_method text NULL CHECK (
    access_method IN ('export', 'api', 'db_replica', 'sftp', 'manual_upload', 'email', 'webhook', 'unknown')
  ),
  sensitivity_level text NOT NULL DEFAULT 'medium'
    CHECK (sensitivity_level IN ('low', 'medium', 'high', 'critical')),
  quality_score numeric(5,2) NULL CHECK (quality_score BETWEEN 0 AND 100),
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'blocked', 'pending', 'archived')),
  notes text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, source_code)
);

CREATE TRIGGER trg_data_sources_updated_at
BEFORE UPDATE ON faro.data_sources
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.source_fields (
  source_field_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  source_id uuid NOT NULL REFERENCES faro.data_sources(source_id),
  original_field_name text NOT NULL,
  normalized_field_name text NULL,
  faro_target_table text NULL,
  faro_target_field text NULL,
  data_type_detected text NULL,
  data_type_expected text NULL,
  is_required boolean NOT NULL DEFAULT false,
  is_sensitive boolean NOT NULL DEFAULT false,
  example_value text NULL,
  transformation_rule text NULL,
  dq_rule_code text NULL,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'ignored', 'pending_mapping')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, source_id, original_field_name)
);

CREATE TRIGGER trg_source_fields_updated_at
BEFORE UPDATE ON faro.source_fields
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.ingestion_batches (
  ingestion_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  source_id uuid NOT NULL REFERENCES faro.data_sources(source_id),
  batch_code text NOT NULL,
  file_name text NULL,
  file_type text NULL,
  file_hash text NULL,
  storage_uri text NULL,
  period_start date NULL,
  period_end date NULL,
  received_at timestamptz NOT NULL DEFAULT now(),
  uploaded_by uuid NULL REFERENCES faro.users(user_id),
  records_count integer NULL CHECK (records_count IS NULL OR records_count >= 0),
  raw_records_count integer NOT NULL DEFAULT 0,
  staging_records_count integer NOT NULL DEFAULT 0,
  rejected_records_count integer NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'received' CHECK (
    status IN (
      'received',
      'stored',
      'parsed',
      'failed_parse',
      'loaded_raw',
      'ready_staging',
      'processed_staging',
      'failed',
      'rejected',
      'quarantined',
      'invalidated'
    )
  ),
  error_message text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, batch_code)
);

CREATE TRIGGER trg_ingestion_batches_updated_at
BEFORE UPDATE ON faro.ingestion_batches
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.raw_records (
  raw_record_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  ingestion_id uuid NOT NULL REFERENCES faro.ingestion_batches(ingestion_id),
  source_id uuid NOT NULL REFERENCES faro.data_sources(source_id),
  row_number integer NULL,
  external_record_id text NULL,
  payload jsonb NOT NULL,
  payload_hash text NULL,
  dq_status text NOT NULL DEFAULT 'pending'
    CHECK (dq_status IN ('pending', 'ok', 'warning', 'error', 'fatal')),
  dq_errors jsonb NOT NULL DEFAULT '[]'::jsonb,
  processed_at timestamptz NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, ingestion_id, row_number)
);

CREATE INDEX IF NOT EXISTS idx_raw_records_company_ingestion
ON faro.raw_records (company_id, ingestion_id);

CREATE INDEX IF NOT EXISTS idx_raw_records_payload_gin
ON faro.raw_records USING gin (payload);

CREATE INDEX IF NOT EXISTS idx_raw_records_dq
ON faro.raw_records (company_id, dq_status);
```

---

# 9. V005 · Master Data

```sql id="v005"
-- ============================================================
-- FARO-SQL-001 · V005__master_data.sql
-- Customers, products, suppliers, employees
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.customers (
  customer_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  customer_code text NOT NULL,
  legal_name text NOT NULL,
  display_name text NULL,
  tax_id text NULL,
  customer_type text NOT NULL DEFAULT 'company'
    CHECK (customer_type IN ('person', 'company', 'public_sector', 'other')),
  segment text NULL,
  city text NULL,
  province text NULL,
  country_code text NOT NULL DEFAULT 'AR',
  credit_limit numeric(18,4) NULL,
  payment_terms_days integer NULL,
  risk_level text NULL CHECK (risk_level IN ('low', 'medium', 'high', 'critical')),
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'blocked', 'archived')),
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  external_ref text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, customer_code)
);

CREATE TRIGGER trg_customers_updated_at
BEFORE UPDATE ON faro.customers
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.products (
  product_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  product_code text NOT NULL,
  sku text NULL,
  name text NOT NULL,
  category text NULL,
  subcategory text NULL,
  brand text NULL,
  unit_of_measure text NOT NULL DEFAULT 'unit',
  cost_current numeric(18,4) NULL,
  price_current numeric(18,4) NULL,
  margin_target numeric(9,6) NULL,
  is_stockable boolean NOT NULL DEFAULT true,
  is_active boolean NOT NULL DEFAULT true,
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  external_ref text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, product_code)
);

CREATE TRIGGER trg_products_updated_at
BEFORE UPDATE ON faro.products
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.suppliers (
  supplier_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  supplier_code text NOT NULL,
  legal_name text NOT NULL,
  display_name text NULL,
  tax_id text NULL,
  category text NULL,
  payment_terms_days integer NULL,
  risk_level text NULL CHECK (risk_level IN ('low', 'medium', 'high', 'critical')),
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'blocked', 'archived')),
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  external_ref text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, supplier_code)
);

CREATE TRIGGER trg_suppliers_updated_at
BEFORE UPDATE ON faro.suppliers
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.employees (
  employee_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  employee_code text NOT NULL,
  user_id uuid NULL REFERENCES faro.users(user_id),
  full_name text NOT NULL,
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  area_id uuid NULL REFERENCES faro.areas(area_id),
  job_title text NULL,
  employee_type text NOT NULL DEFAULT 'staff'
    CHECK (employee_type IN ('staff', 'salesperson', 'manager', 'director', 'contractor', 'other')),
  hire_date date NULL,
  termination_date date NULL,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'terminated', 'archived')),
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  external_ref text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz NULL,
  UNIQUE (company_id, employee_code)
);

CREATE TRIGGER trg_employees_updated_at
BEFORE UPDATE ON faro.employees
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();
```

---

# 10. V006 · Staging Tables

```sql id="v006"
-- ============================================================
-- FARO-SQL-001 · V006__staging_tables.sql
-- Normalized staging tables before facts
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.staging_sales (
  staging_sale_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  ingestion_id uuid NOT NULL REFERENCES faro.ingestion_batches(ingestion_id),
  raw_record_id uuid NULL REFERENCES faro.raw_records(raw_record_id),
  source_id uuid NOT NULL REFERENCES faro.data_sources(source_id),
  document_type text NULL,
  document_number text NULL,
  sale_date date NOT NULL,
  branch_code text NULL,
  customer_code text NULL,
  product_code text NULL,
  employee_code text NULL,
  quantity numeric(18,4) NOT NULL DEFAULT 0,
  unit_price numeric(18,4) NULL,
  gross_amount numeric(18,4) NOT NULL DEFAULT 0,
  discount_amount numeric(18,4) NOT NULL DEFAULT 0,
  net_amount numeric(18,4) NOT NULL DEFAULT 0,
  cost_amount numeric(18,4) NULL,
  margin_amount numeric(18,4) NULL,
  currency_code text NOT NULL DEFAULT 'ARS',
  dq_status text NOT NULL DEFAULT 'pending'
    CHECK (dq_status IN ('pending', 'ok', 'warning', 'error', 'fatal')),
  dq_errors jsonb NOT NULL DEFAULT '[]'::jsonb,
  normalized_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS faro.staging_stock (
  staging_stock_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  ingestion_id uuid NOT NULL REFERENCES faro.ingestion_batches(ingestion_id),
  raw_record_id uuid NULL REFERENCES faro.raw_records(raw_record_id),
  source_id uuid NOT NULL REFERENCES faro.data_sources(source_id),
  snapshot_date date NOT NULL,
  branch_code text NULL,
  product_code text NOT NULL,
  quantity_on_hand numeric(18,4) NOT NULL DEFAULT 0,
  quantity_reserved numeric(18,4) NOT NULL DEFAULT 0,
  quantity_available numeric(18,4) NULL,
  unit_cost numeric(18,4) NULL,
  inventory_value numeric(18,4) NULL,
  last_movement_date date NULL,
  dq_status text NOT NULL DEFAULT 'pending'
    CHECK (dq_status IN ('pending', 'ok', 'warning', 'error', 'fatal')),
  dq_errors jsonb NOT NULL DEFAULT '[]'::jsonb,
  normalized_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS faro.staging_receivables (
  staging_receivable_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  ingestion_id uuid NOT NULL REFERENCES faro.ingestion_batches(ingestion_id),
  raw_record_id uuid NULL REFERENCES faro.raw_records(raw_record_id),
  source_id uuid NOT NULL REFERENCES faro.data_sources(source_id),
  document_type text NULL,
  document_number text NOT NULL,
  customer_code text NULL,
  issue_date date NULL,
  due_date date NULL,
  original_amount numeric(18,4) NOT NULL DEFAULT 0,
  open_amount numeric(18,4) NOT NULL DEFAULT 0,
  paid_amount numeric(18,4) NOT NULL DEFAULT 0,
  payment_date date NULL,
  currency_code text NOT NULL DEFAULT 'ARS',
  status text NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'partial', 'paid', 'cancelled', 'written_off')),
  dq_status text NOT NULL DEFAULT 'pending'
    CHECK (dq_status IN ('pending', 'ok', 'warning', 'error', 'fatal')),
  dq_errors jsonb NOT NULL DEFAULT '[]'::jsonb,
  normalized_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS faro.staging_purchases (
  staging_purchase_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  ingestion_id uuid NOT NULL REFERENCES faro.ingestion_batches(ingestion_id),
  raw_record_id uuid NULL REFERENCES faro.raw_records(raw_record_id),
  source_id uuid NOT NULL REFERENCES faro.data_sources(source_id),
  purchase_date date NOT NULL,
  document_type text NULL,
  document_number text NULL,
  supplier_code text NULL,
  product_code text NULL,
  branch_code text NULL,
  quantity numeric(18,4) NOT NULL DEFAULT 0,
  unit_cost numeric(18,4) NULL,
  total_cost numeric(18,4) NOT NULL DEFAULT 0,
  currency_code text NOT NULL DEFAULT 'ARS',
  dq_status text NOT NULL DEFAULT 'pending'
    CHECK (dq_status IN ('pending', 'ok', 'warning', 'error', 'fatal')),
  dq_errors jsonb NOT NULL DEFAULT '[]'::jsonb,
  normalized_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
```

---

# 11. V007 · Fact Tables

```sql id="v007"
-- ============================================================
-- FARO-SQL-001 · V007__fact_tables.sql
-- Fact tables used by KPIs
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.fact_sales (
  sale_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  customer_id uuid NULL REFERENCES faro.customers(customer_id),
  product_id uuid NULL REFERENCES faro.products(product_id),
  employee_id uuid NULL REFERENCES faro.employees(employee_id),
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  ingestion_id uuid NULL REFERENCES faro.ingestion_batches(ingestion_id),
  sale_date date NOT NULL,
  document_type text NULL,
  document_number text NULL,
  line_number integer NULL,
  quantity numeric(18,4) NOT NULL DEFAULT 0,
  gross_amount numeric(18,4) NOT NULL DEFAULT 0,
  discount_amount numeric(18,4) NOT NULL DEFAULT 0,
  net_amount numeric(18,4) NOT NULL DEFAULT 0,
  cost_amount numeric(18,4) NULL,
  margin_amount numeric(18,4) NULL,
  margin_pct numeric(9,6) NULL,
  discount_pct numeric(9,6) NULL,
  currency_code text NOT NULL DEFAULT 'ARS',
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, source_id, document_number, line_number)
);

CREATE TABLE IF NOT EXISTS faro.fact_stock_snapshots (
  stock_snapshot_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  product_id uuid NOT NULL REFERENCES faro.products(product_id),
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  ingestion_id uuid NULL REFERENCES faro.ingestion_batches(ingestion_id),
  snapshot_date date NOT NULL,
  quantity_on_hand numeric(18,4) NOT NULL DEFAULT 0,
  quantity_reserved numeric(18,4) NOT NULL DEFAULT 0,
  quantity_available numeric(18,4) NOT NULL DEFAULT 0,
  unit_cost numeric(18,4) NULL,
  inventory_value numeric(18,4) NULL,
  last_movement_date date NULL,
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, branch_id, product_id, snapshot_date)
);

CREATE TABLE IF NOT EXISTS faro.fact_receivables (
  receivable_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  customer_id uuid NULL REFERENCES faro.customers(customer_id),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  ingestion_id uuid NULL REFERENCES faro.ingestion_batches(ingestion_id),
  document_type text NULL,
  document_number text NOT NULL,
  issue_date date NULL,
  due_date date NULL,
  payment_date date NULL,
  original_amount numeric(18,4) NOT NULL DEFAULT 0,
  open_amount numeric(18,4) NOT NULL DEFAULT 0,
  paid_amount numeric(18,4) NOT NULL DEFAULT 0,
  days_overdue integer NULL,
  status text NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'partial', 'paid', 'cancelled', 'written_off')),
  currency_code text NOT NULL DEFAULT 'ARS',
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, source_id, document_number)
);

CREATE TABLE IF NOT EXISTS faro.fact_purchases (
  purchase_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  supplier_id uuid NULL REFERENCES faro.suppliers(supplier_id),
  product_id uuid NULL REFERENCES faro.products(product_id),
  source_id uuid NULL REFERENCES faro.data_sources(source_id),
  ingestion_id uuid NULL REFERENCES faro.ingestion_batches(ingestion_id),
  purchase_date date NOT NULL,
  document_type text NULL,
  document_number text NULL,
  quantity numeric(18,4) NOT NULL DEFAULT 0,
  unit_cost numeric(18,4) NULL,
  total_cost numeric(18,4) NOT NULL DEFAULT 0,
  currency_code text NOT NULL DEFAULT 'ARS',
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, source_id, document_number, product_id)
);
```

---

# 12. V008 · KPIs, Signals and Rules

```sql id="v008"
-- ============================================================
-- FARO-SQL-001 · V008__kpis_signals_rules.sql
-- KPI definitions, snapshots, signals, rule definitions/evaluations
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.kpi_definitions (
  kpi_definition_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  kpi_code text NOT NULL UNIQUE,
  name text NOT NULL,
  area_code text NULL,
  module_code text NOT NULL,
  description text NULL,
  formula_text text NULL,
  formula_sql text NULL,
  unit text NULL,
  frequency text NOT NULL DEFAULT 'weekly'
    CHECK (frequency IN ('daily', 'weekly', 'monthly', 'quarterly', 'annual', 'on_demand')),
  default_threshold_warning numeric(18,4) NULL,
  default_threshold_critical numeric(18,4) NULL,
  direction text NOT NULL DEFAULT 'neutral'
    CHECK (direction IN ('higher_is_better', 'lower_is_better', 'neutral')),
  is_mvp boolean NOT NULL DEFAULT false,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'draft', 'archived')),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_kpi_definitions_updated_at
BEFORE UPDATE ON faro.kpi_definitions
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.kpi_snapshots (
  kpi_snapshot_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  kpi_code text NOT NULL REFERENCES faro.kpi_definitions(kpi_code),
  period_start date NOT NULL,
  period_end date NOT NULL,
  dimension_type text NOT NULL DEFAULT 'company'
    CHECK (dimension_type IN ('company', 'branch', 'area', 'user', 'employee', 'product', 'customer', 'supplier')),
  dimension_id uuid NULL,
  value numeric(18,6) NOT NULL,
  reference_value numeric(18,6) NULL,
  delta_value numeric(18,6) NULL,
  delta_pct numeric(9,6) NULL,
  status text NOT NULL DEFAULT 'ok'
    CHECK (status IN ('ok', 'warning', 'critical', 'unknown')),
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  source_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
  calculated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, kpi_code, period_start, period_end, dimension_type, dimension_id)
);

CREATE INDEX IF NOT EXISTS idx_kpi_snapshots_company_period
ON faro.kpi_snapshots (company_id, period_end DESC);

CREATE INDEX IF NOT EXISTS idx_kpi_snapshots_code
ON faro.kpi_snapshots (kpi_code, period_end DESC);

CREATE TABLE IF NOT EXISTS faro.signal_log (
  signal_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  kpi_snapshot_id uuid NULL REFERENCES faro.kpi_snapshots(kpi_snapshot_id),
  signal_code text NOT NULL,
  title text NOT NULL,
  description text NULL,
  signal_type text NOT NULL DEFAULT 'threshold'
    CHECK (signal_type IN ('threshold', 'trend', 'anomaly', 'missing_data', 'manual', 'composite')),
  severity text NOT NULL DEFAULT 'medium'
    CHECK (severity IN ('low', 'medium', 'high', 'critical')),
  value numeric(18,6) NULL,
  threshold numeric(18,6) NULL,
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  status text NOT NULL DEFAULT 'new'
    CHECK (status IN ('new', 'evaluated', 'ignored', 'expired')),
  detected_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_signal_log_company_detected
ON faro.signal_log (company_id, detected_at DESC);

CREATE TABLE IF NOT EXISTS faro.rule_definitions (
  rule_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NULL REFERENCES faro.companies(company_id),
  rule_code text NOT NULL,
  name text NOT NULL,
  description text NULL,
  rule_type text NOT NULL DEFAULT 'tension'
    CHECK (rule_type IN ('alert', 'tension', 'score', 'workflow', 'data_quality')),
  rule_format text NOT NULL DEFAULT 'yaml'
    CHECK (rule_format IN ('yaml', 'json', 'sql', 'js')),
  rule_body jsonb NOT NULL,
  severity_default text NOT NULL DEFAULT 'medium'
    CHECK (severity_default IN ('low', 'medium', 'high', 'critical')),
  is_mvp boolean NOT NULL DEFAULT false,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'draft', 'archived')),
  version integer NOT NULL DEFAULT 1,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, rule_code, version)
);

CREATE TRIGGER trg_rule_definitions_updated_at
BEFORE UPDATE ON faro.rule_definitions
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.rule_evaluations (
  rule_evaluation_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  rule_id uuid NOT NULL REFERENCES faro.rule_definitions(rule_id),
  period_start date NULL,
  period_end date NULL,
  evaluated_at timestamptz NOT NULL DEFAULT now(),
  result boolean NOT NULL DEFAULT false,
  severity text NULL CHECK (severity IN ('low', 'medium', 'high', 'critical')),
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  input_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  output_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  status text NOT NULL DEFAULT 'completed'
    CHECK (status IN ('completed', 'failed', 'skipped')),
  error_message text NULL
);
```

---

# 13. V009 · Tensions, Actions and Evidence

```sql id="v009"
-- ============================================================
-- FARO-SQL-001 · V009__tensions_actions_evidence.sql
-- Tensions, actions, workflow, evidence
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.tensions (
  tension_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  rule_evaluation_id uuid NULL REFERENCES faro.rule_evaluations(rule_evaluation_id),
  tension_code text NOT NULL,
  title text NOT NULL,
  description text NULL,
  area_id uuid NULL REFERENCES faro.areas(area_id),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  responsible_user_id uuid NULL REFERENCES faro.users(user_id),
  severity text NOT NULL DEFAULT 'medium'
    CHECK (severity IN ('low', 'medium', 'high', 'critical')),
  priority_score numeric(6,2) NULL CHECK (priority_score BETWEEN 0 AND 100),
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  status text NOT NULL DEFAULT 'new'
    CHECK (status IN ('new', 'in_analysis', 'in_execution', 'in_verification', 'closed', 'rejected', 'expired', 'escalated')),
  detected_at timestamptz NOT NULL DEFAULT now(),
  due_at timestamptz NULL,
  closed_at timestamptz NULL,
  score_impact numeric(9,4) NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_tensions_updated_at
BEFORE UPDATE ON faro.tensions
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE INDEX IF NOT EXISTS idx_tensions_company_status
ON faro.tensions (company_id, status, detected_at DESC);

CREATE INDEX IF NOT EXISTS idx_tensions_responsible
ON faro.tensions (company_id, responsible_user_id, status);

CREATE TABLE IF NOT EXISTS faro.actions (
  action_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  tension_id uuid NULL REFERENCES faro.tensions(tension_id),
  action_code text NULL,
  title text NOT NULL,
  description text NULL,
  action_type text NOT NULL DEFAULT 'corrective'
    CHECK (action_type IN ('corrective', 'preventive', 'follow_up', 'approval', 'analysis', 'manual')),
  responsible_user_id uuid NULL REFERENCES faro.users(user_id),
  approver_user_id uuid NULL REFERENCES faro.users(user_id),
  area_id uuid NULL REFERENCES faro.areas(area_id),
  branch_id uuid NULL REFERENCES faro.branches(branch_id),
  status text NOT NULL DEFAULT 'new'
    CHECK (status IN ('new', 'in_analysis', 'in_execution', 'in_verification', 'closed', 'cancelled', 'rejected', 'expired', 'escalated', 'rescheduled')),
  priority text NOT NULL DEFAULT 'medium'
    CHECK (priority IN ('low', 'medium', 'high', 'critical')),
  due_date date NULL,
  started_at timestamptz NULL,
  completed_at timestamptz NULL,
  closed_at timestamptz NULL,
  expected_impact text NULL,
  expected_impact_amount numeric(18,4) NULL,
  evidence_required boolean NOT NULL DEFAULT true,
  closure_criteria text NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by uuid NULL REFERENCES faro.users(user_id),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_actions_updated_at
BEFORE UPDATE ON faro.actions
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE INDEX IF NOT EXISTS idx_actions_company_status
ON faro.actions (company_id, status, due_date);

CREATE INDEX IF NOT EXISTS idx_actions_responsible
ON faro.actions (company_id, responsible_user_id, status);

CREATE TABLE IF NOT EXISTS faro.evidence (
  evidence_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  action_id uuid NULL REFERENCES faro.actions(action_id),
  tension_id uuid NULL REFERENCES faro.tensions(tension_id),
  evidence_code text NULL,
  evidence_type text NOT NULL CHECK (
    evidence_type IN (
      'document',
      'screenshot',
      'approval',
      'comment',
      'purchase_order',
      'invoice',
      'payment_receipt',
      'policy_change',
      'kpi_after',
      'meeting_minutes',
      'external_confirmation',
      'director_validation',
      'other'
    )
  ),
  title text NOT NULL,
  description text NULL,
  storage_uri text NULL,
  file_name text NULL,
  file_hash text NULL,
  submitted_by uuid NULL REFERENCES faro.users(user_id),
  submitted_at timestamptz NOT NULL DEFAULT now(),
  reviewed_by uuid NULL REFERENCES faro.users(user_id),
  reviewed_at timestamptz NULL,
  status text NOT NULL DEFAULT 'submitted'
    CHECK (status IN ('submitted', 'approved', 'rejected', 'needs_more_info', 'archived')),
  review_comment text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_evidence_company_action
ON faro.evidence (company_id, action_id);

CREATE TABLE IF NOT EXISTS faro.action_status_history (
  action_status_history_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  action_id uuid NOT NULL REFERENCES faro.actions(action_id),
  previous_status text NULL,
  new_status text NOT NULL,
  changed_by uuid NULL REFERENCES faro.users(user_id),
  change_reason text NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  changed_at timestamptz NOT NULL DEFAULT now()
);
```

---

# 14. V010 · FARO Score and Reports

```sql id="v010"
-- ============================================================
-- FARO-SQL-001 · V010__score_reports.sql
-- FARO Score snapshots and reports
-- ============================================================

CREATE TABLE IF NOT EXISTS faro.score_snapshots (
  score_snapshot_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  period_start date NOT NULL,
  period_end date NOT NULL,
  dimension_type text NOT NULL DEFAULT 'company'
    CHECK (dimension_type IN ('company', 'branch', 'area', 'user', 'employee')),
  dimension_id uuid NULL,
  score_value numeric(6,2) NOT NULL CHECK (score_value BETWEEN 0 AND 100),
  previous_score_value numeric(6,2) NULL CHECK (previous_score_value BETWEEN 0 AND 100),
  delta_value numeric(6,2) NULL,
  score_status text NOT NULL DEFAULT 'medium'
    CHECK (score_status IN ('excellent', 'good', 'medium', 'warning', 'critical')),
  confidence_score numeric(5,2) NULL CHECK (confidence_score BETWEEN 0 AND 100),
  components jsonb NOT NULL DEFAULT '{}'::jsonb,
  explanation text NULL,
  calculated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, period_start, period_end, dimension_type, dimension_id)
);

CREATE INDEX IF NOT EXISTS idx_score_snapshots_company_period
ON faro.score_snapshots (company_id, period_end DESC);

CREATE TABLE IF NOT EXISTS faro.reports (
  report_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  report_code text NOT NULL,
  report_type text NOT NULL DEFAULT 'weekly_executive'
    CHECK (report_type IN ('daily_summary', 'weekly_executive', 'monthly_board', 'pilot_close', 'custom')),
  title text NOT NULL,
  period_start date NOT NULL,
  period_end date NOT NULL,
  status text NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'generated', 'sent', 'archived', 'failed')),
  score_snapshot_id uuid NULL REFERENCES faro.score_snapshots(score_snapshot_id),
  content jsonb NOT NULL DEFAULT '{}'::jsonb,
  pdf_uri text NULL,
  html_uri text NULL,
  generated_by uuid NULL REFERENCES faro.users(user_id),
  generated_at timestamptz NULL,
  sent_at timestamptz NULL,
  recipients jsonb NOT NULL DEFAULT '[]'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, report_code)
);

CREATE TRIGGER trg_reports_updated_at
BEFORE UPDATE ON faro.reports
FOR EACH ROW EXECUTE FUNCTION faro.set_updated_at();

CREATE TABLE IF NOT EXISTS faro.notifications (
  notification_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES faro.companies(company_id),
  user_id uuid NULL REFERENCES faro.users(user_id),
  related_entity_type text NULL,
  related_entity_id uuid NULL,
  channel text NOT NULL DEFAULT 'email'
    CHECK (channel IN ('email', 'web', 'slack', 'whatsapp', 'push')),
  notification_type text NOT NULL,
  title text NOT NULL,
  body text NULL,
  status text NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'sent', 'failed', 'read', 'archived')),
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  scheduled_at timestamptz NULL,
  sent_at timestamptz NULL,
  read_at timestamptz NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
```

---

# 15. V011 · Indexes and Constraints

```sql id="v011"
-- ============================================================
-- FARO-SQL-001 · V011__indexes_constraints.sql
-- Performance indexes and integrity helpers
-- ============================================================

-- Organization
CREATE INDEX IF NOT EXISTS idx_branches_company_status
ON faro.branches (company_id, status);

CREATE INDEX IF NOT EXISTS idx_areas_company_type
ON faro.areas (company_id, area_type);

CREATE INDEX IF NOT EXISTS idx_users_company_status
ON faro.users (company_id, status);

-- Master data
CREATE INDEX IF NOT EXISTS idx_customers_company_status
ON faro.customers (company_id, status);

CREATE INDEX IF NOT EXISTS idx_products_company_active
ON faro.products (company_id, is_active);

CREATE INDEX IF NOT EXISTS idx_suppliers_company_status
ON faro.suppliers (company_id, status);

CREATE INDEX IF NOT EXISTS idx_employees_company_status
ON faro.employees (company_id, status);

-- Staging
CREATE INDEX IF NOT EXISTS idx_staging_sales_company_date
ON faro.staging_sales (company_id, sale_date);

CREATE INDEX IF NOT EXISTS idx_staging_sales_dq
ON faro.staging_sales (company_id, dq_status);

CREATE INDEX IF NOT EXISTS idx_staging_stock_company_date
ON faro.staging_stock (company_id, snapshot_date);

CREATE INDEX IF NOT EXISTS idx_staging_receivables_company_due
ON faro.staging_receivables (company_id, due_date);

-- Facts
CREATE INDEX IF NOT EXISTS idx_fact_sales_company_date
ON faro.fact_sales (company_id, sale_date);

CREATE INDEX IF NOT EXISTS idx_fact_sales_company_branch_date
ON faro.fact_sales (company_id, branch_id, sale_date);

CREATE INDEX IF NOT EXISTS idx_fact_sales_company_employee_date
ON faro.fact_sales (company_id, employee_id, sale_date);

CREATE INDEX IF NOT EXISTS idx_fact_sales_company_product_date
ON faro.fact_sales (company_id, product_id, sale_date);

CREATE INDEX IF NOT EXISTS idx_fact_stock_company_date
ON faro.fact_stock_snapshots (company_id, snapshot_date);

CREATE INDEX IF NOT EXISTS idx_fact_receivables_company_due
ON faro.fact_receivables (company_id, due_date, status);

CREATE INDEX IF NOT EXISTS idx_fact_purchases_company_date
ON faro.fact_purchases (company_id, purchase_date);

-- Rules and workflow
CREATE INDEX IF NOT EXISTS idx_rule_evaluations_company_rule
ON faro.rule_evaluations (company_id, rule_id, evaluated_at DESC);

CREATE INDEX IF NOT EXISTS idx_reports_company_period
ON faro.reports (company_id, period_end DESC);

CREATE INDEX IF NOT EXISTS idx_notifications_user_status
ON faro.notifications (company_id, user_id, status, created_at DESC);
```

---

# 16. V012 · Seed Minimal Catalogs

```sql id="v012"
-- ============================================================
-- FARO-SQL-001 · V012__seed_minimal_catalogs.sql
-- Minimal catalogs for MVP
-- ============================================================

INSERT INTO faro.permissions (permission_code, module_code, action_code, description)
VALUES
  ('companies.read', 'companies', 'read', 'Read company data'),
  ('sources.manage', 'sources', 'manage', 'Manage data sources'),
  ('ingestion.run', 'ingestion', 'run', 'Run ingestion processes'),
  ('kpis.read', 'kpis', 'read', 'Read KPI snapshots'),
  ('rules.manage', 'rules', 'manage', 'Manage rules'),
  ('tensions.read', 'tensions', 'read', 'Read tensions'),
  ('tensions.manage', 'tensions', 'manage', 'Manage tensions'),
  ('actions.read', 'actions', 'read', 'Read actions'),
  ('actions.manage', 'actions', 'manage', 'Manage actions'),
  ('evidence.upload', 'evidence', 'upload', 'Upload evidence'),
  ('score.read', 'score', 'read', 'Read FARO Score'),
  ('reports.read', 'reports', 'read', 'Read reports'),
  ('reports.generate', 'reports', 'generate', 'Generate reports'),
  ('admin.full', 'admin', 'full', 'Full administration')
ON CONFLICT (permission_code) DO NOTHING;

INSERT INTO faro.kpi_definitions (
  kpi_code,
  name,
  area_code,
  module_code,
  description,
  formula_text,
  unit,
  frequency,
  direction,
  is_mvp,
  status
)
VALUES
  (
    'KPI-SAL-001',
    'Ventas netas',
    'commercial',
    'sales',
    'Total de ventas netas del período',
    'SUM(net_amount)',
    'currency',
    'weekly',
    'higher_is_better',
    true,
    'active'
  ),
  (
    'KPI-SAL-002',
    'Margen bruto',
    'commercial',
    'sales',
    'Margen bruto sobre ventas netas',
    'SUM(margin_amount) / NULLIF(SUM(net_amount), 0)',
    'percentage',
    'weekly',
    'higher_is_better',
    true,
    'active'
  ),
  (
    'KPI-SAL-003',
    'Descuento promedio',
    'commercial',
    'sales',
    'Descuento promedio sobre ventas brutas',
    'SUM(discount_amount) / NULLIF(SUM(gross_amount), 0)',
    'percentage',
    'weekly',
    'lower_is_better',
    true,
    'active'
  ),
  (
    'KPI-FIN-001',
    'Días de cobranza',
    'finance',
    'receivables',
    'Promedio de días de cobranza o atraso',
    'AVG(days_overdue)',
    'days',
    'weekly',
    'lower_is_better',
    true,
    'active'
  ),
  (
    'KPI-FIN-002',
    'Mora vencida',
    'finance',
    'receivables',
    'Total de deuda vencida abierta',
    'SUM(open_amount) WHERE due_date < CURRENT_DATE AND status <> paid',
    'currency',
    'weekly',
    'lower_is_better',
    true,
    'active'
  ),
  (
    'KPI-STK-001',
    'Stock crítico',
    'stock',
    'stock',
    'Cantidad de productos con cobertura inferior al mínimo',
    'COUNT(products WHERE coverage_days < threshold)',
    'count',
    'weekly',
    'lower_is_better',
    true,
    'active'
  ),
  (
    'KPI-STK-002',
    'Stock inmovilizado',
    'stock',
    'stock',
    'Valor de inventario sin rotación',
    'SUM(inventory_value WHERE days_without_movement > threshold)',
    'currency',
    'weekly',
    'lower_is_better',
    true,
    'active'
  ),
  (
    'KPI-ACT-001',
    'Acciones vencidas',
    'direction',
    'actions',
    'Cantidad de acciones vencidas sin cierre',
    'COUNT(actions WHERE due_date < CURRENT_DATE AND status NOT IN closed/cancelled)',
    'count',
    'weekly',
    'lower_is_better',
    true,
    'active'
  )
ON CONFLICT (kpi_code) DO NOTHING;
```

---

# 17. Funciones SQL MVP recomendadas

Estas funciones pueden ir en una migración posterior, por ejemplo:

```text id="14naro"
V013__mvp_kpi_functions.sql
```

## 17.1 KPI ventas netas

```sql id="fn_sales"
CREATE OR REPLACE FUNCTION faro.calculate_kpi_sales_net(
  p_company_id uuid,
  p_period_start date,
  p_period_end date
)
RETURNS numeric AS $$
DECLARE
  v_result numeric(18,6);
BEGIN
  SELECT COALESCE(SUM(net_amount), 0)
  INTO v_result
  FROM faro.fact_sales
  WHERE company_id = p_company_id
    AND sale_date BETWEEN p_period_start AND p_period_end;

  RETURN v_result;
END;
$$ LANGUAGE plpgsql;
```

## 17.2 KPI margen bruto

```sql id="fn_margin"
CREATE OR REPLACE FUNCTION faro.calculate_kpi_gross_margin_pct(
  p_company_id uuid,
  p_period_start date,
  p_period_end date
)
RETURNS numeric AS $$
DECLARE
  v_result numeric(18,6);
BEGIN
  SELECT
    CASE
      WHEN COALESCE(SUM(net_amount), 0) = 0 THEN 0
      ELSE COALESCE(SUM(margin_amount), 0) / NULLIF(SUM(net_amount), 0)
    END
  INTO v_result
  FROM faro.fact_sales
  WHERE company_id = p_company_id
    AND sale_date BETWEEN p_period_start AND p_period_end;

  RETURN v_result;
END;
$$ LANGUAGE plpgsql;
```

## 17.3 KPI descuento promedio

```sql id="fn_discount"
CREATE OR REPLACE FUNCTION faro.calculate_kpi_avg_discount_pct(
  p_company_id uuid,
  p_period_start date,
  p_period_end date
)
RETURNS numeric AS $$
DECLARE
  v_result numeric(18,6);
BEGIN
  SELECT
    CASE
      WHEN COALESCE(SUM(gross_amount), 0) = 0 THEN 0
      ELSE COALESCE(SUM(discount_amount), 0) / NULLIF(SUM(gross_amount), 0)
    END
  INTO v_result
  FROM faro.fact_sales
  WHERE company_id = p_company_id
    AND sale_date BETWEEN p_period_start AND p_period_end;

  RETURN v_result;
END;
$$ LANGUAGE plpgsql;
```

## 17.4 KPI mora vencida

```sql id="fn_overdue"
CREATE OR REPLACE FUNCTION faro.calculate_kpi_overdue_amount(
  p_company_id uuid,
  p_as_of_date date
)
RETURNS numeric AS $$
DECLARE
  v_result numeric(18,6);
BEGIN
  SELECT COALESCE(SUM(open_amount), 0)
  INTO v_result
  FROM faro.fact_receivables
  WHERE company_id = p_company_id
    AND due_date < p_as_of_date
    AND status IN ('open', 'partial');

  RETURN v_result;
END;
$$ LANGUAGE plpgsql;
```

---

# 18. Función conceptual para crear snapshots KPI

```sql id="fn_snapshot"
CREATE OR REPLACE FUNCTION faro.insert_kpi_snapshot(
  p_company_id uuid,
  p_kpi_code text,
  p_period_start date,
  p_period_end date,
  p_dimension_type text,
  p_dimension_id uuid,
  p_value numeric,
  p_reference_value numeric DEFAULT NULL,
  p_confidence_score numeric DEFAULT NULL
)
RETURNS uuid AS $$
DECLARE
  v_snapshot_id uuid;
  v_delta numeric;
  v_delta_pct numeric;
  v_status text;
BEGIN
  v_delta := CASE
    WHEN p_reference_value IS NULL THEN NULL
    ELSE p_value - p_reference_value
  END;

  v_delta_pct := CASE
    WHEN p_reference_value IS NULL OR p_reference_value = 0 THEN NULL
    ELSE (p_value - p_reference_value) / p_reference_value
  END;

  v_status := 'ok';

  INSERT INTO faro.kpi_snapshots (
    company_id,
    kpi_code,
    period_start,
    period_end,
    dimension_type,
    dimension_id,
    value,
    reference_value,
    delta_value,
    delta_pct,
    status,
    confidence_score
  )
  VALUES (
    p_company_id,
    p_kpi_code,
    p_period_start,
    p_period_end,
    p_dimension_type,
    p_dimension_id,
    p_value,
    p_reference_value,
    v_delta,
    v_delta_pct,
    v_status,
    p_confidence_score
  )
  ON CONFLICT (
    company_id,
    kpi_code,
    period_start,
    period_end,
    dimension_type,
    dimension_id
  )
  DO UPDATE SET
    value = EXCLUDED.value,
    reference_value = EXCLUDED.reference_value,
    delta_value = EXCLUDED.delta_value,
    delta_pct = EXCLUDED.delta_pct,
    status = EXCLUDED.status,
    confidence_score = EXCLUDED.confidence_score,
    calculated_at = now()
  RETURNING kpi_snapshot_id INTO v_snapshot_id;

  RETURN v_snapshot_id;
END;
$$ LANGUAGE plpgsql;
```

---

# 19. Reglas de integridad mínima

## 19.1 Toda tabla operativa debe tener `company_id`

Obligatorio para:

* fuentes;
* ingestas;
* RAW;
* staging;
* maestros;
* facts;
* KPIs;
* señales;
* reglas;
* tensiones;
* acciones;
* evidencia;
* score;
* reportes.

## 19.2 RAW debe poder reprocesarse

Cada registro RAW debe conservar:

* `ingestion_id`;
* `source_id`;
* `payload`;
* `payload_hash`;
* `row_number`;
* `dq_status`;
* `dq_errors`.

## 19.3 Facts no deben depender de nombres libres

Facts deben vincular contra:

* `customer_id`;
* `product_id`;
* `employee_id`;
* `branch_id`;
* `supplier_id`.

Cuando no se pueda vincular, el registro puede entrar con warning, pero debe quedar registrado el problema.

## 19.4 Acciones no deben cerrarse sin evidencia si `evidence_required = true`

Esta regla puede implementarse luego como constraint, trigger o validación de backend.

---

# 20. RLS recomendado para FARO-SQL-002

Este documento prepara la base para RLS, pero la implementación formal queda para FARO-SQL-002.

Modelo recomendado:

```sql id="rls_ref"
ALTER TABLE faro.fact_sales ENABLE ROW LEVEL SECURITY;

CREATE POLICY company_isolation_fact_sales
ON faro.fact_sales
USING (
  company_id = current_setting('app.company_id')::uuid
);
```

Se debe repetir para:

* `data_sources`;
* `ingestion_batches`;
* `raw_records`;
* `staging_*`;
* `master_data`;
* `fact_*`;
* `kpi_snapshots`;
* `signal_log`;
* `tensions`;
* `actions`;
* `evidence`;
* `score_snapshots`;
* `reports`.

---

# 21. Validaciones mínimas para QA

## 21.1 Validaciones de estructura

```sql id="qa_tables"
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'faro'
ORDER BY table_name;
```

Debe devolver tablas principales:

```text id="qa_tables_expected"
actions
areas
branches
companies
customers
data_sources
employees
evidence
fact_purchases
fact_receivables
fact_sales
fact_stock_snapshots
ingestion_batches
kpi_definitions
kpi_snapshots
notifications
permissions
products
raci_assignments
raw_records
reports
role_permissions
roles
rule_definitions
rule_evaluations
score_snapshots
signal_log
source_fields
staging_purchases
staging_receivables
staging_sales
staging_stock
suppliers
tensions
user_roles
users
```

## 21.2 Validar company_id en tablas críticas

```sql id="qa_company_id"
SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'faro'
  AND column_name = 'company_id'
ORDER BY table_name;
```

## 21.3 Validar KPIs seed

```sql id="qa_kpi_seed"
SELECT kpi_code, name, is_mvp, status
FROM faro.kpi_definitions
ORDER BY kpi_code;
```

---

# 22. Criterios de aceptación de FARO-SQL-001

La migración base se considera aceptada si:

| Criterio                          | Estado esperado |
| --------------------------------- | --------------- |
| Crea schema `faro`                | Sí              |
| Crea schema `audit`               | Sí              |
| Crea extensiones base             | Sí              |
| Crea organización multiempresa    | Sí              |
| Crea usuarios, roles y permisos   | Sí              |
| Crea fuentes e inventario técnico | Sí              |
| Crea ingesta y RAW                | Sí              |
| Crea staging por dominio MVP      | Sí              |
| Crea maestros principales         | Sí              |
| Crea facts principales            | Sí              |
| Crea KPIs y señales               | Sí              |
| Crea reglas y evaluaciones        | Sí              |
| Crea tensiones                    | Sí              |
| Crea acciones                     | Sí              |
| Crea evidencia                    | Sí              |
| Crea score                        | Sí              |
| Crea reportes                     | Sí              |
| Crea índices principales          | Sí              |
| Inserta KPIs mínimos MVP          | Sí              |
| Prepara base para RLS             | Sí              |

---

# 23. Lo que NO incluye FARO-SQL-001

Para evitar inflar el MVP, esta migración no incluye aún:

| Elemento               | Motivo                             | Próximo activo               |
| ---------------------- | ---------------------------------- | ---------------------------- |
| RLS completo por tabla | Se trabaja en documento específico | FARO-SQL-002                 |
| Seeds demo completos   | Requieren dataset integral         | FARO-SQL-003 / FARO-DEMO-001 |
| DSL completo de reglas | Va como config/código              | FARO-CFG-001 / FARO-ENG-002  |
| Motor evaluador        | No corresponde al SQL base         | FARO-ENG-003                 |
| UI                     | No corresponde al SQL              | FARO-UI-001                  |
| Reporte PDF            | Solo se deja tabla `reports`       | FARO-TPL-002                 |
| IA / embeddings        | Fuera del MVP inicial              | FARO-AI                      |
| Peer comparison        | Requiere múltiples clientes        | Fase Enterprise              |
| Simulación avanzada    | Requiere histórico validado        | Fase P3                      |

---

# 24. Próximo paso técnico

Después de FARO-SQL-001, corresponde construir:

## FARO-SQL-002 · Multiempresa, roles y RLS

Objetivo:

* activar Row Level Security;
* aislar empresas por `company_id`;
* definir policies;
* preparar contexto `app.company_id`;
* validar con dos tenants;
* asegurar que ningún usuario vea datos de otra empresa.

Sin FARO-SQL-002, la base existe, pero todavía no está lista para operar múltiples clientes con seguridad real.

---

# 25. Frase ejecutiva

FARO-SQL-001 convierte FARO Connect de arquitectura conceptual en base construible.

A partir de esta migración, el sistema ya tiene dónde guardar:

```text id="7fxe4v"
datos,
errores,
KPIs,
señales,
tensiones,
acciones,
evidencias,
score,
reportes
y auditoría.
```

Eso es el esqueleto. Todavía no camina solo, pero ya no es humo.