65 lines
2.7 KiB
MySQL
65 lines
2.7 KiB
MySQL
|
-- Migration: Create RMM Site Mappings Table
|
||
|
|
-- Purpose: Support mapping multiple RMM sites to a single PSA company
|
||
|
|
-- Date: 2024-11-05
|
||
|
|
|
||
|
|
-- Create the RMM site mappings table
|
||
|
|
CREATE TABLE IF NOT EXISTS rmm_site_mappings (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
rmm_site_uid VARCHAR(255) NOT NULL,
|
||
|
|
rmm_site_name VARCHAR(255) NOT NULL,
|
||
|
|
is_primary BOOLEAN DEFAULT false,
|
||
|
|
device_count INTEGER DEFAULT 0, -- Cache device count for performance
|
||
|
|
notes TEXT,
|
||
|
|
last_sync_at TIMESTAMP,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
created_by VARCHAR(255),
|
||
|
|
UNIQUE(company_id, rmm_site_uid)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create indexes for better query performance
|
||
|
|
CREATE INDEX idx_rmm_site_mappings_company ON rmm_site_mappings(company_id);
|
||
|
|
CREATE INDEX idx_rmm_site_mappings_site_uid ON rmm_site_mappings(rmm_site_uid);
|
||
|
|
CREATE INDEX idx_rmm_site_mappings_is_primary ON rmm_site_mappings(is_primary) WHERE is_primary = true;
|
||
|
|
|
||
|
|
-- Add comments for documentation
|
||
|
|
COMMENT ON TABLE rmm_site_mappings IS 'Maps RMM sites to PSA companies, supporting multiple sites per company';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.company_id IS 'Foreign key to companies table';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.rmm_site_uid IS 'Unique identifier from RMM system';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.rmm_site_name IS 'Display name of the RMM site';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.is_primary IS 'Indicates if this is the primary site for the company';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.device_count IS 'Cached count of devices in this site';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.notes IS 'Optional notes about the site mapping';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.last_sync_at IS 'Timestamp of last successful device sync';
|
||
|
|
COMMENT ON COLUMN rmm_site_mappings.created_by IS 'User who created the mapping';
|
||
|
|
|
||
|
|
-- Create trigger to update the updated_at timestamp
|
||
|
|
CREATE OR REPLACE FUNCTION update_rmm_site_mappings_updated_at()
|
||
|
|
RETURNS TRIGGER AS $$
|
||
|
|
BEGIN
|
||
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER update_rmm_site_mappings_updated_at
|
||
|
|
BEFORE UPDATE ON rmm_site_mappings
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION update_rmm_site_mappings_updated_at();
|
||
|
|
|
||
|
|
-- Create a view for easier querying with company names
|
||
|
|
CREATE OR REPLACE VIEW rmm_site_mappings_view AS
|
||
|
|
SELECT
|
||
|
|
rsm.*,
|
||
|
|
c.company_name,
|
||
|
|
c.company_number,
|
||
|
|
c.is_active AS company_is_active
|
||
|
|
FROM rmm_site_mappings rsm
|
||
|
|
JOIN companies c ON c.id = rsm.company_id;
|
||
|
|
|
||
|
|
-- Grant permissions (adjust as needed for your setup)
|
||
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON rmm_site_mappings TO PUBLIC;
|
||
|
|
GRANT SELECT ON rmm_site_mappings_view TO PUBLIC;
|
||
|
|
GRANT USAGE, SELECT ON SEQUENCE rmm_site_mappings_id_seq TO PUBLIC;
|