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

**Código:** FARO-SQL-002
**Nombre:** Multiempresa, Roles, Permisos y Row Level Security FARO Connect
**Versión:** v1.0
**Estado:** Base técnica de seguridad MVP
**Prioridad:** P1 · Crítico para piloto multiempresa
**Motor recomendado:** PostgreSQL 15+
**Formato:** SQL versionado + políticas RLS + tests de aislamiento
**Uso:** CTO · backend developer · data engineer · seguridad · socio técnico
**Depende de:** FARO-SQL-001 · Migraciones Base MVP
**Conecta con:**

* FARO-DOC-002 · Runbook de Implementación Piloto
* FARO-SQL-003 · Seeds Empresa Demo
* FARO-ENG-003 · Motor Evaluador MVP
* FARO-UI-001 · Bandeja de Tensiones
* FARO-TEST-002 · Tests de Reglas y Seguridad

---

# 1. Objetivo

El objetivo de FARO-SQL-002 es implementar la capa mínima de seguridad multiempresa para FARO Connect.

FARO debe poder operar con varias empresas/clientes sin que una vea, consulte, procese, modifique o exporte datos de otra.

Esto se logra con:

1. modelo multiempresa por `company_id`;
2. usuarios asociados a empresa;
3. roles y permisos;
4. contexto seguro de sesión;
5. Row Level Security en PostgreSQL;
6. funciones helper para obtener contexto;
7. policies por tabla;
8. tests con dos tenants;
9. restricciones de escritura;
10. auditoría de acciones sensibles.

La regla madre:

```text
Ninguna consulta operativa debe confiar solamente en el backend.
La base también debe defenderse.
```

Si un bug de backend arma mal un query, RLS debe seguir protegiendo los datos. Cinturón y tirantes. Tradicional, aburrido y correcto.

---

# 2. Tesis técnica

FARO Connect debe nacer multiempresa.

Aunque el primer piloto tenga un solo cliente, el diseño debe soportar:

* varios clientes;
* varias empresas por grupo;
* varias sucursales;
* varios roles;
* varios niveles de acceso;
* información sensible diferenciada;
* auditoría;
* expansión futura a Enterprise.

El error común es decir:

> “Primero hacemos simple, después agregamos seguridad.”

Eso suele terminar en cirugía mayor con anestesia escasa. FARO debe incorporar seguridad desde el inicio, aunque al principio se active en modo simple.

---

# 3. Alcance de FARO-SQL-002

## 3.1 Incluye

| Componente                     | Incluido |
| ------------------------------ | -------: |
| Contexto `app.company_id`      |       Sí |
| Contexto `app.user_id`         |       Sí |
| Contexto `app.role_codes`      |       Sí |
| Funciones helper de sesión     |       Sí |
| RLS para tablas multiempresa   |       Sí |
| Policies SELECT                |       Sí |
| Policies INSERT                |       Sí |
| Policies UPDATE                |       Sí |
| Policies DELETE restrictivo    |       Sí |
| Roles de aplicación PostgreSQL |       Sí |
| Grants mínimos                 |       Sí |
| Tests con dos tenants          |       Sí |
| Vista de diagnóstico RLS       |       Sí |
| Auditoría complementaria       |       Sí |
| Criterios de aceptación        |       Sí |

## 3.2 No incluye

| Componente                       | Motivo                             | Próximo activo |
| -------------------------------- | ---------------------------------- | -------------- |
| Autenticación completa JWT/OAuth | Depende del backend                | FARO-AUTH-001  |
| UI de administración de usuarios | Frontend posterior                 | FARO-UI-ADMIN  |
| ABAC avanzado                    | Fase Enterprise                    | FARO-SEC-003   |
| RLS por columna                  | Se resuelve parcialmente con views | FARO-SEC-004   |
| Encriptación campo a campo       | Se define según sensibilidad       | FARO-SEC-005   |
| Auditoría legal avanzada         | Fase posterior                     | FARO-AUDIT-002 |

---

# 4. Arquitectura de seguridad recomendada

## 4.1 Capas de defensa

```text
Usuario autenticado
→ Backend valida sesión
→ Backend define contexto app.company_id / app.user_id
→ PostgreSQL aplica RLS
→ Policies filtran por company_id
→ Roles/permissions limitan acción
→ Auditoría registra operación
```

## 4.2 Responsabilidad por capa

| Capa       | Responsabilidad                              |
| ---------- | -------------------------------------------- |
| Frontend   | No mostrar lo que el usuario no debería usar |
| Backend    | Validar sesión, rol, payload y permisos      |
| PostgreSQL | Aislar datos por empresa con RLS             |
| Auditoría  | Registrar acciones sensibles                 |
| Reportes   | No mezclar empresas ni dimensiones           |
| IA         | Recibir solo datos ya autorizados            |

---

# 5. Modelo de roles funcionales FARO

## 5.1 Roles base MVP

| Código                | Nombre                | Descripción                                  |
| --------------------- | --------------------- | -------------------------------------------- |
| `faro_owner`          | Owner FARO            | Control interno máximo FARO                  |
| `company_admin`       | Administrador empresa | Administra usuarios, fuentes y configuración |
| `director`            | Director              | Ve información ejecutiva completa            |
| `general_manager`     | Gerente General       | Ve operación integral y acciones             |
| `area_manager`        | Responsable de Área   | Ve su área y acciones asignadas              |
| `branch_manager`      | Responsable Sucursal  | Ve su sucursal                               |
| `finance_user`        | Usuario Finanzas      | Ve cobranza, caja, reportes financieros      |
| `commercial_user`     | Usuario Comercial     | Ve ventas, clientes y acciones comerciales   |
| `stock_user`          | Usuario Stock         | Ve inventario, quiebres, reposición          |
| `hr_user`             | Usuario RRHH          | Ve RRHH si está habilitado                   |
| `viewer`              | Lector                | Solo lectura limitada                        |
| `external_auditor`    | Auditor externo       | Lectura auditada y restringida               |
| `integration_service` | Servicio integración  | Carga datos, no opera UI                     |

## 5.2 Niveles de alcance

| Scope     | Descripción                      |
| --------- | -------------------------------- |
| `system`  | Nivel FARO interno               |
| `company` | Toda la empresa cliente          |
| `branch`  | Solo sucursal asignada           |
| `area`    | Solo área asignada               |
| `own`     | Solo registros propios/asignados |
| `service` | Servicio técnico, sin UI         |

---

# 6. Matriz de permisos MVP

| Módulo         | company_admin |     director | general_manager |  area_manager | branch_manager |       viewer | integration_service |
| -------------- | ------------: | -----------: | --------------: | ------------: | -------------: | -----------: | ------------------: |
| Empresas       |         Admin |         Read |            Read |            No |             No |           No |                  No |
| Usuarios       |         Admin |         Read |            Read |            No |             No |           No |                  No |
| Fuentes        |         Admin |         Read |            Read |            No |             No | Read parcial |       Write técnico |
| RAW            |          Read |           No |              No |            No |             No |           No |               Write |
| Staging        |          Read |           No |              No |            No |             No |           No |               Write |
| Maestros       |         Admin |         Read |            Read |  Read parcial |   Read parcial | Read parcial |               Write |
| Facts ventas   |          Read |         Read |            Read |          Área |       Sucursal |      Parcial |               Write |
| Facts cobranza |          Read |         Read |            Read |      Finanzas |     No/parcial |           No |               Write |
| KPIs           |          Read |         Read |            Read |          Área |       Sucursal |      Parcial |                  No |
| Señales        |          Read |         Read |            Read |          Área |       Sucursal |      Parcial |                  No |
| Tensiones      |         Admin |         Read |          Manage | Assigned/Área |       Sucursal | Read parcial |                  No |
| Acciones       |         Admin |         Read |          Manage | Assigned/Área |       Sucursal | Read parcial |                  No |
| Evidencia      |         Admin |         Read |          Manage |      Assigned |       Assigned | Read parcial |                  No |
| Score          |          Read |         Read |            Read |          Área |       Sucursal |      Parcial |                  No |
| Reportes       |         Admin |         Read |            Read |          Área |       Sucursal |      Parcial |                  No |
| Auditoría      |          Read | Read parcial |      No/parcial |            No |             No |           No |                  No |

