Database Schema - Cierro

Este documento describe el esquema completo de la base de datos PostgreSQL de Cierro.

Diagrama Entidad-Relación

┌─────────────────────┐
│ subscription_plans  │
└──────────┬──────────┘
           │
           │ 1:N
           ▼
┌─────────────────────┐      ┌─────────────────────┐
│     companies       │──────│ company_invitations │
└──────────┬──────────┘      └─────────────────────┘
           │
           │ 1:N
           ▼
┌─────────────────────┐      ┌─────────────────────┐
│     profiles        │──────│    user_roles       │
└──────────┬──────────┘      └─────────────────────┘
           │
     ┌─────┴─────┐
     │           │
     ▼           ▼
┌─────────┐  ┌─────────────────┐
│ budgets │  │  sales_goals    │
└────┬────┘  └─────────────────┘
     │
     ├──────────────────┐
     ▼                  ▼
┌─────────────┐  ┌───────────────────┐
│budget_vers. │  │budget_conversations│
└─────────────┘  └───────────────────┘

Tablas Principales

companies

Almacena información de cada empresa/organización.

CREATE TABLE companies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  
  -- Branding
  logo_url TEXT,
  primary_color TEXT DEFAULT '#5869f7',
  secondary_color TEXT DEFAULT '#0f1011',
  accent_color TEXT DEFAULT '#3b82f6',
  background_color TEXT DEFAULT '#0f172a',
  text_on_primary TEXT DEFAULT '#ffffff',
  font_title TEXT DEFAULT 'Space Grotesk',
  font_body TEXT DEFAULT 'Inter',
  
  -- Configuración
  default_currency TEXT DEFAULT 'USD',
  default_language TEXT DEFAULT 'es',
  base_report_currency TEXT DEFAULT 'USD',
  system_prompt TEXT,
  memory TEXT,
  
  -- Suscripción
  subscription_status TEXT DEFAULT 'trialing',
  subscription_plan_id UUID REFERENCES subscription_plans(id),
  trial_started_at TIMESTAMPTZ,
  trial_ends_at TIMESTAMPTZ,
  subscription_started_at TIMESTAMPTZ,
  subscription_ends_at TIMESTAMPTZ,
  downgraded_from_plan_id UUID REFERENCES subscription_plans(id),
  downgraded_at TIMESTAMPTZ,
  
  -- Pagos (Reveniu)
  reveniu_subscription_id INTEGER,
  reveniu_plan_id INTEGER,
  reveniu_customer_email TEXT,
  reveniu_external_id TEXT,
  
  -- Pagos (Stripe)
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Índices

CREATE INDEX idx_companies_subscription_status ON companies(subscription_status);
CREATE INDEX idx_companies_trial_ends_at ON companies(trial_ends_at);

profiles

Perfiles de usuario vinculados a Clerk.

CREATE TABLE profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clerk_user_id TEXT NOT NULL UNIQUE,
  email TEXT,
  full_name TEXT,
  avatar_url TEXT,
  company_id UUID REFERENCES companies(id),
  role TEXT DEFAULT 'user',
  is_super_admin BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Índices

CREATE UNIQUE INDEX idx_profiles_clerk_user_id ON profiles(clerk_user_id);
CREATE INDEX idx_profiles_company_id ON profiles(company_id);
CREATE INDEX idx_profiles_email ON profiles(email);

user_roles

Roles de usuarios por empresa (RBAC).

CREATE TYPE app_role AS ENUM ('viewer', 'member', 'manager', 'admin');

CREATE TABLE user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id UUID NOT NULL REFERENCES profiles(id),
  company_id UUID NOT NULL REFERENCES companies(id),
  role app_role NOT NULL DEFAULT 'member',
  created_by UUID REFERENCES profiles(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  
  UNIQUE(profile_id, company_id)
);

Jerarquía de Roles

admin > manager > member > viewer

budgets

Propuestas comerciales/presupuestos.

