Source	Traffic	Engaged	Eng. rate	Avg dwell	Quick exits	QE rate

Sensors auto-provision to Join APs via ODN SSID. Station MAC matches sensor MAC — confirming link between WiFi session and MQTT data stream.

Status	Station MAC (sensor)	AP MAC	Site	SSID	RSSI	Signal	Channel	fw version	Connected

-- OPTiFi PostgreSQL schema (AWS RDS ca-central-1) -- Run as: psql -h your-rds-endpoint.ca-central-1.rds.amazonaws.com -U optifi_admin -d optifi CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION IF NOT EXISTS timescaledb; -- ENUMS CREATE TYPE account_type AS ENUM ('retail','msp','brand','platform'); CREATE TYPE user_role AS ENUM ('superadmin','msp_admin','retail_admin','insights_only','brand_viewer'); CREATE TYPE sensor_status AS ENUM ('online','warn','offline','staging'); CREATE TYPE presence_event_type AS ENUM ('presence_start','presence_end','zone_enter','zone_exit','target_move'); CREATE TYPE share_access AS ENUM ('data_only','data_uptime','full_read'); CREATE TYPE share_status AS ENUM ('pending','approved','denied'); -- accounts (multi-tenant root) CREATE TABLE accounts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, type account_type NOT NULL, admin_email TEXT, alert_email TEXT, store_code TEXT, parent_id UUID REFERENCES accounts(id), active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ DEFAULT now() ); -- sensor_groups CREATE TABLE sensor_groups ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, name TEXT NOT NULL, site_code TEXT, location TEXT, is_shared BOOLEAN DEFAULT false, created_at TIMESTAMPTZ DEFAULT now() ); -- sensors CREATE TABLE sensors ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), mac_address MACADDR UNIQUE NOT NULL, group_id UUID REFERENCES sensor_groups(id), account_id UUID NOT NULL REFERENCES accounts(id), alias TEXT, firmware_version TEXT, capture_profile TEXT DEFAULT 'medium', zone_config JSONB, gate_config JSONB, join_ap_mac MACADDR, status sensor_status DEFAULT 'staging', last_seen TIMESTAMPTZ, provisioned_at TIMESTAMPTZ DEFAULT now() ); -- sensor_telemetry (time-series, partitioned) CREATE TABLE sensor_telemetry ( id BIGSERIAL, sensor_id UUID NOT NULL REFERENCES sensors(id), ts TIMESTAMPTZ NOT NULL DEFAULT now(), rssi SMALLINT, heap_free INTEGER, uptime_s INTEGER, mqtt_connected BOOLEAN, wifi_ssid TEXT, battery_mv SMALLINT, PRIMARY KEY (id, ts) ); SELECT create_hypertable('sensor_telemetry','ts', chunk_time_interval => INTERVAL '1 day'); -- presence_events (core analytics) CREATE TABLE presence_events ( id BIGSERIAL, sensor_id UUID NOT NULL REFERENCES sensors(id), account_id UUID NOT NULL REFERENCES accounts(id), event_ts TIMESTAMPTZ NOT NULL DEFAULT now(), event_type presence_event_type NOT NULL, targets_count SMALLINT DEFAULT 0, zone_id SMALLINT, dwell_seconds INTEGER, is_engaged BOOLEAN, quick_exit BOOLEAN, raw_payload JSONB, PRIMARY KEY (id, event_ts) ); SELECT create_hypertable('presence_events','event_ts', chunk_time_interval => INTERVAL '1 day'); CREATE INDEX ON presence_events (sensor_id, event_ts DESC); CREATE INDEX ON presence_events (account_id, event_ts DESC); -- zone_analytics_hourly (materialized, refreshed by pg_cron) CREATE MATERIALIZED VIEW zone_analytics_hourly AS SELECT time_bucket('1 hour', event_ts) AS bucket, sensor_id, account_id, zone_id, COUNT(*) AS total_count, COUNT(*) FILTER (WHERE is_engaged) AS engaged_count, COUNT(*) FILTER (WHERE quick_exit) AS quick_exit_count, AVG(dwell_seconds)::NUMERIC(8,1) AS avg_dwell_s, MAX(targets_count) AS max_targets FROM presence_events GROUP BY 1,2,3,4; CREATE UNIQUE INDEX ON zone_analytics_hourly (bucket, sensor_id, zone_id); -- Refresh: SELECT cron.schedule('refresh-zone-analytics','*/15 * * * *',$$REFRESH MATERIALIZED VIEW CONCURRENTLY zone_analytics_hourly$$); -- users CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), account_id UUID NOT NULL REFERENCES accounts(id), email TEXT UNIQUE NOT NULL, name TEXT NOT NULL, role user_role NOT NULL DEFAULT 'insights_only', password_hash TEXT, mfa_secret TEXT, permissions JSONB DEFAULT '{}', active BOOLEAN DEFAULT true, last_login TIMESTAMPTZ, invited_by UUID REFERENCES users(id), created_at TIMESTAMPTZ DEFAULT now() ); -- Row-level security: users only see their own account's data ALTER TABLE sensors ENABLE ROW LEVEL SECURITY; ALTER TABLE presence_events ENABLE ROW LEVEL SECURITY; ALTER TABLE sensor_telemetry ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON sensors USING (account_id = current_setting('app.current_account_id')::UUID); CREATE POLICY tenant_isolation ON presence_events USING (account_id = current_setting('app.current_account_id')::UUID); -- sensor_shares (cross-account) CREATE TABLE sensor_shares ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), group_id UUID NOT NULL REFERENCES sensor_groups(id), from_account_id UUID NOT NULL REFERENCES accounts(id), to_account_id UUID NOT NULL REFERENCES accounts(id), access_level share_access NOT NULL DEFAULT 'data_only', status share_status NOT NULL DEFAULT 'pending', approved_by UUID REFERENCES users(id), created_at TIMESTAMPTZ DEFAULT now(), expires_at TIMESTAMPTZ, UNIQUE (group_id, to_account_id) ); -- alert_rules CREATE TABLE alert_rules ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), account_id UUID NOT NULL REFERENCES accounts(id), rule_type TEXT NOT NULL, scope TEXT, threshold_value TEXT, notify_emails TEXT[] DEFAULT ARRAY['uptime@OPTiFi.ca'], first_alert_min SMALLINT DEFAULT 5, repeat_interval_min SMALLINT DEFAULT 60, active BOOLEAN DEFAULT true ); -- api_endpoints (Meerby, webhooks) CREATE TABLE api_endpoints ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), account_id UUID NOT NULL REFERENCES accounts(id), name TEXT, base_url TEXT, endpoint_path TEXT, api_key_enc TEXT, trigger_event TEXT, payload_template TEXT, enabled BOOLEAN DEFAULT true, last_triggered TIMESTAMPTZ ); -- join_access_points CREATE TABLE join_access_points ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), account_id UUID NOT NULL REFERENCES accounts(id), mac_address MACADDR UNIQUE, alias TEXT, model TEXT, site TEXT, firmware_version TEXT, ip_address INET, status TEXT, health_pct SMALLINT, last_contact TIMESTAMPTZ ); -- meerby_events (trigger audit) CREATE TABLE meerby_events ( id BIGSERIAL PRIMARY KEY, sensor_id UUID REFERENCES sensors(id), account_id UUID REFERENCES accounts(id), triggered_at TIMESTAMPTZ NOT NULL DEFAULT now(), beacon_id TEXT, store_code TEXT, http_status SMALLINT, response_ms INTEGER, lifecycle_status TEXT );

Connection string (Node.js):
postgresql://optifi_app:<secret>@your-rds-proxy.ca-central-1.rds.amazonaws.com:5432/optifi?sslmode=require

OPTiFi API (Next.js): https://api.optifi.io

Secrets Manager key: optifi/prod/rds-credentials  ·  IAM role: optifi-lambda-rds-role

Feature	Super admin	MSP admin	Retail admin	Insights viewer	Brand viewer
Data insights & zone analytics					◑
Sensor fleet view (own account)
Sensor configuration & range profiles
Firmware OTA push			◑
Network / Join AP management		◑
ESPHome YAML generator
API router configuration			◑
Account management & sensor sharing		◑	◑
Alert & notification settings
User & access management		◑	◑
Campaigns — create & manage				◑	◑
GitHub & firmware repos
Cross-account data (all orgs)

Full access ◑Partial / scoped No access