---

# 7. Estructura sugerida de migraciones

```text
migrations/
  V014__security_context_functions.sql
  V015__application_db_roles_and_grants.sql
  V016__seed_roles_permissions.sql
  V017__enable_rls_core_tables.sql
  V018__rls_policies_org_sources_raw.sql
  V019__rls_policies_master_staging_facts.sql
  V020__rls_policies_kpis_rules_tensions_actions.sql
  V021__rls_policies_score_reports_notifications.sql
  V022__secure_views_sensitive_data.sql
  V023__rls_test_two_tenants.sql
```

Si se usa Flyway, se recomienda continuar la numeración después de FARO-SQL-001.

---

# 8. V014 · Security Context Functions

```sql
-- ============================================================
-- FARO-SQL-002 · V014__security_context_functions.sql
-- Session context helper functions
-- ============================================================

CREATE OR REPLACE FUNCTION faro.current_company_id()
RETURNS uuid AS $$
DECLARE
  v_company_id text;
BEGIN
  v_company_id := current_setting('app.company_id', true);

  IF v_company_id IS NULL OR v_company_id = '' THEN
    RETURN NULL;
  END IF;

  RETURN v_company_id::uuid;
EXCEPTION
  WHEN invalid_text_representation THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.current_user_id()
RETURNS uuid AS $$
DECLARE
  v_user_id text;
BEGIN
  v_user_id := current_setting('app.user_id', true);

  IF v_user_id IS NULL OR v_user_id = '' THEN
    RETURN NULL;
  END IF;

  RETURN v_user_id::uuid;
EXCEPTION
  WHEN invalid_text_representation THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.current_role_codes()
RETURNS text[] AS $$
DECLARE
  v_roles text;
BEGIN
  v_roles := current_setting('app.role_codes', true);

  IF v_roles IS NULL OR v_roles = '' THEN
    RETURN ARRAY[]::text[];
  END IF;

  RETURN string_to_array(v_roles, ',');
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.has_role(p_role_code text)
RETURNS boolean AS $$
BEGIN
  RETURN p_role_code = ANY(faro.current_role_codes());
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.has_any_role(p_role_codes text[])
RETURNS boolean AS $$
BEGIN
  RETURN faro.current_role_codes() && p_role_codes;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.is_service_role()
RETURNS boolean AS $$
BEGIN
  RETURN faro.has_any_role(ARRAY['integration_service', 'faro_owner']);
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.is_company_admin()
RETURNS boolean AS $$
BEGIN
  RETURN faro.has_any_role(ARRAY['company_admin', 'faro_owner']);
END;
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION faro.is_executive_role()
RETURNS boolean AS $$
BEGIN
  RETURN faro.has_any_role(ARRAY['director', 'general_manager', 'company_admin', 'faro_owner']);
END;
$$ LANGUAGE plpgsql STABLE;

COMMENT ON FUNCTION faro.current_company_id() IS 'Returns current tenant/company id from app.company_id session setting';
COMMENT ON FUNCTION faro.current_user_id() IS 'Returns current user id from app.user_id session setting';
COMMENT ON FUNCTION faro.current_role_codes() IS 'Returns role codes from app.role_codes session setting';
```

---

# 9. Cómo debe setear contexto el backend

El backend debe setear el contexto al abrir transacción o antes de ejecutar queries del usuario.

## Ejemplo SQL

```sql
SELECT set_config('app.company_id', '00000000-0000-0000-0000-000000000001', true);
SELECT set_config('app.user_id', '11111111-1111-1111-1111-111111111111', true);
SELECT set_config('app.role_codes', 'director,general_manager', true);
```

## Ejemplo Node.js conceptual

```ts
await client.query('BEGIN');

await client.query(
  `SELECT set_config('app.company_id', $1, true)`,
  [session.companyId]
);

await client.query(
  `SELECT set_config('app.user_id', $1, true)`,
  [session.userId]
);

await client.query(
  `SELECT set_config('app.role_codes', $1, true)`,
  [session.roleCodes.join(',')]
);

const result = await client.query(`
  SELECT *
  FROM faro.tensions
  ORDER BY detected_at DESC
`);

await client.query('COMMIT');
```

## Advertencia

No se debe usar una conexión pooled sin resetear contexto.

Si se usa pool, cada request debe:

1. abrir transacción;
2. setear contexto;
3. ejecutar queries;
4. commit/rollback;
5. liberar conexión.

Si no se hace, se corre el riesgo de arrastrar contexto de otro usuario. Eso no es bug menor; es incendio.

---

# 10. V015 · Application DB Roles and Grants

```sql
-- ============================================================
-- FARO-SQL-002 · V015__application_db_roles_and_grants.sql
-- PostgreSQL roles and grants
-- ============================================================

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_app') THEN
    CREATE ROLE faro_app NOINHERIT;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_migration') THEN
    CREATE ROLE faro_migration NOINHERIT;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_readonly') THEN
    CREATE ROLE faro_readonly NOINHERIT;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'faro_ingestion') THEN
    CREATE ROLE faro_ingestion NOINHERIT;
  END IF;
END $$;

GRANT USAGE ON SCHEMA faro TO faro_app;
GRANT USAGE ON SCHEMA audit TO faro_app;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA faro TO faro_app;
GRANT INSERT ON audit.audit_log TO faro_app;

GRANT USAGE ON ALL SEQUENCES IN SCHEMA faro TO faro_app;

GRANT USAGE ON SCHEMA faro TO faro_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA faro TO faro_readonly;

GRANT USAGE ON SCHEMA faro TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.data_sources TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.ingestion_batches TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.raw_records TO faro_ingestion;
GRANT SELECT, INSERT, UPDATE ON faro.source_fields TO faro_ingestion;

ALTER DEFAULT PRIVILEGES IN SCHEMA faro
GRANT SELECT, INSERT, UPDATE ON TABLES TO faro_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA faro
GRANT SELECT ON TABLES TO faro_readonly;
```

## Nota importante

En producción, los usuarios reales de aplicación no deberían conectarse como superuser ni como owner de tablas.

Deben conectarse con un rol controlado, por ejemplo:

```text
faro_app
```

Y RLS debe aplicarse sobre ese rol.

---

# 11. V016 · Seed Roles and Permissions

```sql
-- ============================================================
-- FARO-SQL-002 · V016__seed_roles_permissions.sql
-- Functional roles and permissions
-- ============================================================

-- This seed assumes companies already exist.
-- System roles may have company_id NULL.

INSERT INTO faro.roles (
  company_id,
  role_code,
  name,
  scope,
  description,
  is_system_role
)
VALUES
  (NULL, 'faro_owner', 'Owner FARO', 'system', 'Internal FARO owner role', true),
  (NULL, 'integration_service', 'Integration Service', 'service', 'Technical integration role', true),
  (NULL, 'company_admin', 'Administrador Empresa', 'company', 'Company administrator', true),
  (NULL, 'director', 'Director', 'company', 'Executive director', true),
  (NULL, 'general_manager', 'Gerente General', 'company', 'General manager', true),
  (NULL, 'area_manager', 'Responsable de Área', 'area', 'Area manager', true),
  (NULL, 'branch_manager', 'Responsable de Sucursal', 'branch', 'Branch manager', true),
  (NULL, 'finance_user', 'Usuario Finanzas', 'area', 'Finance user', true),
  (NULL, 'commercial_user', 'Usuario Comercial', 'area', 'Commercial user', true),
  (NULL, 'stock_user', 'Usuario Stock', 'area', 'Stock user', true),
  (NULL, 'hr_user', 'Usuario RRHH', 'area', 'HR user', true),
  (NULL, 'viewer', 'Lector', 'company', 'Read-only limited user', true),
  (NULL, 'external_auditor', 'Auditor Externo', 'company', 'External auditor', true)
ON CONFLICT (company_id, role_code) DO NOTHING;

INSERT INTO faro.permissions (permission_code, module_code, action_code, description)
VALUES
  ('users.read', 'users', 'read', 'Read users'),
  ('users.manage', 'users', 'manage', 'Manage users'),
  ('roles.read', 'roles', 'read', 'Read roles'),
  ('roles.manage', 'roles', 'manage', 'Manage roles'),
  ('sources.read', 'sources', 'read', 'Read data sources'),
  ('sources.manage', 'sources', 'manage', 'Manage data sources'),
  ('raw.read', 'raw', 'read', 'Read RAW data'),
  ('raw.write', 'raw', 'write', 'Write RAW data'),
  ('staging.read', 'staging', 'read', 'Read staging data'),
  ('staging.write', 'staging', 'write', 'Write staging data'),
  ('facts.read', 'facts', 'read', 'Read facts'),
  ('facts.write', 'facts', 'write', 'Write facts'),
  ('kpis.read', 'kpis', 'read', 'Read KPIs'),
  ('signals.read', 'signals', 'read', 'Read signals'),
  ('rules.read', 'rules', 'read', 'Read rules'),
  ('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.read', 'evidence', 'read', 'Read evidence'),
  ('evidence.upload', 'evidence', 'upload', 'Upload evidence'),
  ('evidence.review', 'evidence', 'review', 'Review evidence'),
  ('score.read', 'score', 'read', 'Read score'),
  ('reports.read', 'reports', 'read', 'Read reports'),
  ('reports.generate', 'reports', 'generate', 'Generate reports'),
  ('audit.read', 'audit', 'read', 'Read audit logs'),
  ('admin.full', 'admin', 'full', 'Full administration')
ON CONFLICT (permission_code) DO NOTHING;
```