CREATE TABLE budgets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  created_by UUID REFERENCES profiles(id),
  owner_id UUID REFERENCES profiles(id), -- Nuevo v1.7.0: Responsable de la venta
  
  -- Información básica
  title TEXT NOT NULL,
  status TEXT DEFAULT 'draft',
  version INTEGER DEFAULT 1,
  
  -- Cliente
  client_name TEXT,
  client_company TEXT,
  client_email TEXT,
  
  -- Contenido
  raw_html TEXT,
  executive_summary TEXT,
  scope TEXT,
  services TEXT,
  context TEXT,
  context_files JSONB DEFAULT '[]',
  
  -- Precios
  total_amount NUMERIC DEFAULT 0,
  currency TEXT DEFAULT 'USD',
  exchange_rate NUMERIC,
  base_currency_amount NUMERIC,
  price_range_min NUMERIC,
  price_range_max NUMERIC,
  billing_type TEXT DEFAULT 'one-time',
  tax_type TEXT DEFAULT 'plus-tax',
  estimated_budget TEXT,
  pricing_notes TEXT,
  commercial_conditions TEXT,
  
  -- Costos y etapas
  costs JSONB DEFAULT '[]',
  stages JSONB DEFAULT '[]',
  sections JSONB DEFAULT '[]',
  selected_products JSONB DEFAULT '[]',
  
  -- Descuentos
  discount_preset_id UUID REFERENCES discount_presets(id),
  discount_name TEXT,
  discount_type TEXT,
  discount_value NUMERIC,
  discount_amount NUMERIC DEFAULT 0,
  
  -- Configuración de visualización
  show_deliverables BOOLEAN DEFAULT true,
  show_one_time_cost_details BOOLEAN DEFAULT true,
  show_recurring_cost_details BOOLEAN DEFAULT true,
  special_blocks_order TEXT[] DEFAULT ARRAY['stages-block', 'pricing-block'],
  
  -- Configuración de página
  page_size TEXT DEFAULT 'LETTER',
  orientation TEXT DEFAULT 'portrait',
  margins JSONB DEFAULT '{"top": 2.54, "left": 2.54, "right": 2.54, "bottom": 2.54}',
  page_header JSONB,
  
  -- Validez
  validity_days INTEGER DEFAULT 30,
  
  -- Aprobación
  approved_at TIMESTAMPTZ,
  approved_amount NUMERIC,
  approved_one_time_amount NUMERIC,
  approved_recurring_amount NUMERIC,
  
  -- Facturación
  invoice_number TEXT,
  invoiced_at TIMESTAMPTZ,
  invoiced_amount NUMERIC,
  
  -- Integraciones
  hubspot_deal_id TEXT,
  hubspot_contact_id TEXT,
  pdf_url TEXT,
  
  -- Demo data flag (v1.2.0)
  is_demo BOOLEAN DEFAULT false,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Status Values

draft → sent → approved/rejected

Índices

CREATE INDEX idx_budgets_company_id ON budgets(company_id);
CREATE INDEX idx_budgets_created_by ON budgets(created_by);
CREATE INDEX idx_budgets_status ON budgets(status);
CREATE INDEX idx_budgets_created_at ON budgets(created_at DESC);

budget_versions

Historial de versiones de presupuestos.

CREATE TABLE budget_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  budget_id UUID NOT NULL REFERENCES budgets(id) ON DELETE CASCADE,
  version_number INTEGER NOT NULL DEFAULT 1,
  change_description TEXT,
  created_by UUID REFERENCES profiles(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  
  -- Snapshot de todos los campos del budget
  title TEXT NOT NULL,
  -- ... (mismos campos que budgets)
  
  UNIQUE(budget_id, version_number)
);

budget_conversations

Mensajes del chat asociados a un presupuesto.

CREATE TABLE budget_conversations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  budget_id UUID NOT NULL REFERENCES budgets(id) ON DELETE CASCADE,
  role TEXT NOT NULL, -- 'user' | 'assistant'
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

products

Catálogo de productos/servicios.

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  created_by UUID REFERENCES profiles(id),
  
  name TEXT NOT NULL,
  description TEXT,
  image_url TEXT,
  category TEXT,
  
  item_type TEXT DEFAULT 'product', -- 'product' | 'service'
  cost_type TEXT DEFAULT 'one-time', -- 'one-time' | 'recurring'
  
  default_unit_price NUMERIC NOT NULL DEFAULT 0,
  default_quantity INTEGER DEFAULT 1,
  
  is_active BOOLEAN DEFAULT true,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

discount_presets

Descuentos predefinidos.

