rckrdmrd
57f41859de
Add complete projects schema with timesheets support: - projects.projects table with status, privacy settings - projects.tasks table with Kanban support - projects.timesheets table for Odoo-style time tracking - projects.milestones table for project milestones - projects.project_stages table for Kanban columns - projects.project_members table for team management Includes all necessary indexes and enums for performance. Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
382 lines
14 KiB
SQL
382 lines
14 KiB
SQL
-- =============================================================
|
|
-- ARCHIVO: 60-projects-timesheets.sql
|
|
-- DESCRIPCION: Schema de proyectos con soporte para timesheets
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-01-20
|
|
-- DEPENDE DE: auth schema (tenants, users, companies)
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- SCHEMA: projects
|
|
-- Schema para modulo de gestion de proyectos
|
|
-- =====================
|
|
CREATE SCHEMA IF NOT EXISTS projects;
|
|
|
|
-- =====================
|
|
-- EXTENSIONES REQUERIDAS
|
|
-- =====================
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- =====================
|
|
-- TIPOS ENUMERADOS
|
|
-- =====================
|
|
|
|
-- Estado del proyecto
|
|
DO $$ BEGIN
|
|
CREATE TYPE projects.project_status_enum AS ENUM (
|
|
'draft', -- Borrador
|
|
'active', -- Activo
|
|
'completed', -- Completado
|
|
'cancelled', -- Cancelado
|
|
'on_hold' -- En espera
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- Privacidad del proyecto
|
|
DO $$ BEGIN
|
|
CREATE TYPE projects.project_privacy_enum AS ENUM (
|
|
'public', -- Publico (visible para todos)
|
|
'private', -- Privado (solo miembros)
|
|
'followers' -- Solo seguidores
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- Estado de la tarea
|
|
DO $$ BEGIN
|
|
CREATE TYPE projects.task_status_enum AS ENUM (
|
|
'todo', -- Por hacer
|
|
'in_progress', -- En progreso
|
|
'review', -- En revision
|
|
'done', -- Completada
|
|
'cancelled' -- Cancelada
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- Prioridad de la tarea
|
|
DO $$ BEGIN
|
|
CREATE TYPE projects.task_priority_enum AS ENUM (
|
|
'low', -- Baja
|
|
'normal', -- Normal
|
|
'high', -- Alta
|
|
'urgent' -- Urgente
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- Estado del timesheet
|
|
DO $$ BEGIN
|
|
CREATE TYPE projects.timesheet_status_enum AS ENUM (
|
|
'draft', -- Borrador
|
|
'submitted', -- Enviado para aprobacion
|
|
'approved', -- Aprobado
|
|
'rejected' -- Rechazado
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- Estado del milestone
|
|
DO $$ BEGIN
|
|
CREATE TYPE projects.milestone_status_enum AS ENUM (
|
|
'pending', -- Pendiente
|
|
'completed', -- Completado
|
|
'cancelled' -- Cancelado
|
|
);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- =====================
|
|
-- TABLA: projects
|
|
-- Proyectos
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS projects.projects (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(255) NOT NULL,
|
|
code VARCHAR(50),
|
|
description TEXT,
|
|
|
|
-- Responsables
|
|
manager_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
partner_id UUID, -- Cliente asociado al proyecto
|
|
|
|
-- Cuenta analitica (para integracion contable)
|
|
analytic_account_id UUID,
|
|
|
|
-- Fechas
|
|
date_start DATE,
|
|
date_end DATE,
|
|
|
|
-- Estado y configuracion
|
|
status projects.project_status_enum DEFAULT 'draft',
|
|
privacy projects.project_privacy_enum DEFAULT 'public',
|
|
allow_timesheets BOOLEAN DEFAULT TRUE,
|
|
color VARCHAR(20),
|
|
|
|
-- Audit columns
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES auth.users(id),
|
|
|
|
-- Constraint de unicidad por codigo dentro de company
|
|
UNIQUE(company_id, code)
|
|
);
|
|
|
|
-- Indices para projects
|
|
CREATE INDEX IF NOT EXISTS idx_projects_tenant ON projects.projects(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_company ON projects.projects(company_id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_manager ON projects.projects(manager_id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_partner ON projects.projects(partner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects.projects(status);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_active ON projects.projects(tenant_id) WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_projects_code ON projects.projects(code);
|
|
|
|
-- =====================
|
|
-- TABLA: project_stages
|
|
-- Etapas/columnas del tablero Kanban
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS projects.project_stages (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Puede ser global o por proyecto
|
|
project_id UUID REFERENCES projects.projects(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(100) NOT NULL,
|
|
sequence INT DEFAULT 0,
|
|
|
|
-- Configuracion
|
|
fold BOOLEAN DEFAULT FALSE, -- Columna plegada por defecto
|
|
is_closed BOOLEAN DEFAULT FALSE, -- Indica etapa de cierre
|
|
|
|
-- Audit columns
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para project_stages
|
|
CREATE INDEX IF NOT EXISTS idx_project_stages_tenant ON projects.project_stages(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_stages_project ON projects.project_stages(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_stages_sequence ON projects.project_stages(sequence);
|
|
|
|
-- =====================
|
|
-- TABLA: tasks
|
|
-- Tareas del proyecto
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS projects.tasks (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Relaciones
|
|
project_id UUID NOT NULL REFERENCES projects.projects(id) ON DELETE CASCADE,
|
|
stage_id UUID REFERENCES projects.project_stages(id) ON DELETE SET NULL,
|
|
parent_id UUID REFERENCES projects.tasks(id) ON DELETE SET NULL,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Asignacion
|
|
assigned_to UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
|
|
-- Fechas
|
|
date_deadline DATE,
|
|
|
|
-- Estimacion y seguimiento
|
|
estimated_hours DECIMAL(10,2) DEFAULT 0,
|
|
|
|
-- Estado y prioridad
|
|
priority projects.task_priority_enum DEFAULT 'normal',
|
|
status projects.task_status_enum DEFAULT 'todo',
|
|
|
|
-- Ordenamiento
|
|
sequence INT DEFAULT 0,
|
|
color VARCHAR(20),
|
|
|
|
-- Audit columns
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para tasks
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_tenant ON projects.tasks(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_project ON projects.tasks(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_stage ON projects.tasks(stage_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_parent ON projects.tasks(parent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_assigned ON projects.tasks(assigned_to);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_status ON projects.tasks(status);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_priority ON projects.tasks(priority);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_deadline ON projects.tasks(date_deadline);
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_active ON projects.tasks(tenant_id, project_id) WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_sequence ON projects.tasks(project_id, sequence);
|
|
|
|
-- =====================
|
|
-- TABLA: milestones
|
|
-- Hitos del proyecto
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS projects.milestones (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Relacion
|
|
project_id UUID NOT NULL REFERENCES projects.projects(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
|
|
-- Fecha objetivo
|
|
date_deadline DATE,
|
|
|
|
-- Estado
|
|
status projects.milestone_status_enum DEFAULT 'pending',
|
|
|
|
-- Audit columns
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para milestones
|
|
CREATE INDEX IF NOT EXISTS idx_milestones_tenant ON projects.milestones(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_milestones_project ON projects.milestones(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_milestones_status ON projects.milestones(status);
|
|
CREATE INDEX IF NOT EXISTS idx_milestones_deadline ON projects.milestones(date_deadline);
|
|
|
|
-- =====================
|
|
-- TABLA: timesheets
|
|
-- Registro de horas trabajadas (estilo Odoo)
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS projects.timesheets (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL,
|
|
|
|
-- Relaciones
|
|
project_id UUID NOT NULL REFERENCES projects.projects(id) ON DELETE CASCADE,
|
|
task_id UUID REFERENCES projects.tasks(id) ON DELETE SET NULL,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- Datos del registro
|
|
date DATE NOT NULL,
|
|
hours DECIMAL(5,2) NOT NULL CHECK (hours >= 0 AND hours <= 24),
|
|
description TEXT,
|
|
|
|
-- Facturacion
|
|
billable BOOLEAN DEFAULT TRUE,
|
|
invoiced BOOLEAN DEFAULT FALSE,
|
|
invoice_id UUID, -- FK a factura cuando se facture
|
|
|
|
-- Aprobacion
|
|
status projects.timesheet_status_enum DEFAULT 'draft',
|
|
approved_by UUID REFERENCES auth.users(id),
|
|
approved_at TIMESTAMPTZ,
|
|
|
|
-- Audit columns
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para timesheets
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_tenant ON projects.timesheets(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_company ON projects.timesheets(company_id);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_project ON projects.timesheets(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_task ON projects.timesheets(task_id);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_user ON projects.timesheets(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_user_date ON projects.timesheets(user_id, date);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_date ON projects.timesheets(date);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_status ON projects.timesheets(status);
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_billable ON projects.timesheets(billable) WHERE billable = TRUE;
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_not_invoiced ON projects.timesheets(project_id, invoiced) WHERE invoiced = FALSE;
|
|
CREATE INDEX IF NOT EXISTS idx_timesheets_invoice ON projects.timesheets(invoice_id);
|
|
|
|
-- =====================
|
|
-- TABLA: project_members
|
|
-- Miembros del equipo del proyecto
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS projects.project_members (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Relaciones
|
|
project_id UUID NOT NULL REFERENCES projects.projects(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- Rol en el proyecto
|
|
role VARCHAR(50) DEFAULT 'member', -- member, contributor, viewer
|
|
|
|
-- Audit columns
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
-- Un usuario solo puede ser miembro una vez por proyecto
|
|
UNIQUE(project_id, user_id)
|
|
);
|
|
|
|
-- Indices para project_members
|
|
CREATE INDEX IF NOT EXISTS idx_project_members_tenant ON projects.project_members(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_members_project ON projects.project_members(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_members_user ON projects.project_members(user_id);
|
|
|
|
-- =====================
|
|
-- COMENTARIOS
|
|
-- =====================
|
|
COMMENT ON SCHEMA projects IS 'Schema para gestion de proyectos: proyectos, tareas, timesheets, milestones';
|
|
|
|
COMMENT ON TABLE projects.projects IS 'Proyectos con seguimiento de tareas y timesheets';
|
|
COMMENT ON COLUMN projects.projects.allow_timesheets IS 'TRUE si el proyecto permite registro de horas';
|
|
COMMENT ON COLUMN projects.projects.analytic_account_id IS 'Cuenta analitica para integracion contable';
|
|
|
|
COMMENT ON TABLE projects.project_stages IS 'Etapas del tablero Kanban (columnas)';
|
|
COMMENT ON COLUMN projects.project_stages.fold IS 'TRUE si la columna debe mostrarse plegada';
|
|
COMMENT ON COLUMN projects.project_stages.is_closed IS 'TRUE si representa una etapa de cierre/completado';
|
|
|
|
COMMENT ON TABLE projects.tasks IS 'Tareas asignables a proyectos';
|
|
COMMENT ON COLUMN projects.tasks.estimated_hours IS 'Horas estimadas para completar la tarea';
|
|
COMMENT ON COLUMN projects.tasks.parent_id IS 'Referencia a tarea padre para subtareas';
|
|
|
|
COMMENT ON TABLE projects.milestones IS 'Hitos importantes del proyecto';
|
|
|
|
COMMENT ON TABLE projects.timesheets IS 'Registro de horas trabajadas estilo Odoo';
|
|
COMMENT ON COLUMN projects.timesheets.hours IS 'Horas trabajadas (0-24 por dia)';
|
|
COMMENT ON COLUMN projects.timesheets.billable IS 'TRUE si las horas son facturables al cliente';
|
|
COMMENT ON COLUMN projects.timesheets.invoiced IS 'TRUE si ya fue incluido en una factura';
|
|
COMMENT ON COLUMN projects.timesheets.status IS 'Flujo de aprobacion: draft -> submitted -> approved/rejected';
|
|
|
|
COMMENT ON TABLE projects.project_members IS 'Miembros del equipo del proyecto';
|
|
COMMENT ON COLUMN projects.project_members.role IS 'Rol: member (puede editar), contributor (puede agregar), viewer (solo lectura)';
|