---

# 12. Función para validar permiso funcional

```sql
-- ============================================================
-- FARO-SQL-002 · permission helper
-- ============================================================

CREATE OR REPLACE FUNCTION faro.current_user_has_permission(p_permission_code text)
RETURNS boolean AS $$
DECLARE
  v_user_id uuid;
  v_company_id uuid;
  v_has_permission boolean;
BEGIN
  v_user_id := faro.current_user_id();
  v_company_id := faro.current_company_id();

  IF v_user_id IS NULL OR v_company_id IS NULL THEN
    RETURN false;
  END IF;

  SELECT EXISTS (
    SELECT 1
    FROM faro.user_roles ur
    JOIN faro.role_permissions rp
      ON rp.role_id = ur.role_id
    JOIN faro.permissions p
      ON p.permission_id = rp.permission_id
    WHERE ur.user_id = v_user_id
      AND ur.company_id = v_company_id
      AND ur.status = 'active'
      AND p.permission_code = p_permission_code
  )
  INTO v_has_permission;

  RETURN COALESCE(v_has_permission, false);
END;
$$ LANGUAGE plpgsql STABLE;
```

---

# 13. V017 · Enable RLS Core Tables

```sql
-- ============================================================
-- FARO-SQL-002 · V017__enable_rls_core_tables.sql
-- Enable row level security on core tenant tables
-- ============================================================

ALTER TABLE faro.companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.branches ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.areas ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.user_roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.raci_assignments ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.data_sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.source_fields ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.ingestion_batches ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.raw_records ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.suppliers ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.employees ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.staging_sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.staging_stock ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.staging_receivables ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.staging_purchases ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.fact_sales ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_stock_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_receivables ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_purchases ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.kpi_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.signal_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.rule_definitions ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.rule_evaluations ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.tensions ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.evidence ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.action_status_history ENABLE ROW LEVEL SECURITY;

ALTER TABLE faro.score_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE faro.notifications ENABLE ROW LEVEL SECURITY;

-- Optional: Force RLS so table owners are also subject to policies.
-- Use carefully in production after migration strategy is clear.

ALTER TABLE faro.companies FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.branches FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.areas FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.users FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.data_sources FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.raw_records FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.fact_sales FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.tensions FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.actions FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.evidence FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.score_snapshots FORCE ROW LEVEL SECURITY;
ALTER TABLE faro.reports FORCE ROW LEVEL SECURITY;
```

---

# 14. Política base multiempresa

## 14.1 Patrón SELECT

```sql
USING (
  company_id = faro.current_company_id()
)
```

## 14.2 Patrón INSERT

```sql
WITH CHECK (
  company_id = faro.current_company_id()
)
```

## 14.3 Patrón UPDATE

```sql
USING (
  company_id = faro.current_company_id()
)
WITH CHECK (
  company_id = faro.current_company_id()
)
```

## 14.4 Patrón DELETE

Para MVP, no se recomienda permitir DELETE físico desde aplicación.

Preferible:

* `status = archived`;
* `deleted_at = now()`;
* auditoría.

```sql
-- No DELETE policy by default
```

---

# 15. V018 · RLS Policies Organization, Sources and RAW

```sql
-- ============================================================
-- FARO-SQL-002 · V018__rls_policies_org_sources_raw.sql
-- RLS policies for organization, sources and RAW
-- ============================================================

-- Companies: user can see only current company.
DROP POLICY IF EXISTS company_isolation_companies_select ON faro.companies;
CREATE POLICY company_isolation_companies_select
ON faro.companies
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  OR faro.has_role('faro_owner')
);

-- Branches
DROP POLICY IF EXISTS company_isolation_branches_select ON faro.branches;
CREATE POLICY company_isolation_branches_select
ON faro.branches
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_branches_write ON faro.branches;
CREATE POLICY company_isolation_branches_write
ON faro.branches
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
);

-- Areas
DROP POLICY IF EXISTS company_isolation_areas_select ON faro.areas;
CREATE POLICY company_isolation_areas_select
ON faro.areas
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_areas_write ON faro.areas;
CREATE POLICY company_isolation_areas_write
ON faro.areas
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
);

-- Users
DROP POLICY IF EXISTS company_isolation_users_select ON faro.users;
CREATE POLICY company_isolation_users_select
ON faro.users
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS company_isolation_users_write ON faro.users;
CREATE POLICY company_isolation_users_write
ON faro.users
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
);

-- User roles
DROP POLICY IF EXISTS company_isolation_user_roles_select ON faro.user_roles;
CREATE POLICY company_isolation_user_roles_select
ON faro.user_roles
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_user_roles_write ON faro.user_roles;
CREATE POLICY company_isolation_user_roles_write
ON faro.user_roles
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
);

-- RACI
DROP POLICY IF EXISTS company_isolation_raci_select ON faro.raci_assignments;
CREATE POLICY company_isolation_raci_select
ON faro.raci_assignments
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_raci_write ON faro.raci_assignments;
CREATE POLICY company_isolation_raci_write
ON faro.raci_assignments
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND faro.is_company_admin()
);

-- Data sources
DROP POLICY IF EXISTS company_isolation_data_sources_select ON faro.data_sources;
CREATE POLICY company_isolation_data_sources_select
ON faro.data_sources
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_data_sources_write ON faro.data_sources;
CREATE POLICY company_isolation_data_sources_write
ON faro.data_sources
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
  )
);

-- Source fields
DROP POLICY IF EXISTS company_isolation_source_fields_select ON faro.source_fields;
CREATE POLICY company_isolation_source_fields_select
ON faro.source_fields
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_source_fields_write ON faro.source_fields;
CREATE POLICY company_isolation_source_fields_write
ON faro.source_fields
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
  )
);

-- Ingestion batches
DROP POLICY IF EXISTS company_isolation_ingestion_batches_select ON faro.ingestion_batches;
CREATE POLICY company_isolation_ingestion_batches_select
ON faro.ingestion_batches
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('sources.read')
  )
);

DROP POLICY IF EXISTS company_isolation_ingestion_batches_write ON faro.ingestion_batches;
CREATE POLICY company_isolation_ingestion_batches_write
ON faro.ingestion_batches
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.current_user_has_permission('raw.write')
    OR faro.is_company_admin()
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.current_user_has_permission('raw.write')
    OR faro.is_company_admin()
  )
);

-- RAW records: restricted.
DROP POLICY IF EXISTS company_isolation_raw_records_select ON faro.raw_records;
CREATE POLICY company_isolation_raw_records_select
ON faro.raw_records
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('raw.read')
  )
);

DROP POLICY IF EXISTS company_isolation_raw_records_write ON faro.raw_records;
CREATE POLICY company_isolation_raw_records_write
ON faro.raw_records
FOR INSERT
TO faro_app
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.current_user_has_permission('raw.write')
    OR faro.is_company_admin()
  )
);

DROP POLICY IF EXISTS company_isolation_raw_records_update ON faro.raw_records;
CREATE POLICY company_isolation_raw_records_update
ON faro.raw_records
FOR UPDATE
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.current_user_has_permission('raw.write')
    OR faro.is_company_admin()
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.current_user_has_permission('raw.write')
    OR faro.is_company_admin()
  )
);
```