CREATE TABLE discount_presets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  
  name TEXT NOT NULL,
  description TEXT,
  discount_type TEXT NOT NULL, -- 'percentage' | 'fixed'
  discount_value NUMERIC NOT NULL,
  applies_to TEXT DEFAULT 'total', -- 'total' | 'one-time' | 'recurring'
  
  is_active BOOLEAN DEFAULT true,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

sales_goals

Metas de ventas por período.

CREATE TABLE sales_goals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  profile_id UUID NOT NULL REFERENCES profiles(id),
  created_by UUID REFERENCES profiles(id),
  
  period_type TEXT DEFAULT 'monthly',
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  
  one_time_goal NUMERIC DEFAULT 0,
  recurring_goal NUMERIC DEFAULT 0,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  
  UNIQUE(company_id, profile_id, period_start)
);

commission_rules

Reglas de comisiones por ejecutivo.

CREATE TABLE commission_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  profile_id UUID NOT NULL REFERENCES profiles(id),
  created_by UUID REFERENCES profiles(id),
  
  rule_type TEXT NOT NULL, -- 'base' | 'product' | 'category' | 'goal_bonus'
  commission_percentage NUMERIC NOT NULL DEFAULT 0,
  bonus_percentage NUMERIC DEFAULT 0,
  goal_threshold_percent NUMERIC,
  
  product_id UUID REFERENCES products(id),
  category TEXT,
  
  is_active BOOLEAN DEFAULT true,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

commission_calculations

Cálculos de comisiones realizados.

Nota v1.7.0: Se generan automáticamente mediante el trigger calculate_budget_commission cuando una propuesta pasa a estado approved.

CREATE TABLE commission_calculations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  profile_id UUID NOT NULL REFERENCES profiles(id),
  budget_id UUID NOT NULL REFERENCES budgets(id),
  
  base_sale_amount NUMERIC NOT NULL DEFAULT 0,
  commission_amount NUMERIC NOT NULL DEFAULT 0,
  calculation_breakdown JSONB DEFAULT '{}',
  
  status TEXT DEFAULT 'pending', -- 'pending' | 'approved' | 'paid'
  
  period_start DATE,
  period_end DATE,
  calculated_at TIMESTAMPTZ DEFAULT now(),
  paid_at TIMESTAMPTZ,
  notes TEXT,
  
  created_at TIMESTAMPTZ DEFAULT now()
);

subscription_plans

Planes de suscripción disponibles.

CREATE TABLE subscription_plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  
  price_monthly NUMERIC NOT NULL DEFAULT 0,
  price_yearly NUMERIC,
  currency TEXT DEFAULT 'USD',
  
  limits JSONB NOT NULL DEFAULT '{}',
  features JSONB DEFAULT '[]',
  
  is_active BOOLEAN DEFAULT true,
  sort_order INTEGER DEFAULT 0,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Ejemplo de Limits

{
  "users": 5,
  "budgets_per_month": 50,
  "ai_generations_per_month": 100,
  "storage_mb": 500,
  "custom_branding": true,
  "api_access": false
}

company_usage

Uso mensual por empresa.

CREATE TABLE company_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  
  budgets_created INTEGER DEFAULT 0,
  ai_generations INTEGER DEFAULT 0,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  
  UNIQUE(company_id, period_start)
);

company_storage_quotas

Cuotas de almacenamiento por empresa.

CREATE TABLE company_storage_quotas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id) UNIQUE,
  
  plan_name TEXT DEFAULT 'free',
  max_storage_bytes BIGINT DEFAULT 104857600, -- 100MB
  used_storage_bytes BIGINT DEFAULT 0,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

company_invitations

Invitaciones pendientes al equipo.

CREATE TABLE company_invitations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  invited_by UUID REFERENCES profiles(id),
  
  email TEXT NOT NULL,
  role app_role DEFAULT 'member',
  token TEXT NOT NULL UNIQUE,
  
  status TEXT DEFAULT 'pending', -- 'pending' | 'accepted' | 'expired'
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_at TIMESTAMPTZ,
  
  created_at TIMESTAMPTZ DEFAULT now()
);

user_images

Biblioteca de imágenes por empresa.

