613fb2d787
Implements the full ADMIN → ORGANISMO_OPERADOR → OPERATOR permission hierarchy with scope-filtered data access across all backend services. Adds organismos operadores management (ADMIN only) and a new Histórico page for viewing per-meter reading history with chart, consumption stats, and CSV export. Key changes: - Backend: 3-level scope filtering on all services (meters, readings, projects, users) - Backend: Protect GET /meters routes with authenticateToken for role-based filtering - Backend: Pass requestingUser to reading service for scoped meter readings - Frontend: New HistoricoPage with meter selector, AreaChart, paginated table - Frontend: Consumption cards (Actual, Pasado, Diferencial) above date filters - Frontend: Meter search by name, serial, location, CESPT account, cadastral key - Frontend: OrganismosPage, updated Sidebar with 3-level visibility - SQL migrations for organismos_operadores table and FK columns Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
67 lines
2.9 KiB
SQL
67 lines
2.9 KiB
SQL
-- ============================================
|
|
-- Migration: Add Organismos Operadores (3-level hierarchy)
|
|
-- Admin → Organismo Operador → Operador
|
|
-- ============================================
|
|
|
|
-- 1. Add ORGANISMO_OPERADOR to role_name ENUM
|
|
-- NOTE: ALTER TYPE ADD VALUE cannot run inside a transaction block
|
|
ALTER TYPE role_name ADD VALUE IF NOT EXISTS 'ORGANISMO_OPERADOR';
|
|
|
|
-- 2. Create organismos_operadores table
|
|
CREATE TABLE IF NOT EXISTS organismos_operadores (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
region VARCHAR(255),
|
|
contact_name VARCHAR(255),
|
|
contact_email VARCHAR(255),
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Add updated_at trigger
|
|
CREATE TRIGGER set_organismos_operadores_updated_at
|
|
BEFORE UPDATE ON organismos_operadores
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Index for active organismos
|
|
CREATE INDEX IF NOT EXISTS idx_organismos_operadores_active ON organismos_operadores (is_active);
|
|
|
|
-- 3. Add organismo_operador_id FK to projects table
|
|
ALTER TABLE projects
|
|
ADD COLUMN IF NOT EXISTS organismo_operador_id UUID REFERENCES organismos_operadores(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_projects_organismo_operador_id ON projects (organismo_operador_id);
|
|
|
|
-- 4. Add organismo_operador_id FK to users table
|
|
ALTER TABLE users
|
|
ADD COLUMN IF NOT EXISTS organismo_operador_id UUID REFERENCES organismos_operadores(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_organismo_operador_id ON users (organismo_operador_id);
|
|
|
|
-- 5. Insert ORGANISMO_OPERADOR role with permissions
|
|
INSERT INTO roles (name, description, permissions)
|
|
SELECT
|
|
'ORGANISMO_OPERADOR',
|
|
'Organismo operador que gestiona proyectos y operadores dentro de su jurisdicción',
|
|
'["projects:read", "projects:list", "concentrators:read", "concentrators:list", "meters:read", "meters:write", "meters:list", "readings:read", "readings:list", "users:read", "users:write", "users:list"]'::jsonb
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM roles WHERE name = 'ORGANISMO_OPERADOR'
|
|
);
|
|
|
|
-- 6. Migrate VIEWER users to OPERATOR role
|
|
UPDATE users
|
|
SET role_id = (SELECT id FROM roles WHERE name = 'OPERATOR' LIMIT 1)
|
|
WHERE role_id = (SELECT id FROM roles WHERE name = 'VIEWER' LIMIT 1);
|
|
|
|
-- 7. Seed example organismos operadores
|
|
INSERT INTO organismos_operadores (name, description, region, contact_name, contact_email)
|
|
SELECT 'CESPT', 'Comisión Estatal de Servicios Públicos de Tijuana', 'Tijuana, BC', 'Admin CESPT', 'admin@cespt.gob.mx'
|
|
WHERE NOT EXISTS (SELECT 1 FROM organismos_operadores WHERE name = 'CESPT');
|
|
|
|
INSERT INTO organismos_operadores (name, description, region, contact_name, contact_email)
|
|
SELECT 'XICALI', 'Organismo Operador de Mexicali', 'Mexicali, BC', 'Admin XICALI', 'admin@xicali.gob.mx'
|
|
WHERE NOT EXISTS (SELECT 1 FROM organismos_operadores WHERE name = 'XICALI');
|