---

# 16. V019 · RLS Policies Master, Staging and Facts

```sql
-- ============================================================
-- FARO-SQL-002 · V019__rls_policies_master_staging_facts.sql
-- RLS policies for master data, staging and facts
-- ============================================================

-- Master data pattern

DROP POLICY IF EXISTS company_isolation_customers_select ON faro.customers;
CREATE POLICY company_isolation_customers_select
ON faro.customers
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_customers_write ON faro.customers;
CREATE POLICY company_isolation_customers_write
ON faro.customers
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('facts.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('facts.write')
  )
);

DROP POLICY IF EXISTS company_isolation_products_select ON faro.products;
CREATE POLICY company_isolation_products_select
ON faro.products
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_products_write ON faro.products;
CREATE POLICY company_isolation_products_write
ON faro.products
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('facts.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('facts.write')
  )
);

DROP POLICY IF EXISTS company_isolation_suppliers_select ON faro.suppliers;
CREATE POLICY company_isolation_suppliers_select
ON faro.suppliers
FOR SELECT
TO faro_app
USING (company_id = faro.current_company_id());

DROP POLICY IF EXISTS company_isolation_suppliers_write ON faro.suppliers;
CREATE POLICY company_isolation_suppliers_write
ON faro.suppliers
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('facts.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('facts.write')
  )
);

DROP POLICY IF EXISTS company_isolation_employees_select ON faro.employees;
CREATE POLICY company_isolation_employees_select
ON faro.employees
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_any_role(ARRAY['hr_user', 'company_admin', 'integration_service'])
    OR user_id = faro.current_user_id()
  )
);

DROP POLICY IF EXISTS company_isolation_employees_write ON faro.employees;
CREATE POLICY company_isolation_employees_write
ON faro.employees
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('hr_user')
    OR faro.has_role('integration_service')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_company_admin()
    OR faro.has_role('hr_user')
    OR faro.has_role('integration_service')
  )
);

-- Staging tables: normally only service/admin.

DROP POLICY IF EXISTS company_isolation_staging_sales_all ON faro.staging_sales;
CREATE POLICY company_isolation_staging_sales_all
ON faro.staging_sales
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.read')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.write')
  )
);

DROP POLICY IF EXISTS company_isolation_staging_stock_all ON faro.staging_stock;
CREATE POLICY company_isolation_staging_stock_all
ON faro.staging_stock
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.read')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.write')
  )
);

DROP POLICY IF EXISTS company_isolation_staging_receivables_all ON faro.staging_receivables;
CREATE POLICY company_isolation_staging_receivables_all
ON faro.staging_receivables
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.read')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.write')
  )
);

DROP POLICY IF EXISTS company_isolation_staging_purchases_all ON faro.staging_purchases;
CREATE POLICY company_isolation_staging_purchases_all
ON faro.staging_purchases
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.read')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('staging.write')
  )
);

-- Facts: read by authorized users, write by service/admin.

DROP POLICY IF EXISTS company_isolation_fact_sales_select ON faro.fact_sales;
CREATE POLICY company_isolation_fact_sales_select
ON faro.fact_sales
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_any_role(ARRAY['commercial_user', 'branch_manager', 'area_manager'])
    OR faro.current_user_has_permission('facts.read')
  )
);

DROP POLICY IF EXISTS company_isolation_fact_sales_write ON faro.fact_sales;
CREATE POLICY company_isolation_fact_sales_write
ON faro.fact_sales
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
);

DROP POLICY IF EXISTS company_isolation_fact_stock_select ON faro.fact_stock_snapshots;
CREATE POLICY company_isolation_fact_stock_select
ON faro.fact_stock_snapshots
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_any_role(ARRAY['stock_user', 'branch_manager', 'area_manager'])
    OR faro.current_user_has_permission('facts.read')
  )
);

DROP POLICY IF EXISTS company_isolation_fact_stock_write ON faro.fact_stock_snapshots;
CREATE POLICY company_isolation_fact_stock_write
ON faro.fact_stock_snapshots
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
);

DROP POLICY IF EXISTS company_isolation_fact_receivables_select ON faro.fact_receivables;
CREATE POLICY company_isolation_fact_receivables_select
ON faro.fact_receivables
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_role('finance_user')
    OR faro.current_user_has_permission('facts.read')
  )
);

DROP POLICY IF EXISTS company_isolation_fact_receivables_write ON faro.fact_receivables;
CREATE POLICY company_isolation_fact_receivables_write
ON faro.fact_receivables
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
);

DROP POLICY IF EXISTS company_isolation_fact_purchases_select ON faro.fact_purchases;
CREATE POLICY company_isolation_fact_purchases_select
ON faro.fact_purchases
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_any_role(ARRAY['stock_user', 'area_manager'])
    OR faro.current_user_has_permission('facts.read')
  )
);

DROP POLICY IF EXISTS company_isolation_fact_purchases_write ON faro.fact_purchases;
CREATE POLICY company_isolation_fact_purchases_write
ON faro.fact_purchases
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('facts.write')
  )
);
```

---

# 17. V020 · RLS Policies KPIs, Rules, Tensions, Actions