CREATE TABLE user_images (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  uploaded_by UUID NOT NULL REFERENCES profiles(id),
  
  name TEXT NOT NULL,
  original_filename TEXT,
  storage_path TEXT NOT NULL,
  url TEXT NOT NULL,
  
  mime_type TEXT NOT NULL,
  file_size BIGINT NOT NULL,
  category TEXT DEFAULT 'general',
  is_favorite BOOLEAN DEFAULT false,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

company_templates

Plantillas de documentos personalizadas por empresa.

CREATE TABLE company_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  created_by UUID REFERENCES profiles(id),
  
  name TEXT NOT NULL,
  description TEXT,
  thumbnail TEXT,
  
  base_style TEXT DEFAULT 'professional', -- 'professional' | 'modern' | 'creative' | 'simple'
  blocks JSONB DEFAULT '[]',
  page_config JSONB, -- { pageSize, margins, orientation }
  
  is_default BOOLEAN DEFAULT false,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Políticas RLS

-- SELECT: Cualquier usuario autenticado puede ver plantillas
CREATE POLICY "Users can view company templates" 
ON company_templates FOR SELECT USING (true);

-- INSERT: Cualquier usuario autenticado puede crear plantillas
CREATE POLICY "Allow insert company templates" 
ON company_templates FOR INSERT WITH CHECK (true);

-- UPDATE: Solo el creador o admin puede actualizar
CREATE POLICY "Creator or admin can update templates" 
ON company_templates FOR UPDATE 
USING (created_by = get_profile_id_from_clerk(auth.jwt() ->> 'sub'));

-- DELETE: Solo el creador o admin puede eliminar
CREATE POLICY "Creator or admin can delete templates" 
ON company_templates FOR DELETE 
USING (created_by = get_profile_id_from_clerk(auth.jwt() ->> 'sub'));

Notas

El filtrado por company_id se realiza en la capa de aplicación
blocks contiene el array de bloques del documento en formato JSON
page_config almacena configuración de página (tamaño, márgenes, orientación)

knowledge_base

Base de conocimiento para IA.

CREATE TABLE knowledge_base (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  
  title TEXT NOT NULL,
  content TEXT,
  type TEXT DEFAULT 'document', -- 'document' | 'url' | 'text'
  file_url TEXT,
  metadata JSONB DEFAULT '{}',
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

hubspot_settings

Configuración de integración HubSpot.

CREATE TABLE hubspot_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id) UNIQUE,
  
  is_connected BOOLEAN DEFAULT false,
  portal_id TEXT,
  access_token TEXT,
  refresh_token TEXT,
  token_expires_at TIMESTAMPTZ,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

pipedream_connections

Conexiones de Pipedream.

CREATE TABLE pipedream_connections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  profile_id UUID NOT NULL REFERENCES profiles(id),
  
  app_name_slug TEXT NOT NULL,
  app_name_display TEXT NOT NULL,
  account_id TEXT NOT NULL,
  account_name TEXT,
  external_user_id TEXT NOT NULL,
  
  is_active BOOLEAN DEFAULT true,
  metadata JSONB DEFAULT '{}',
  
  connected_at TIMESTAMPTZ DEFAULT now(),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

workflows

Definiciones de flujos de automatización.

CREATE TABLE workflows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  created_by UUID REFERENCES profiles(id),
  
  name TEXT NOT NULL,
  description TEXT,
  
  trigger_type TEXT NOT NULL, -- 'status_change' | 'budget_created' | 'budget_expiring' | 'manual'
  trigger_config JSONB DEFAULT '{}', -- { fromStatus, toStatus, daysBeforeExpiry }
  
  definition JSONB NOT NULL DEFAULT '{}', -- { nodes: [], edges: [] } - React Flow format
  
  is_active BOOLEAN DEFAULT true,
  
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Trigger Types

Tipo

Descripción

Config

status_change

Cuando cambia el estado de propuesta

{ fromStatus, toStatus }

budget_created

Cuando se crea una propuesta

{}

budget_expiring

Días antes de vencimiento

{ daysBeforeExpiry }

manual

Ejecución manual

{}

Definition Structure (React Flow)

{
  "nodes": [
    {
      "id": "trigger-1",
      "type": "trigger",
      "data": { "label": "Propuesta Aprobada", "triggerType": "status_change" },
      "position": { "x": 100, "y": 100 }
    },
    {
      "id": "action-1",
      "type": "action",
      "data": { "label": "Enviar Email", "actionType": "send_email", "config": { "to": "..." } },
      "position": { "x": 100, "y": 200 }
    }
  ],
  "edges": [
    { "id": "e1", "source": "trigger-1", "target": "action-1" }
  ]
}

workflow_executions

Registro de ejecuciones de workflows.

CREATE TABLE workflow_executions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workflow_id UUID NOT NULL REFERENCES workflows(id),
  budget_id UUID REFERENCES budgets(id),
  
  status TEXT DEFAULT 'pending', -- 'pending' | 'running' | 'completed' | 'failed'
  trigger_data JSONB DEFAULT '{}',
  execution_log JSONB DEFAULT '[]', -- Array de pasos ejecutados
  error_message TEXT,
  
  started_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ
);

