daabe5320a
- 6 tables: asset_categories, assets, asset_assignments, asset_audits, asset_audit_items, asset_maintenance - Multiple enums for status tracking - RLS policies for multi-tenancy - Triggers for automatic updates - Views for common queries Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
498 lines
17 KiB
PL/PgSQL
498 lines
17 KiB
PL/PgSQL
-- ============================================================================
|
|
-- ASSETS SCHEMA - MMD-013 Asset Management
|
|
-- Mecánicas Diesel - ERP Suite
|
|
-- ============================================================================
|
|
-- Schema for asset/tool management with assignment tracking, audits, and maintenance
|
|
-- ============================================================================
|
|
|
|
-- Create schema
|
|
CREATE SCHEMA IF NOT EXISTS assets;
|
|
|
|
-- Set search path
|
|
SET search_path TO assets, public;
|
|
|
|
-- ============================================================================
|
|
-- ENUMS
|
|
-- ============================================================================
|
|
|
|
CREATE TYPE asset_status AS ENUM (
|
|
'available',
|
|
'assigned',
|
|
'in_maintenance',
|
|
'damaged',
|
|
'retired'
|
|
);
|
|
|
|
CREATE TYPE asset_location AS ENUM (
|
|
'warehouse',
|
|
'unit',
|
|
'technician',
|
|
'external'
|
|
);
|
|
|
|
CREATE TYPE criticality_level AS ENUM (
|
|
'low',
|
|
'medium',
|
|
'high',
|
|
'critical'
|
|
);
|
|
|
|
CREATE TYPE assignee_type AS ENUM (
|
|
'employee',
|
|
'unit',
|
|
'location'
|
|
);
|
|
|
|
CREATE TYPE assignment_status AS ENUM (
|
|
'active',
|
|
'returned',
|
|
'overdue',
|
|
'lost',
|
|
'damaged'
|
|
);
|
|
|
|
CREATE TYPE audit_status AS ENUM (
|
|
'in_progress',
|
|
'completed',
|
|
'cancelled'
|
|
);
|
|
|
|
CREATE TYPE audit_item_status AS ENUM (
|
|
'found',
|
|
'missing',
|
|
'damaged',
|
|
'extra'
|
|
);
|
|
|
|
CREATE TYPE item_condition AS ENUM (
|
|
'good',
|
|
'fair',
|
|
'poor'
|
|
);
|
|
|
|
CREATE TYPE maintenance_type AS ENUM (
|
|
'preventive',
|
|
'corrective',
|
|
'calibration'
|
|
);
|
|
|
|
CREATE TYPE maintenance_status AS ENUM (
|
|
'scheduled',
|
|
'in_progress',
|
|
'completed',
|
|
'cancelled'
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- TABLE: asset_categories
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE asset_categories (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
parent_id UUID REFERENCES asset_categories(id) ON DELETE SET NULL,
|
|
requires_service_types TEXT[], -- Service types that require this category
|
|
sort_order INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_asset_categories_name UNIQUE (tenant_id, name)
|
|
);
|
|
|
|
CREATE INDEX idx_asset_categories_tenant ON asset_categories(tenant_id);
|
|
CREATE INDEX idx_asset_categories_parent ON asset_categories(parent_id);
|
|
|
|
-- ============================================================================
|
|
-- TABLE: assets
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE assets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
code VARCHAR(50) NOT NULL,
|
|
name VARCHAR(150) NOT NULL,
|
|
description TEXT,
|
|
category_id UUID NOT NULL REFERENCES asset_categories(id),
|
|
serial_number VARCHAR(100),
|
|
qr_code VARCHAR(100),
|
|
barcode VARCHAR(100),
|
|
manufacturer VARCHAR(100),
|
|
model VARCHAR(100),
|
|
purchase_date DATE,
|
|
purchase_cost DECIMAL(12, 2),
|
|
warranty_expiry DATE,
|
|
status asset_status DEFAULT 'available',
|
|
current_location asset_location DEFAULT 'warehouse',
|
|
current_assignee_id UUID,
|
|
current_assignee_type assignee_type,
|
|
criticality criticality_level DEFAULT 'medium',
|
|
requires_calibration BOOLEAN DEFAULT FALSE,
|
|
last_calibration_date DATE,
|
|
next_calibration_date DATE,
|
|
photo_url TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
created_by UUID,
|
|
|
|
CONSTRAINT uq_assets_code UNIQUE (tenant_id, code),
|
|
CONSTRAINT uq_assets_qr_code UNIQUE (tenant_id, qr_code),
|
|
CONSTRAINT uq_assets_barcode UNIQUE (tenant_id, barcode)
|
|
);
|
|
|
|
CREATE INDEX idx_assets_tenant ON assets(tenant_id);
|
|
CREATE INDEX idx_assets_category ON assets(category_id);
|
|
CREATE INDEX idx_assets_status ON assets(status);
|
|
CREATE INDEX idx_assets_location ON assets(current_location);
|
|
CREATE INDEX idx_assets_assignee ON assets(current_assignee_id);
|
|
CREATE INDEX idx_assets_qr ON assets(qr_code) WHERE qr_code IS NOT NULL;
|
|
CREATE INDEX idx_assets_barcode ON assets(barcode) WHERE barcode IS NOT NULL;
|
|
CREATE INDEX idx_assets_calibration ON assets(next_calibration_date) WHERE requires_calibration = TRUE;
|
|
|
|
-- ============================================================================
|
|
-- TABLE: asset_assignments
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE asset_assignments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
asset_id UUID NOT NULL REFERENCES assets(id),
|
|
assignee_id UUID NOT NULL,
|
|
assignee_type assignee_type NOT NULL,
|
|
assigned_at TIMESTAMPTZ DEFAULT NOW(),
|
|
assigned_by UUID NOT NULL,
|
|
expected_return_at TIMESTAMPTZ,
|
|
actual_return_at TIMESTAMPTZ,
|
|
returned_to UUID,
|
|
status assignment_status DEFAULT 'active',
|
|
assignment_photo_url TEXT,
|
|
return_photo_url TEXT,
|
|
notes TEXT,
|
|
signature_url TEXT,
|
|
incident_id UUID, -- FK to rescue_order if applicable
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
CONSTRAINT chk_return_dates CHECK (actual_return_at IS NULL OR actual_return_at >= assigned_at)
|
|
);
|
|
|
|
CREATE INDEX idx_asset_assignments_tenant ON asset_assignments(tenant_id);
|
|
CREATE INDEX idx_asset_assignments_asset ON asset_assignments(asset_id);
|
|
CREATE INDEX idx_asset_assignments_assignee ON asset_assignments(assignee_id, assignee_type);
|
|
CREATE INDEX idx_asset_assignments_status ON asset_assignments(status);
|
|
CREATE INDEX idx_asset_assignments_incident ON asset_assignments(incident_id) WHERE incident_id IS NOT NULL;
|
|
CREATE INDEX idx_asset_assignments_active ON asset_assignments(asset_id) WHERE status = 'active';
|
|
|
|
-- ============================================================================
|
|
-- TABLE: asset_audits
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE asset_audits (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
audit_date DATE NOT NULL,
|
|
auditor_id UUID NOT NULL,
|
|
location_id UUID,
|
|
unit_id UUID,
|
|
technician_id UUID,
|
|
status audit_status DEFAULT 'in_progress',
|
|
total_assets INTEGER DEFAULT 0,
|
|
found_assets INTEGER DEFAULT 0,
|
|
missing_assets INTEGER DEFAULT 0,
|
|
damaged_assets INTEGER DEFAULT 0,
|
|
notes TEXT,
|
|
started_at TIMESTAMPTZ DEFAULT NOW(),
|
|
completed_at TIMESTAMPTZ,
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_asset_audits_tenant ON asset_audits(tenant_id);
|
|
CREATE INDEX idx_asset_audits_date ON asset_audits(audit_date);
|
|
CREATE INDEX idx_asset_audits_auditor ON asset_audits(auditor_id);
|
|
CREATE INDEX idx_asset_audits_status ON asset_audits(status);
|
|
CREATE INDEX idx_asset_audits_unit ON asset_audits(unit_id) WHERE unit_id IS NOT NULL;
|
|
CREATE INDEX idx_asset_audits_technician ON asset_audits(technician_id) WHERE technician_id IS NOT NULL;
|
|
|
|
-- ============================================================================
|
|
-- TABLE: asset_audit_items
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE asset_audit_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
audit_id UUID NOT NULL REFERENCES asset_audits(id) ON DELETE CASCADE,
|
|
asset_id UUID REFERENCES assets(id),
|
|
status audit_item_status NOT NULL,
|
|
condition item_condition,
|
|
notes TEXT,
|
|
photo_url TEXT,
|
|
scanned_at TIMESTAMPTZ DEFAULT NOW(),
|
|
scanned_by UUID,
|
|
|
|
CONSTRAINT uq_audit_items_asset UNIQUE (audit_id, asset_id)
|
|
);
|
|
|
|
CREATE INDEX idx_asset_audit_items_audit ON asset_audit_items(audit_id);
|
|
CREATE INDEX idx_asset_audit_items_asset ON asset_audit_items(asset_id);
|
|
CREATE INDEX idx_asset_audit_items_status ON asset_audit_items(status);
|
|
|
|
-- ============================================================================
|
|
-- TABLE: asset_maintenance
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE asset_maintenance (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
asset_id UUID NOT NULL REFERENCES assets(id),
|
|
maintenance_type maintenance_type NOT NULL,
|
|
scheduled_date DATE,
|
|
performed_date DATE,
|
|
performed_by UUID,
|
|
external_vendor VARCHAR(200),
|
|
cost DECIMAL(12, 2),
|
|
description TEXT NOT NULL,
|
|
result TEXT,
|
|
next_maintenance_date DATE,
|
|
status maintenance_status DEFAULT 'scheduled',
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
created_by UUID
|
|
);
|
|
|
|
CREATE INDEX idx_asset_maintenance_tenant ON asset_maintenance(tenant_id);
|
|
CREATE INDEX idx_asset_maintenance_asset ON asset_maintenance(asset_id);
|
|
CREATE INDEX idx_asset_maintenance_type ON asset_maintenance(maintenance_type);
|
|
CREATE INDEX idx_asset_maintenance_status ON asset_maintenance(status);
|
|
CREATE INDEX idx_asset_maintenance_scheduled ON asset_maintenance(scheduled_date) WHERE status = 'scheduled';
|
|
|
|
-- ============================================================================
|
|
-- FUNCTIONS
|
|
-- ============================================================================
|
|
|
|
-- Function to update asset status when assigned
|
|
CREATE OR REPLACE FUNCTION update_asset_on_assignment()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.status = 'active' THEN
|
|
UPDATE assets
|
|
SET
|
|
status = 'assigned',
|
|
current_location = CASE NEW.assignee_type
|
|
WHEN 'employee' THEN 'technician'
|
|
WHEN 'unit' THEN 'unit'
|
|
ELSE 'external'
|
|
END,
|
|
current_assignee_id = NEW.assignee_id,
|
|
current_assignee_type = NEW.assignee_type,
|
|
updated_at = NOW()
|
|
WHERE id = NEW.asset_id;
|
|
ELSIF NEW.status IN ('returned', 'lost', 'damaged') AND OLD.status = 'active' THEN
|
|
UPDATE assets
|
|
SET
|
|
status = CASE NEW.status
|
|
WHEN 'lost' THEN 'retired'
|
|
WHEN 'damaged' THEN 'damaged'
|
|
ELSE 'available'
|
|
END,
|
|
current_location = 'warehouse',
|
|
current_assignee_id = NULL,
|
|
current_assignee_type = NULL,
|
|
updated_at = NOW()
|
|
WHERE id = NEW.asset_id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_asset_assignment_status
|
|
AFTER INSERT OR UPDATE OF status ON asset_assignments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_asset_on_assignment();
|
|
|
|
-- Function to update audit counts
|
|
CREATE OR REPLACE FUNCTION update_audit_counts()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
UPDATE asset_audits
|
|
SET
|
|
total_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id)),
|
|
found_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id) AND status = 'found'),
|
|
missing_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id) AND status = 'missing'),
|
|
damaged_assets = (SELECT COUNT(*) FROM asset_audit_items WHERE audit_id = COALESCE(NEW.audit_id, OLD.audit_id) AND status = 'damaged')
|
|
WHERE id = COALESCE(NEW.audit_id, OLD.audit_id);
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_audit_items_count
|
|
AFTER INSERT OR UPDATE OR DELETE ON asset_audit_items
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_audit_counts();
|
|
|
|
-- Function to auto-update timestamps
|
|
CREATE OR REPLACE FUNCTION assets_update_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_assets_updated_at
|
|
BEFORE UPDATE ON assets
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION assets_update_timestamp();
|
|
|
|
CREATE TRIGGER trg_asset_maintenance_updated_at
|
|
BEFORE UPDATE ON asset_maintenance
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION assets_update_timestamp();
|
|
|
|
CREATE TRIGGER trg_asset_categories_updated_at
|
|
BEFORE UPDATE ON asset_categories
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION assets_update_timestamp();
|
|
|
|
-- ============================================================================
|
|
-- VIEWS
|
|
-- ============================================================================
|
|
|
|
-- View: Assets with current assignment info
|
|
CREATE OR REPLACE VIEW vw_assets_with_assignment AS
|
|
SELECT
|
|
a.*,
|
|
c.name AS category_name,
|
|
aa.id AS active_assignment_id,
|
|
aa.assignee_id,
|
|
aa.assignee_type,
|
|
aa.assigned_at,
|
|
aa.expected_return_at,
|
|
CASE
|
|
WHEN aa.expected_return_at IS NOT NULL AND aa.expected_return_at < NOW()
|
|
THEN TRUE
|
|
ELSE FALSE
|
|
END AS is_overdue
|
|
FROM assets a
|
|
LEFT JOIN asset_categories c ON a.category_id = c.id
|
|
LEFT JOIN asset_assignments aa ON a.id = aa.asset_id AND aa.status = 'active';
|
|
|
|
-- View: Assets requiring calibration soon
|
|
CREATE OR REPLACE VIEW vw_assets_calibration_due AS
|
|
SELECT
|
|
a.id,
|
|
a.tenant_id,
|
|
a.code,
|
|
a.name,
|
|
a.next_calibration_date,
|
|
a.current_location,
|
|
a.current_assignee_id,
|
|
EXTRACT(DAY FROM a.next_calibration_date - NOW()) AS days_until_due
|
|
FROM assets a
|
|
WHERE a.requires_calibration = TRUE
|
|
AND a.status != 'retired'
|
|
AND a.next_calibration_date <= NOW() + INTERVAL '30 days'
|
|
ORDER BY a.next_calibration_date;
|
|
|
|
-- View: Asset assignment history
|
|
CREATE OR REPLACE VIEW vw_asset_assignment_history AS
|
|
SELECT
|
|
aa.*,
|
|
a.code AS asset_code,
|
|
a.name AS asset_name,
|
|
a.category_id,
|
|
c.name AS category_name
|
|
FROM asset_assignments aa
|
|
JOIN assets a ON aa.asset_id = a.id
|
|
JOIN asset_categories c ON a.category_id = c.id
|
|
ORDER BY aa.assigned_at DESC;
|
|
|
|
-- View: Audit summary
|
|
CREATE OR REPLACE VIEW vw_audit_summary AS
|
|
SELECT
|
|
au.id,
|
|
au.tenant_id,
|
|
au.audit_date,
|
|
au.auditor_id,
|
|
au.status,
|
|
au.total_assets,
|
|
au.found_assets,
|
|
au.missing_assets,
|
|
au.damaged_assets,
|
|
CASE
|
|
WHEN au.total_assets > 0
|
|
THEN ROUND((au.found_assets::DECIMAL / au.total_assets) * 100, 2)
|
|
ELSE 0
|
|
END AS accuracy_percentage,
|
|
au.started_at,
|
|
au.completed_at,
|
|
EXTRACT(EPOCH FROM (COALESCE(au.completed_at, NOW()) - au.started_at)) / 60 AS duration_minutes
|
|
FROM asset_audits au;
|
|
|
|
-- ============================================================================
|
|
-- ROW LEVEL SECURITY
|
|
-- ============================================================================
|
|
|
|
-- Enable RLS on all tables
|
|
ALTER TABLE asset_categories ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE assets ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE asset_assignments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE asset_audits ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE asset_audit_items ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE asset_maintenance ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create policies for tenant isolation
|
|
CREATE POLICY asset_categories_tenant_isolation ON asset_categories
|
|
USING (tenant_id = current_setting('app.current_tenant')::UUID);
|
|
|
|
CREATE POLICY assets_tenant_isolation ON assets
|
|
USING (tenant_id = current_setting('app.current_tenant')::UUID);
|
|
|
|
CREATE POLICY asset_assignments_tenant_isolation ON asset_assignments
|
|
USING (tenant_id = current_setting('app.current_tenant')::UUID);
|
|
|
|
CREATE POLICY asset_audits_tenant_isolation ON asset_audits
|
|
USING (tenant_id = current_setting('app.current_tenant')::UUID);
|
|
|
|
CREATE POLICY asset_audit_items_tenant_isolation ON asset_audit_items
|
|
USING (audit_id IN (SELECT id FROM asset_audits WHERE tenant_id = current_setting('app.current_tenant')::UUID));
|
|
|
|
CREATE POLICY asset_maintenance_tenant_isolation ON asset_maintenance
|
|
USING (tenant_id = current_setting('app.current_tenant')::UUID);
|
|
|
|
-- ============================================================================
|
|
-- SEED DATA (sample categories)
|
|
-- ============================================================================
|
|
|
|
-- Note: In production, seed data should be added via migration scripts
|
|
-- These are example categories for reference:
|
|
/*
|
|
INSERT INTO asset_categories (tenant_id, name, description, sort_order) VALUES
|
|
('00000000-0000-0000-0000-000000000001', 'Herramientas Manuales', 'Llaves, desarmadores, pinzas', 1),
|
|
('00000000-0000-0000-0000-000000000001', 'Herramientas Eléctricas', 'Taladros, esmeriles, etc.', 2),
|
|
('00000000-0000-0000-0000-000000000001', 'Equipos de Diagnóstico', 'Scanners, multímetros', 3),
|
|
('00000000-0000-0000-0000-000000000001', 'Equipos de Rescate', 'Grúas, gatos hidráulicos', 4),
|
|
('00000000-0000-0000-0000-000000000001', 'Equipos de Seguridad', 'EPP, señalización', 5),
|
|
('00000000-0000-0000-0000-000000000001', 'Equipos de Medición', 'Calibradores, manómetros', 6);
|
|
*/
|
|
|
|
-- ============================================================================
|
|
-- GRANTS (adjust based on your database roles)
|
|
-- ============================================================================
|
|
|
|
-- Grant usage on schema
|
|
-- GRANT USAGE ON SCHEMA assets TO app_user;
|
|
-- GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA assets TO app_user;
|
|
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA assets TO app_user;
|
|
|
|
COMMENT ON SCHEMA assets IS 'MMD-013: Asset and tool management module';
|
|
COMMENT ON TABLE assets IS 'Master table for all trackable assets and tools';
|
|
COMMENT ON TABLE asset_categories IS 'Hierarchical categorization of assets';
|
|
COMMENT ON TABLE asset_assignments IS 'Track asset assignments to employees, units, or locations';
|
|
COMMENT ON TABLE asset_audits IS 'Physical inventory audits';
|
|
COMMENT ON TABLE asset_audit_items IS 'Individual items checked during an audit';
|
|
COMMENT ON TABLE asset_maintenance IS 'Maintenance records including calibration';
|