```sql
-- ============================================================
-- FARO-SQL-002 · V020__rls_policies_kpis_rules_tensions_actions.sql
-- RLS policies for KPIs, signals, rules, tensions, actions, evidence
-- ============================================================

-- KPI snapshots
DROP POLICY IF EXISTS company_isolation_kpi_snapshots_select ON faro.kpi_snapshots;
CREATE POLICY company_isolation_kpi_snapshots_select
ON faro.kpi_snapshots
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS company_isolation_kpi_snapshots_write ON faro.kpi_snapshots;
CREATE POLICY company_isolation_kpi_snapshots_write
ON faro.kpi_snapshots
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('kpis.write')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('kpis.write')
  )
);

-- Signals
DROP POLICY IF EXISTS company_isolation_signal_log_select ON faro.signal_log;
CREATE POLICY company_isolation_signal_log_select
ON faro.signal_log
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS company_isolation_signal_log_write ON faro.signal_log;
CREATE POLICY company_isolation_signal_log_write
ON faro.signal_log
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
  )
);

-- Rule definitions: global rules company_id NULL + company-specific rules.
DROP POLICY IF EXISTS rule_definitions_select_policy ON faro.rule_definitions;
CREATE POLICY rule_definitions_select_policy
ON faro.rule_definitions
FOR SELECT
TO faro_app
USING (
  company_id IS NULL
  OR company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS rule_definitions_write_policy ON faro.rule_definitions;
CREATE POLICY rule_definitions_write_policy
ON faro.rule_definitions
FOR ALL
TO faro_app
USING (
  (
    company_id = faro.current_company_id()
    AND faro.is_company_admin()
  )
  OR faro.has_role('faro_owner')
)
WITH CHECK (
  (
    company_id = faro.current_company_id()
    AND faro.is_company_admin()
  )
  OR faro.has_role('faro_owner')
);

-- Rule evaluations
DROP POLICY IF EXISTS company_isolation_rule_evaluations_select ON faro.rule_evaluations;
CREATE POLICY company_isolation_rule_evaluations_select
ON faro.rule_evaluations
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS company_isolation_rule_evaluations_write ON faro.rule_evaluations;
CREATE POLICY company_isolation_rule_evaluations_write
ON faro.rule_evaluations
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
  )
);

-- Tensions
DROP POLICY IF EXISTS company_isolation_tensions_select ON faro.tensions;
CREATE POLICY company_isolation_tensions_select
ON faro.tensions
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('tensions.read')
    OR faro.has_any_role(ARRAY['area_manager', 'branch_manager'])
  )
);

DROP POLICY IF EXISTS company_isolation_tensions_insert ON faro.tensions;
CREATE POLICY company_isolation_tensions_insert
ON faro.tensions
FOR INSERT
TO faro_app
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
    OR faro.current_user_has_permission('tensions.manage')
  )
);

DROP POLICY IF EXISTS company_isolation_tensions_update ON faro.tensions;
CREATE POLICY company_isolation_tensions_update
ON faro.tensions
FOR UPDATE
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('tensions.manage')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('tensions.manage')
  )
);

-- Actions
DROP POLICY IF EXISTS company_isolation_actions_select ON faro.actions;
CREATE POLICY company_isolation_actions_select
ON faro.actions
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR approver_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('actions.read')
    OR faro.has_any_role(ARRAY['area_manager', 'branch_manager'])
  )
);

DROP POLICY IF EXISTS company_isolation_actions_insert ON faro.actions;
CREATE POLICY company_isolation_actions_insert
ON faro.actions
FOR INSERT
TO faro_app
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_executive_role()
    OR faro.current_user_has_permission('actions.manage')
  )
);

DROP POLICY IF EXISTS company_isolation_actions_update ON faro.actions;
CREATE POLICY company_isolation_actions_update
ON faro.actions
FOR UPDATE
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR approver_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('actions.manage')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR responsible_user_id = faro.current_user_id()
    OR approver_user_id = faro.current_user_id()
    OR faro.current_user_has_permission('actions.manage')
  )
);

-- Evidence
DROP POLICY IF EXISTS company_isolation_evidence_select ON faro.evidence;
CREATE POLICY company_isolation_evidence_select
ON faro.evidence
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR submitted_by = faro.current_user_id()
    OR reviewed_by = faro.current_user_id()
    OR faro.current_user_has_permission('evidence.read')
  )
);

DROP POLICY IF EXISTS company_isolation_evidence_insert ON faro.evidence;
CREATE POLICY company_isolation_evidence_insert
ON faro.evidence
FOR INSERT
TO faro_app
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    submitted_by = faro.current_user_id()
    OR faro.is_executive_role()
    OR faro.current_user_has_permission('evidence.upload')
  )
);

DROP POLICY IF EXISTS company_isolation_evidence_update ON faro.evidence;
CREATE POLICY company_isolation_evidence_update
ON faro.evidence
FOR UPDATE
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    reviewed_by = faro.current_user_id()
    OR faro.is_executive_role()
    OR faro.current_user_has_permission('evidence.review')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    reviewed_by = faro.current_user_id()
    OR faro.is_executive_role()
    OR faro.current_user_has_permission('evidence.review')
  )
);

-- Action status history
DROP POLICY IF EXISTS company_isolation_action_status_history_select ON faro.action_status_history;
CREATE POLICY company_isolation_action_status_history_select
ON faro.action_status_history
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS company_isolation_action_status_history_insert ON faro.action_status_history;
CREATE POLICY company_isolation_action_status_history_insert
ON faro.action_status_history
FOR INSERT
TO faro_app
WITH CHECK (
  company_id = faro.current_company_id()
);
```

---

# 18. V021 · RLS Policies Score, Reports and Notifications

```sql
-- ============================================================
-- FARO-SQL-002 · V021__rls_policies_score_reports_notifications.sql
-- RLS policies for score, reports, notifications
-- ============================================================

-- Score snapshots
DROP POLICY IF EXISTS company_isolation_score_snapshots_select ON faro.score_snapshots;
CREATE POLICY company_isolation_score_snapshots_select
ON faro.score_snapshots
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
);

DROP POLICY IF EXISTS company_isolation_score_snapshots_write ON faro.score_snapshots;
CREATE POLICY company_isolation_score_snapshots_write
ON faro.score_snapshots
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_company_admin()
  )
);

-- Reports
DROP POLICY IF EXISTS company_isolation_reports_select ON faro.reports;
CREATE POLICY company_isolation_reports_select
ON faro.reports
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.current_user_has_permission('reports.read')
    OR faro.has_any_role(ARRAY['area_manager', 'branch_manager', 'viewer'])
  )
);

DROP POLICY IF EXISTS company_isolation_reports_write ON faro.reports;
CREATE POLICY company_isolation_reports_write
ON faro.reports
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('reports.generate')
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.is_executive_role()
    OR faro.has_role('integration_service')
    OR faro.current_user_has_permission('reports.generate')
  )
);

-- Notifications: users see only their own notifications unless executive/admin.
DROP POLICY IF EXISTS company_isolation_notifications_select ON faro.notifications;
CREATE POLICY company_isolation_notifications_select
ON faro.notifications
FOR SELECT
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    user_id = faro.current_user_id()
    OR faro.is_executive_role()
  )
);

DROP POLICY IF EXISTS company_isolation_notifications_write ON faro.notifications;
CREATE POLICY company_isolation_notifications_write
ON faro.notifications
FOR ALL
TO faro_app
USING (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_executive_role()
  )
)
WITH CHECK (
  company_id = faro.current_company_id()
  AND (
    faro.has_role('integration_service')
    OR faro.is_executive_role()
  )
);
```

---

# 19. V022 · Secure Views for Sensitive Data

## 19.1 Objetivo

No toda restricción debe resolverse solo con RLS. Algunas restricciones son de columna.

Ejemplo:

* un usuario comercial puede ver ventas;
* pero no necesariamente margen;
* un responsable de sucursal puede ver su sucursal;
* pero no ver margen global.

Para eso conviene crear vistas seguras.

## 19.2 Vista ventas sin margen

```sql
-- ============================================================
-- FARO-SQL-002 · V022__secure_views_sensitive_data.sql
-- Secure views for sensitive data
-- ============================================================

CREATE OR REPLACE VIEW faro.v_sales_public AS
SELECT
  sale_id,
  company_id,
  branch_id,
  customer_id,
  product_id,
  employee_id,
  sale_date,
  document_type,
  document_number,
  quantity,
  gross_amount,
  discount_amount,
  net_amount,
  discount_pct,
  currency_code,
  confidence_score,
  created_at
FROM faro.fact_sales;

GRANT SELECT ON faro.v_sales_public TO faro_app;
```

## 19.3 Vista ejecutiva de ventas con margen

```sql
CREATE OR REPLACE VIEW faro.v_sales_executive AS
SELECT
  sale_id,
  company_id,
  branch_id,
  customer_id,
  product_id,
  employee_id,
  sale_date,
  document_type,
  document_number,
  quantity,
  gross_amount,
  discount_amount,
  net_amount,
  cost_amount,
  margin_amount,
  margin_pct,
  discount_pct,
  currency_code,
  confidence_score,
  created_at
FROM faro.fact_sales
WHERE
  faro.is_executive_role()
  OR faro.current_user_has_permission('facts.read');

GRANT SELECT ON faro.v_sales_executive TO faro_app;
```

## 19.4 Vista de acciones propias

```sql
CREATE OR REPLACE VIEW faro.v_my_actions AS
SELECT
  a.*
FROM faro.actions a
WHERE
  a.company_id = faro.current_company_id()
  AND (
    a.responsible_user_id = faro.current_user_id()
    OR a.approver_user_id = faro.current_user_id()
    OR faro.is_executive_role()
  );

GRANT SELECT ON faro.v_my_actions TO faro_app;
```

## 19.5 Vista bandeja de tensiones