Execution Log Structure

[
  {
    "nodeId": "action-1",
    "nodeType": "action",
    "actionType": "send_email",
    "status": "completed",
    "result": { "emailId": "..." },
    "executedAt": "2024-01-15T10:00:00Z"
  }
]

payment_history

Historial de pagos.

CREATE TABLE payment_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID REFERENCES companies(id),
  
  reveniu_subscription_id INTEGER,
  reveniu_buy_order INTEGER,
  
  amount NUMERIC,
  currency TEXT DEFAULT 'CLP',
  status TEXT,
  paid_at TIMESTAMPTZ,
  
  created_at TIMESTAMPTZ DEFAULT now()
);

subscription_notifications

Registro de notificaciones enviadas.

CREATE TABLE subscription_notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(id),
  
  notification_type TEXT NOT NULL,
  sent_via TEXT[] DEFAULT ARRAY['in_app'],
  metadata JSONB DEFAULT '{}',
  
  sent_at TIMESTAMPTZ DEFAULT now()
);

Funciones de Base de Datos

Funciones de Autenticación

-- Obtener profile_id desde clerk_user_id
CREATE FUNCTION get_profile_id_from_clerk(_clerk_user_id TEXT)
RETURNS UUID AS $$
  SELECT id FROM profiles WHERE clerk_user_id = _clerk_user_id LIMIT 1
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Obtener company_id desde clerk_user_id
CREATE FUNCTION get_company_id_from_clerk(_clerk_user_id TEXT)
RETURNS UUID AS $$
  SELECT company_id FROM profiles WHERE clerk_user_id = _clerk_user_id LIMIT 1
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Verificar si pertenece a una empresa
CREATE FUNCTION belongs_to_company(_clerk_user_id TEXT, _company_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM profiles
    WHERE clerk_user_id = _clerk_user_id AND company_id = _company_id
  )
$$ LANGUAGE sql STABLE SECURITY DEFINER;

Funciones de Roles

-- Verificar si tiene rol específico
CREATE FUNCTION has_role_in_company(
  _clerk_user_id TEXT,
  _company_id UUID,
  _role app_role
) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN profiles p ON p.id = ur.profile_id
    WHERE p.clerk_user_id = _clerk_user_id
      AND ur.company_id = _company_id
      AND ur.role = _role
  )
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Verificar si tiene rol mínimo
CREATE FUNCTION has_min_role_in_company(
  _clerk_user_id TEXT,
  _company_id UUID,
  _min_role app_role
) RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles ur
    JOIN profiles p ON p.id = ur.profile_id
    WHERE p.clerk_user_id = _clerk_user_id
      AND ur.company_id = _company_id
      AND (
        (_min_role = 'viewer') OR
        (_min_role = 'member' AND ur.role IN ('member', 'manager', 'admin')) OR
        (_min_role = 'manager' AND ur.role IN ('manager', 'admin')) OR
        (_min_role = 'admin' AND ur.role = 'admin')
      )
  )
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Verificar super admin
CREATE FUNCTION is_super_admin(_clerk_user_id TEXT)
RETURNS BOOLEAN AS $$
  SELECT COALESCE(
    (SELECT is_super_admin FROM profiles WHERE clerk_user_id = _clerk_user_id),
    false
  )
$$ LANGUAGE sql STABLE SECURITY DEFINER;

Funciones de Presupuestos

