lorentz
c518eefdb2
- Add MorningSummaryService with Zabbix aggregation and adaptive card builder - Add webhook delivery system with Teams incoming webhooks - Add admin UI at /admin/morning-summary for webhook/config management - Add API routes: /send, /test, /webhooks, /webhooks/[id], /config, /history - Register morning-summary cron job in SyncScheduler (Mon-Fri 6:30 AM) - Add outages_only filter (Unavailable triggers only) - Fix host resolution: use getTriggerEnabledHosts to exclude disabled hosts - Fix resolved events: event.get value:1 scoped to window with r_eventid filter - Remove emojis from fact rows and section headers in card - Remove Open Zabbix button (duplicate of View Problems) - Add migrations: morning_summary_config + morning_summaries tables - Add outages_only column to morning_summary_config
37 lines
1.3 KiB
SQL
37 lines
1.3 KiB
SQL
-- Migration 041: Create engagement tables for Microsoft Graph activity data
|
|
|
|
CREATE TABLE IF NOT EXISTS graph_users (
|
|
id VARCHAR(255) PRIMARY KEY, -- Azure AD object ID
|
|
display_name VARCHAR(255),
|
|
email VARCHAR(255) UNIQUE, -- matches resources.email
|
|
job_title VARCHAR(255),
|
|
department VARCHAR(255),
|
|
account_enabled BOOLEAN DEFAULT true,
|
|
last_activity_date DATE,
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS engagement_snapshots (
|
|
id SERIAL PRIMARY KEY,
|
|
user_email VARCHAR(255) NOT NULL,
|
|
period_type VARCHAR(10) NOT NULL, -- 'D7', 'D30', 'D90'
|
|
period_end DATE NOT NULL,
|
|
-- Teams
|
|
teams_chat_messages INT DEFAULT 0,
|
|
teams_private_messages INT DEFAULT 0,
|
|
teams_calls INT DEFAULT 0,
|
|
teams_meetings_attended INT DEFAULT 0,
|
|
teams_meetings_organized INT DEFAULT 0,
|
|
-- Email
|
|
emails_sent INT DEFAULT 0,
|
|
emails_received INT DEFAULT 0,
|
|
emails_read INT DEFAULT 0,
|
|
-- Last activity
|
|
last_activity_date DATE,
|
|
synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(user_email, period_type, period_end)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_engagement_snapshots_email ON engagement_snapshots(user_email);
|
|
CREATE INDEX IF NOT EXISTS idx_engagement_snapshots_period ON engagement_snapshots(period_type, period_end);
|
|
CREATE INDEX IF NOT EXISTS idx_graph_users_email ON graph_users(email);
|