```sql
CREATE OR REPLACE VIEW faro.v_tension_inbox AS
SELECT
  t.tension_id,
  t.company_id,
  t.tension_code,
  t.title,
  t.description,
  t.severity,
  t.priority_score,
  t.confidence_score,
  t.status,
  t.detected_at,
  t.due_at,
  t.responsible_user_id,
  u.full_name AS responsible_name,
  t.area_id,
  a.name AS area_name,
  t.branch_id,
  b.name AS branch_name,
  t.score_impact
FROM faro.tensions t
LEFT JOIN faro.users u
  ON u.user_id = t.responsible_user_id
LEFT JOIN faro.areas a
  ON a.area_id = t.area_id
LEFT JOIN faro.branches b
  ON b.branch_id = t.branch_id
WHERE
  t.company_id = faro.current_company_id();

GRANT SELECT ON faro.v_tension_inbox TO faro_app;
```

---

# 20. Auditoría complementaria para cambios sensibles

## 20.1 Trigger genérico de auditoría

```sql
CREATE OR REPLACE FUNCTION audit.log_row_change()
RETURNS trigger AS $$
DECLARE
  v_company_id uuid;
  v_actor_user_id uuid;
BEGIN
  v_company_id := faro.current_company_id();
  v_actor_user_id := faro.current_user_id();

  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit.audit_log (
      company_id,
      actor_user_id,
      entity_name,
      entity_id,
      action,
      before_data,
      after_data
    )
    VALUES (
      v_company_id,
      v_actor_user_id,
      TG_TABLE_NAME,
      COALESCE((to_jsonb(NEW)->>'id')::uuid, NULL),
      'insert',
      NULL,
      to_jsonb(NEW)
    );
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit.audit_log (
      company_id,
      actor_user_id,
      entity_name,
      entity_id,
      action,
      before_data,
      after_data
    )
    VALUES (
      v_company_id,
      v_actor_user_id,
      TG_TABLE_NAME,
      NULL,
      'update',
      to_jsonb(OLD),
      to_jsonb(NEW)
    );
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit.audit_log (
      company_id,
      actor_user_id,
      entity_name,
      entity_id,
      action,
      before_data,
      after_data
    )
    VALUES (
      v_company_id,
      v_actor_user_id,
      TG_TABLE_NAME,
      NULL,
      'delete',
      to_jsonb(OLD),
      NULL
    );
    RETURN OLD;
  END IF;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
```

## 20.2 Aplicar auditoría a tablas críticas

```sql
DROP TRIGGER IF EXISTS trg_audit_tensions ON faro.tensions;
CREATE TRIGGER trg_audit_tensions
AFTER INSERT OR UPDATE OR DELETE ON faro.tensions
FOR EACH ROW EXECUTE FUNCTION audit.log_row_change();

DROP TRIGGER IF EXISTS trg_audit_actions ON faro.actions;
CREATE TRIGGER trg_audit_actions
AFTER INSERT OR UPDATE OR DELETE ON faro.actions
FOR EACH ROW EXECUTE FUNCTION audit.log_row_change();

DROP TRIGGER IF EXISTS trg_audit_evidence ON faro.evidence;
CREATE TRIGGER trg_audit_evidence
AFTER INSERT OR UPDATE OR DELETE ON faro.evidence
FOR EACH ROW EXECUTE FUNCTION audit.log_row_change();

DROP TRIGGER IF EXISTS trg_audit_reports ON faro.reports;
CREATE TRIGGER trg_audit_reports
AFTER INSERT OR UPDATE OR DELETE ON faro.reports
FOR EACH ROW EXECUTE FUNCTION audit.log_row_change();

DROP TRIGGER IF EXISTS trg_audit_users ON faro.users;
CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON faro.users
FOR EACH ROW EXECUTE FUNCTION audit.log_row_change();
```

## Nota técnica

El trigger anterior es base conceptual. En producción conviene mejorar la identificación de `entity_id`, porque las PK tienen nombres distintos: `tension_id`, `action_id`, `evidence_id`, etc.

Una versión productiva debería recibir el nombre de PK como argumento del trigger.

---

# 21. V023 · Test Two Tenants

## 21.1 Objetivo

Probar que una empresa no puede ver datos de otra.

## 21.2 Seed de prueba

```sql
-- ============================================================
-- FARO-SQL-002 · V023__rls_test_two_tenants.sql
-- Two tenant isolation test
-- ============================================================

-- Use fixed UUIDs for repeatable tests.

INSERT INTO faro.companies (
  company_id,
  company_code,
  legal_name,
  display_name,
  status
)
VALUES
  (
    '00000000-0000-0000-0000-000000000001',
    'TENANT_A',
    'Tenant A SA',
    'Tenant A',
    'pilot'
  ),
  (
    '00000000-0000-0000-0000-000000000002',
    'TENANT_B',
    'Tenant B SA',
    'Tenant B',
    'pilot'
  )
ON CONFLICT (company_id) DO NOTHING;

INSERT INTO faro.users (
  user_id,
  company_id,
  email,
  full_name,
  status
)
VALUES
  (
    '11111111-1111-1111-1111-111111111111',
    '00000000-0000-0000-0000-000000000001',
    'director.a@test.local',
    'Director Tenant A',
    'active'
  ),
  (
    '22222222-2222-2222-2222-222222222222',
    '00000000-0000-0000-0000-000000000002',
    'director.b@test.local',
    'Director Tenant B',
    'active'
  )
ON CONFLICT (company_id, email) DO NOTHING;

INSERT INTO faro.data_sources (
  source_id,
  company_id,
  source_code,
  name,
  source_type,
  format_type,
  update_frequency,
  access_method,
  status
)
VALUES
  (
    'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
    '00000000-0000-0000-0000-000000000001',
    'SALES_A',
    'Ventas Tenant A',
    'csv',
    'csv',
    'weekly',
    'manual_upload',
    'active'
  ),
  (
    'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb',
    '00000000-0000-0000-0000-000000000002',
    'SALES_B',
    'Ventas Tenant B',
    'csv',
    'csv',
    'weekly',
    'manual_upload',
    'active'
  )
ON CONFLICT (company_id, source_code) DO NOTHING;

INSERT INTO faro.kpi_snapshots (
  company_id,
  kpi_code,
  period_start,
  period_end,
  dimension_type,
  dimension_id,
  value,
  status,
  confidence_score
)
VALUES
  (
    '00000000-0000-0000-0000-000000000001',
    'KPI-SAL-001',
    '2026-05-01',
    '2026-05-31',
    'company',
    NULL,
    1000000,
    'ok',
    90
  ),
  (
    '00000000-0000-0000-0000-000000000002',
    'KPI-SAL-001',
    '2026-05-01',
    '2026-05-31',
    'company',
    NULL,
    2000000,
    'ok',
    90
  )
ON CONFLICT (
  company_id,
  kpi_code,
  period_start,
  period_end,
  dimension_type,
  dimension_id
) DO NOTHING;
```

## 21.3 Test como Tenant A

```sql
BEGIN;

SELECT set_config('app.company_id', '00000000-0000-0000-0000-000000000001', true);
SELECT set_config('app.user_id', '11111111-1111-1111-1111-111111111111', true);
SELECT set_config('app.role_codes', 'director', true);

-- Debe ver solo Tenant A.
SELECT company_code
FROM faro.companies;

-- Debe devolver solo SALES_A.
SELECT source_code, name
FROM faro.data_sources
ORDER BY source_code;

-- Debe devolver value = 1000000.
SELECT kpi_code, value
FROM faro.kpi_snapshots
WHERE kpi_code = 'KPI-SAL-001';

COMMIT;
```

## 21.4 Test como Tenant B

```sql
BEGIN;

SELECT set_config('app.company_id', '00000000-0000-0000-0000-000000000002', true);
SELECT set_config('app.user_id', '22222222-2222-2222-2222-222222222222', true);
SELECT set_config('app.role_codes', 'director', true);

-- Debe ver solo Tenant B.
SELECT company_code
FROM faro.companies;

-- Debe devolver solo SALES_B.
SELECT source_code, name
FROM faro.data_sources
ORDER BY source_code;

-- Debe devolver value = 2000000.
SELECT kpi_code, value
FROM faro.kpi_snapshots
WHERE kpi_code = 'KPI-SAL-001';

COMMIT;
```

## 21.5 Test sin contexto