-- Verificar si puede ver presupuesto
CREATE FUNCTION can_view_budget(_clerk_user_id TEXT, _budget_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM budgets b
    JOIN profiles p ON p.company_id = b.company_id
    JOIN user_roles ur ON ur.profile_id = p.id AND ur.company_id = b.company_id
    WHERE p.clerk_user_id = _clerk_user_id AND b.id = _budget_id
      AND (
        is_super_admin(_clerk_user_id) OR
        ur.role IN ('manager', 'admin') OR
        (ur.role IN ('member', 'viewer') AND b.created_by = p.id)
      )
  )
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Verificar si puede editar presupuesto
CREATE FUNCTION can_edit_budget(_clerk_user_id TEXT, _budget_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM budgets b
    JOIN profiles p ON p.company_id = b.company_id
    JOIN user_roles ur ON ur.profile_id = p.id AND ur.company_id = b.company_id
    WHERE p.clerk_user_id = _clerk_user_id AND b.id = _budget_id
      AND (
        is_super_admin(_clerk_user_id) OR
        ur.role IN ('admin', 'manager') OR
        (ur.role = 'member' AND b.created_by = p.id)
      )
  )
$$ LANGUAGE sql STABLE SECURITY DEFINER;

Funciones de Suscripción

-- Verificar límite de feature
CREATE FUNCTION check_feature_limit(p_company_id UUID, p_feature TEXT)
RETURNS JSONB AS $$
DECLARE
  v_current_usage INTEGER;
  v_limit INTEGER;
  v_plan_limits JSONB;
BEGIN
  -- Obtener límites del plan
  SELECT sp.limits INTO v_plan_limits
  FROM companies c
  JOIN subscription_plans sp ON sp.id = c.subscription_plan_id
  WHERE c.id = p_company_id;
  
  -- Obtener uso actual del período
  SELECT CASE p_feature
    WHEN 'budgets' THEN COALESCE(budgets_created, 0)
    WHEN 'ai' THEN COALESCE(ai_generations, 0)
  END INTO v_current_usage
  FROM company_usage
  WHERE company_id = p_company_id
    AND period_start = date_trunc('month', CURRENT_DATE);
  
  v_limit := (v_plan_limits->>p_feature||'_per_month')::INTEGER;
  
  RETURN jsonb_build_object(
    'current', COALESCE(v_current_usage, 0),
    'limit', COALESCE(v_limit, -1),
    'allowed', v_limit IS NULL OR v_current_usage < v_limit
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Incrementar uso
CREATE FUNCTION increment_company_usage(p_company_id UUID, p_usage_type TEXT)
RETURNS JSONB AS $$
  -- Ver implementación completa en la base de datos
$$ LANGUAGE plpgsql SECURITY DEFINER;

Políticas RLS

Ejemplo: budgets

-- Solo usuarios autenticados
ALTER TABLE budgets ENABLE ROW LEVEL SECURITY;

-- Política de lectura
CREATE POLICY "Users can view budgets they have access to"
ON budgets FOR SELECT
USING (
  can_view_budget(
    (current_setting('request.jwt.claims')::json->>'sub'),
    id
  )
);

-- Política de inserción
CREATE POLICY "Users can create budgets for their company"
ON budgets FOR INSERT
WITH CHECK (
  company_id = get_company_id_from_clerk(
    (current_setting('request.jwt.claims')::json->>'sub')
  )
);

-- Política de actualización
CREATE POLICY "Users can update budgets they can edit"
ON budgets FOR UPDATE
USING (
  can_edit_budget(
    (current_setting('request.jwt.claims')::json->>'sub'),
    id
  )
);

-- Política de eliminación
CREATE POLICY "Only admins can delete budgets"
ON budgets FOR DELETE
USING (
  has_min_role_in_company(
    (current_setting('request.jwt.claims')::json->>'sub'),
    company_id,
    'admin'
  )
);

Triggers

Actualizar updated_at

CREATE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_companies_updated_at
BEFORE UPDATE ON companies
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Actualizar uso de storage

CREATE FUNCTION update_storage_usage()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO company_storage_quotas (company_id, used_storage_bytes)
    VALUES (NEW.company_id, NEW.file_size)
    ON CONFLICT (company_id) 
    DO UPDATE SET used_storage_bytes = company_storage_quotas.used_storage_bytes + NEW.file_size;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE company_storage_quotas 
    SET used_storage_bytes = GREATEST(0, used_storage_bytes - OLD.file_size)
    WHERE company_id = OLD.company_id;
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER update_storage_on_image_change
AFTER INSERT OR DELETE ON user_images
FOR EACH ROW EXECUTE FUNCTION update_storage_usage();

Funciones de Datos Demo (v1.2.0)

seed_demo_data

Crea datos de demostración para nuevas empresas.

CREATE FUNCTION seed_demo_data(p_company_id UUID, p_profile_id UUID)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER;

-- Uso: SELECT seed_demo_data('company-uuid', 'profile-uuid');
-- Crea: 2 empresas cliente, 3 contactos, 3 propuestas, 1 meta de ventas

delete_demo_data

Elimina todos los datos de demostración de una empresa.

CREATE FUNCTION delete_demo_data(p_company_id UUID)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER;

-- Uso: SELECT delete_demo_data('company-uuid');
-- Elimina todos los registros con is_demo = true

Storage Buckets

Bucket

Público

Descripción

company-logos

✅

Logos de empresas

budget-images

✅

Imágenes en presupuestos

knowledge-files

❌

Archivos de conocimiento

Políticas de Storage

-- company-logos: público para lectura
CREATE POLICY "Logos are publicly accessible"
ON storage.objects FOR SELECT
USING (bucket_id = 'company-logos');

-- Solo usuarios de la empresa pueden subir
CREATE POLICY "Users can upload company logos"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'company-logos' AND
  (storage.foldername(name))[1] = get_company_id_from_clerk(
    (current_setting('request.jwt.claims')::json->>'sub')
  )::TEXT
);