```sql
BEGIN;

RESET app.company_id;
RESET app.user_id;
RESET app.role_codes;

-- Debe devolver 0 filas.
SELECT *
FROM faro.data_sources;

-- Debe devolver 0 filas.
SELECT *
FROM faro.kpi_snapshots;

COMMIT;
```

## 21.6 Test de escritura cruzada prohibida

```sql
BEGIN;

SELECT set_config('app.company_id', '00000000-0000-0000-0000-000000000001', true);
SELECT set_config('app.user_id', '11111111-1111-1111-1111-111111111111', true);
SELECT set_config('app.role_codes', 'company_admin', true);

-- Debe fallar porque intenta insertar company_id de Tenant B
-- mientras el contexto es Tenant A.

INSERT INTO faro.data_sources (
  company_id,
  source_code,
  name,
  source_type,
  format_type,
  update_frequency,
  access_method,
  status
)
VALUES (
  '00000000-0000-0000-0000-000000000002',
  'ILLEGAL_SOURCE',
  'Fuente ilegal cruzada',
  'csv',
  'csv',
  'weekly',
  'manual_upload',
  'active'
);

ROLLBACK;
```

Resultado esperado:

```text
ERROR: new row violates row-level security policy
```

---

# 22. Vista de diagnóstico RLS

```sql
CREATE OR REPLACE VIEW faro.v_rls_context_debug AS
SELECT
  faro.current_company_id() AS current_company_id,
  faro.current_user_id() AS current_user_id,
  faro.current_role_codes() AS current_role_codes,
  faro.is_company_admin() AS is_company_admin,
  faro.is_executive_role() AS is_executive_role,
  faro.is_service_role() AS is_service_role;

GRANT SELECT ON faro.v_rls_context_debug TO faro_app;
```

Uso:

```sql
SELECT *
FROM faro.v_rls_context_debug;
```

Esto ayuda a detectar rápido si el backend no seteó bien el contexto.

---

# 23. Checklist de tablas que deben tener RLS

| Tabla                 |           RLS | Política base            |
| --------------------- | ------------: | ------------------------ |
| companies             |            Sí | company_id               |
| branches              |            Sí | company_id               |
| areas                 |            Sí | company_id               |
| users                 |            Sí | company_id               |
| user_roles            |            Sí | company_id               |
| raci_assignments      |            Sí | company_id               |
| data_sources          |            Sí | company_id               |
| source_fields         |            Sí | company_id               |
| ingestion_batches     |            Sí | company_id               |
| raw_records           |            Sí | company_id               |
| customers             |            Sí | company_id               |
| products              |            Sí | company_id               |
| suppliers             |            Sí | company_id               |
| employees             |            Sí | company_id               |
| staging_sales         |            Sí | company_id               |
| staging_stock         |            Sí | company_id               |
| staging_receivables   |            Sí | company_id               |
| staging_purchases     |            Sí | company_id               |
| fact_sales            |            Sí | company_id               |
| fact_stock_snapshots  |            Sí | company_id               |
| fact_receivables      |            Sí | company_id               |
| fact_purchases        |            Sí | company_id               |
| kpi_snapshots         |            Sí | company_id               |
| signal_log            |            Sí | company_id               |
| rule_definitions      | Sí / especial | company_id o global      |
| rule_evaluations      |            Sí | company_id               |
| tensions              |            Sí | company_id + responsable |
| actions               |            Sí | company_id + responsable |
| evidence              |            Sí | company_id + acción      |
| action_status_history |            Sí | company_id               |
| score_snapshots       |            Sí | company_id               |
| reports               |            Sí | company_id               |
| notifications         |            Sí | company_id + user_id     |

---

# 24. Query para auditar RLS activado

```sql
SELECT
  schemaname,
  tablename,
  rowsecurity
FROM pg_tables
WHERE schemaname = 'faro'
ORDER BY tablename;
```

Debe mostrar `rowsecurity = true` para tablas operativas.

---

# 25. Query para listar policies

```sql
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'faro'
ORDER BY tablename, policyname;
```

---

# 26. Criterios de aceptación técnica

FARO-SQL-002 se considera aceptado si:

| Criterio                                     | Aceptación |
| -------------------------------------------- | ---------- |
| Todas las tablas operativas tienen RLS       | Sí         |
| `current_company_id()` funciona              | Sí         |
| `current_user_id()` funciona                 | Sí         |
| `current_role_codes()` funciona              | Sí         |
| Usuario sin contexto no ve datos             | Sí         |
| Tenant A no ve Tenant B                      | Sí         |
| Tenant B no ve Tenant A                      | Sí         |
| No se permite insertar con otro `company_id` | Sí         |
| RAW queda restringido                        | Sí         |
| Staging queda restringido                    | Sí         |
| Facts quedan restringidos                    | Sí         |
| Tensiones respetan empresa y responsable     | Sí         |
| Acciones respetan empresa y responsable      | Sí         |
| Notificaciones respetan usuario              | Sí         |
| Reports respetan empresa                     | Sí         |
| Vistas sensibles separan margen              | Sí         |
| Tests de dos tenants pasan                   | Sí         |

---

# 27. Riesgos técnicos

| Riesgo                                | Severidad  | Mitigación                             |
| ------------------------------------- | ---------- | -------------------------------------- |
| Backend no setea contexto             | Alta       | Middleware obligatorio + test          |
| Pool reutiliza contexto anterior      | Crítica    | Transacción por request + reset        |
| Superuser bypass RLS                  | Alta       | App no usa superuser                   |
| Owner bypass RLS                      | Media/Alta | FORCE RLS                              |
| Policies demasiado permisivas         | Alta       | Tests negativos                        |
| Views exponen columnas sensibles      | Alta       | Revisar grants                         |
| Funciones SECURITY DEFINER mal usadas | Alta       | Evitar salvo necesidad                 |
| Migraciones corren con rol equivocado | Media      | Separar `faro_migration` de `faro_app` |
| Reports mezclan empresas              | Crítica    | Siempre filtrar company_id             |
| IA recibe datos sin filtrar           | Crítica    | IA solo consume queries ya autorizadas |

---

# 28. Reglas de oro para el backend

1. Nunca confiar en `company_id` enviado por frontend.
2. Obtener `company_id` desde sesión/JWT validado.
3. Setear `app.company_id` en cada request.
4. Setear `app.user_id` en cada request.
5. Setear `app.role_codes` en cada request.
6. No usar superuser para la app.
7. No consultar tablas sensibles desde endpoints genéricos.
8. No exportar sin verificar permiso.
9. No mandar a IA datos crudos sin autorización.
10. No desactivar RLS para “resolver rápido”.

La frase “lo apagamos un minuto para probar” suele ser el inicio de una auditoría triste.

---

# 29. Ejemplo de middleware conceptual

```ts
type SessionContext = {
  companyId: string;
  userId: string;
  roleCodes: string[];
};

export async function withFaroDbContext<T>(
  db: any,
  session: SessionContext,
  fn: (client: any) => Promise<T>
): Promise<T> {
  const client = await db.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      `SELECT set_config('app.company_id', $1, true)`,
      [session.companyId]
    );

    await client.query(
      `SELECT set_config('app.user_id', $1, true)`,
      [session.userId]
    );

    await client.query(
      `SELECT set_config('app.role_codes', $1, true)`,
      [session.roleCodes.join(',')]
    );

    const result = await fn(client);

    await client.query('COMMIT');

    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}
```

---

# 30. Ejemplo endpoint seguro

```ts
export async function getTensionInbox(req: any, res: any) {
  const session = req.session;

  const result = await withFaroDbContext(db, session, async (client) => {
    return client.query(`
      SELECT
        tension_id,
        tension_code,
        title,
        severity,
        priority_score,
        confidence_score,
        status,
        detected_at,
        due_at,
        responsible_name,
        area_name,
        branch_name,
        score_impact
      FROM faro.v_tension_inbox
      ORDER BY
        CASE severity
          WHEN 'critical' THEN 4
          WHEN 'high' THEN 3
          WHEN 'medium' THEN 2
          ELSE 1
        END DESC,
        detected_at DESC
      LIMIT 100
    `);
  });

  res.json(result.rows);
}
```

---

# 31. Política sobre IA y RLS

La IA no debe tener acceso directo a tablas sin pasar por el mismo contexto de seguridad.

## Permitido

```text
Usuario autorizado
→ Backend setea contexto
→ Query filtra por RLS
→ Resultado autorizado
→ IA redacta explicación
```

## Prohibido

```text
IA
→ acceso directo a base completa
→ arma respuesta cruzando empresas
```

La IA en FARO explica, resume y asiste. No rompe gobierno de datos.

---

# 32. Política sobre reportes PDF

Todo reporte debe generarse dentro del contexto de empresa.

Campos obligatorios en `reports`:

* `company_id`;
* `period_start`;
* `period_end`;
* `report_type`;
* `score_snapshot_id`;
* `recipients`;
* `generated_by`.

La generación del PDF debe usar queries bajo RLS o procedimientos que reciban `company_id` validado.

---

# 33. Política sobre exportaciones

Exportar datos es más riesgoso que verlos en pantalla.

## Reglas mínimas

| Exportación           | Permiso requerido            |
| --------------------- | ---------------------------- |
| Reporte ejecutivo PDF | `reports.read`               |
| KPIs                  | `kpis.read`                  |
| Tensiones             | `tensions.read`              |
| Acciones              | `actions.read`               |
| Facts ventas          | `facts.read` + rol ejecutivo |
| RAW                   | `raw.read` + admin           |
| Datos sensibles       | aprobación específica        |

Toda exportación debe registrarse en `audit.audit_log` con acción `export`.

---

# 34. Soft delete recomendado

Para tablas críticas, evitar DELETE físico desde aplicación.

## Patrón

```sql
UPDATE faro.actions
SET
  status = 'cancelled',
  updated_at = now()
WHERE action_id = $1;
```

## Tablas donde evitar DELETE

| Tabla             | Motivo              |
| ----------------- | ------------------- |
| raw_records       | Trazabilidad        |
| ingestion_batches | Auditoría           |
| fact_sales        | Historial           |
| kpi_snapshots     | Evolución           |
| signal_log        | Diagnóstico         |
| rule_evaluations  | Explicabilidad      |
| tensions          | Gobierno            |
| actions           | Seguimiento         |
| evidence          | Cierre              |
| score_snapshots   | Evolución           |
| reports           | Historial ejecutivo |

---

# 35. Seguridad por sucursal y área

RLS por `company_id` es el mínimo.

En fases posteriores se puede profundizar:

```text
company_id
→ branch_id
→ area_id
→ responsible_user_id
```

Para MVP, se puede resolver parte de esto en vistas y backend.

Para Enterprise, conviene crear policies específicas por alcance:

* director ve todo;
* gerente general ve todo;
* responsable sucursal ve `branch_id`;
* responsable área ve `area_id`;
* usuario operativo ve asignados;
* auditor ve solo lectura.

---

# 36. Futuro FARO-SQL-002.1 · Branch/Area RLS avanzado

Ejemplo conceptual:

```sql
CREATE OR REPLACE FUNCTION faro.user_has_branch_access(p_branch_id uuid)
RETURNS boolean AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM faro.user_roles ur
    WHERE ur.company_id = faro.current_company_id()
      AND ur.user_id = faro.current_user_id()
      AND ur.status = 'active'
      AND (
        ur.branch_id = p_branch_id
        OR ur.branch_id IS NULL
        OR faro.is_executive_role()
      )
  );
END;
$$ LANGUAGE plpgsql STABLE;
```

Luego:

```sql
USING (
  company_id = faro.current_company_id()
  AND faro.user_has_branch_access(branch_id)
)
```

Esto se recomienda después del MVP, cuando ya estén bien definidos usuarios y sucursales reales.

---

# 37. Comandos de validación rápida

```sql
-- Ver contexto actual
SELECT *
FROM faro.v_rls_context_debug;

-- Ver si RLS está activo
SELECT
  tablename,
  rowsecurity
FROM pg_tables
WHERE schemaname = 'faro'
ORDER BY tablename;

-- Ver policies
SELECT
  tablename,
  policyname,
  cmd
FROM pg_policies
WHERE schemaname = 'faro'
ORDER BY tablename, policyname;

-- Probar fuentes visibles
SELECT source_code, name
FROM faro.data_sources;

-- Probar KPIs visibles
SELECT kpi_code, value
FROM faro.kpi_snapshots;

-- Probar bandeja de tensiones
SELECT *
FROM faro.v_tension_inbox
LIMIT 20;
```

---

# 38. Checklist operativo de implementación

## Antes de activar RLS

| Check | Tarea                                                       |
| ----- | ----------------------------------------------------------- |
| ☐     | Confirmar que todas las tablas críticas tienen `company_id` |
| ☐     | Confirmar roles funcionales                                 |
| ☐     | Confirmar permisos base                                     |
| ☐     | Confirmar usuario app no es superuser                       |
| ☐     | Confirmar backend puede setear contexto                     |
| ☐     | Confirmar migraciones corren con rol separado               |
| ☐     | Confirmar tests de dos tenants preparados                   |

## Durante activación

| Check | Tarea                           |
| ----- | ------------------------------- |
| ☐     | Crear funciones de contexto     |
| ☐     | Crear roles DB                  |
| ☐     | Crear grants mínimos            |
| ☐     | Activar RLS                     |
| ☐     | Crear policies                  |
| ☐     | Crear vistas seguras            |
| ☐     | Ejecutar tests Tenant A         |
| ☐     | Ejecutar tests Tenant B         |
| ☐     | Ejecutar test sin contexto      |
| ☐     | Ejecutar test escritura cruzada |

## Después de activar

| Check | Tarea                                |
| ----- | ------------------------------------ |
| ☐     | Validar UI con usuario director      |
| ☐     | Validar UI con usuario responsable   |
| ☐     | Validar integración con service role |
| ☐     | Validar reporte PDF                  |
| ☐     | Validar exportación                  |
| ☐     | Revisar audit log                    |
| ☐     | Documentar excepciones               |
| ☐     | Bloquear DELETE físico               |

---

# 39. Criterio de rechazo

FARO-SQL-002 debe rechazarse si ocurre cualquiera de estos casos:

| Caso                                    | Severidad |
| --------------------------------------- | --------- |
| Usuario sin contexto ve datos           | Crítica   |
| Tenant A ve datos Tenant B              | Crítica   |
| Usuario puede insertar company_id ajeno | Crítica   |
| RAW queda visible para usuarios comunes | Alta      |
| Reports pueden mezclar empresas         | Crítica   |
| Backend usa superuser                   | Alta      |
| Conexión pooled arrastra contexto       | Crítica   |
| No hay tests negativos                  | Alta      |
| IA accede directo a datos sin contexto  | Crítica   |

---

# 40. Resultado esperado

Al finalizar FARO-SQL-002, FARO Connect debe poder afirmar:

```text
La base soporta múltiples empresas.
Cada dato operativo pertenece a una empresa.
La sesión define contexto seguro.
PostgreSQL aplica aislamiento por RLS.
Los roles limitan lectura y escritura.
Las vistas reducen exposición sensible.
Los tests prueban aislamiento entre tenants.
La auditoría registra acciones críticas.
```

Esto no hace que FARO sea “enterprise-grade” completo todavía. Pero sí evita el pecado mortal de un SaaS: mezclar datos de clientes. Ese error no se arregla con diseño premium ni con una frase linda. Se arregla con seguridad desde la base.

---

# 41. Próximo paso recomendado

Después de FARO-SQL-002 corresponde construir:

## FARO-SQL-003 · Seeds Empresa Demo

Objetivo:

Crear datos mínimos para probar:

* empresa demo;
* sucursales;
* áreas;
* usuarios;
* roles;
* permisos;
* productos;
* clientes;
* vendedores;
* fuentes;
* ventas;
* stock;
* cobranza;
* KPIs;
* tensiones;
* acciones;
* evidencias;
* score inicial.

Sin FARO-SQL-003, la base está segura, pero vacía. Y una base vacía es como una Ferrari sin nafta: técnicamente impresionante, comercialmente inútil.