AnteriorReferencia (interno)SiguienteAPI Reference - Edge Functions

Última actualización hace 27 días

Buenas noches

hashtagDiagrama Entidad-Relación

hashtagTablas Principales

hashtagcompanies

hashtagÍndices

hashtagprofiles

hashtagÍndices

hashtaguser_roles

hashtagJerarquía de Roles

hashtagbudgets

hashtagStatus Values

hashtagÍndices

hashtagbudget_versions

hashtagbudget_conversations

hashtagproducts

hashtagdiscount_presets

hashtagsales_goals

hashtagcommission_rules

hashtagcommission_calculations

hashtagsubscription_plans

hashtagEjemplo de Limits

hashtagcompany_usage

hashtagcompany_storage_quotas

hashtagcompany_invitations

hashtaguser_images

hashtagcompany_templates

hashtagPolíticas RLS

hashtagNotas

hashtagknowledge_base

hashtaghubspot_settings

hashtagpipedream_connections

hashtagworkflows

hashtagTrigger Types

hashtagDefinition Structure (React Flow)

hashtagworkflow_executions

hashtagExecution Log Structure

hashtagpayment_history

hashtagsubscription_notifications

hashtagFunciones de Base de Datos

hashtagFunciones de Autenticación

hashtagFunciones de Roles

hashtagFunciones de Presupuestos

hashtagFunciones de Suscripción

hashtagPolíticas RLS

hashtagEjemplo: budgets

hashtagTriggers

hashtagActualizar updated_at

hashtagActualizar uso de storage

hashtagFunciones de Datos Demo (v1.2.0)

hashtagseed_demo_data

hashtagdelete_demo_data

hashtagStorage Buckets

hashtagPolíticas de Storage

Diagrama Entidad-Relación

Tablas Principales

companies

Índices

profiles

Índices

user_roles

Jerarquía de Roles

budgets

Status Values

Índices

budget_versions

budget_conversations

products

discount_presets

sales_goals

commission_rules

commission_calculations

subscription_plans

Ejemplo de Limits

company_usage

company_storage_quotas

company_invitations

user_images

company_templates

Políticas RLS

Notas

knowledge_base

hubspot_settings

pipedream_connections

workflows

Trigger Types

Definition Structure (React Flow)

workflow_executions

Execution Log Structure

payment_history

subscription_notifications

Funciones de Base de Datos

Funciones de Autenticación

Funciones de Roles

Funciones de Presupuestos

Funciones de Suscripción

Políticas RLS

Ejemplo: budgets

Triggers

Actualizar updated_at

Actualizar uso de storage

Funciones de Datos Demo (v1.2.0)

seed_demo_data

delete_demo_data

Storage Buckets

Políticas de Storage