Skip to main content

Analytics & Audit - Domain Specification

First Introduced: MVP.0 Status: Specification Complete Last Updated: 2025-10-25

Overview​

Analytics & Audit captures what happened, when, and by whom across the entire platform — operational, behavioral, and business layers alike. It provides an event-driven backbone for telemetry, reporting, and compliance, ensuring that every change (data, booking, pricing, payment) can be reconstructed and analyzed historically.

This layer is both diagnostic (for debugging and metrics) and forensic (for compliance and trust). It enables the platform to meet SOC2, GDPR, and PCI-DSS requirements while providing real-time operational insights and business intelligence.

Responsibilities​

This domain IS responsible for:

  • Recording all system and user-generated events (atomic activity log)
  • Maintaining tamper-evident audit trails for sensitive operations
  • Aggregating time-series metrics for dashboards and reporting
  • Providing observability for debugging and performance monitoring
  • Enabling compliance-grade audit evidence (SOC2, GDPR, PCI-DSS)
  • Supporting business intelligence and analytics use cases
  • Time-based partitioning and data retention management

This domain is NOT responsible for:

  • Real-time alerting systems (→ System Architecture / Monitoring)
  • Application performance monitoring (→ Infrastructure layer)
  • Business logic execution (events are observational, not operational)
  • Data transformation pipelines (→ Data Warehouse layer)
  • User-facing analytics UI (→ Application layer)

Relationships​

Depends On:

  • Identity & Tenancy - User, Org, Account for actor tracking
  • ALL other domains - Events sourced from every domain action

Depended On By:

Related Domains:

Core Concepts​

Entity: Event (Generic Activity Log)​

Purpose: Atomic record of a system or user action across all domains. Provides unified telemetry for debugging, monitoring, and analytics.

Key Attributes:

  • id (UUID, primary key) - Unique event identifier
  • org_id (UUID, foreign key → organizations.id) - Tenant boundary
  • account_id (UUID, nullable, foreign key → accounts.id) - Sub-tenant scope
  • user_id (UUID, nullable, foreign key → users.id) - Actor (null for system events)
  • event_type (VARCHAR, required) - Namespaced action (e.g., booking.confirmed, space.updated)
  • resource_type (VARCHAR, required) - Entity class (e.g., booking, space, payment)
  • resource_id (UUID, required) - Specific entity affected
  • payload (JSONB, nullable) - Event-specific data (flexible schema)
  • source (VARCHAR, required) - Origin: api, ui, system, integration, worker
  • trace_id (UUID, nullable) - Distributed tracing correlation ID
  • occurred_at (TIMESTAMPTZ, required) - When the event happened (indexed for time-series queries)
  • created_at (TIMESTAMPTZ, default now()) - When recorded in DB

Relationships:

  • Event → Org (*, many-to-one)
  • Event → Account (*, many-to-one, optional)
  • Event → User (*, many-to-one, optional for system events)
  • Event → [Any Domain Entity] (*, many-to-one via resource_type + resource_id)

Lifecycle:

  • Created: Append-only on any significant system action
  • Never Updated: Immutable once written
  • Retention: Configurable by org_tier (12-24 months default)
  • Archived: Moved to cold storage after retention period

Business Rules:

  • Events are append-only (no updates or deletes)
  • event_type follows namespace convention: domain.action (e.g., booking.created, payment.succeeded)
  • trace_id enables correlation across distributed operations
  • Payload schema varies by event_type (validated at application layer)
  • Time-based partitioning by occurred_at for performance
  • Failed event writes captured in dead-letter queue for retry

Entity: AuditEvent (Compliance-Grade Audit Trail)​

Purpose: Cryptographically verifiable record of sensitive state changes for compliance, security, and forensic investigation. Subset of events with enhanced metadata and tamper-evidence.

Key Attributes:

  • id (UUID, primary key)
  • org_id (UUID, foreign key → organizations.id)
  • account_id (UUID, nullable, foreign key → accounts.id)
  • user_id (UUID, nullable, foreign key → users.id) - Actor (required for user actions)
  • action (VARCHAR, required) - High-level operation: create, update, delete, access, export
  • resource_type (VARCHAR, required) - Entity class
  • resource_id (UUID, required) - Specific entity
  • before (JSONB, nullable) - State snapshot before change (for updates/deletes)
  • after (JSONB, nullable) - State snapshot after change (for creates/updates)
  • outcome (VARCHAR, required) - success, failure, denied
  • ip_address (INET, nullable) - Source IP address
  • user_agent (TEXT, nullable) - Client identifier
  • session_id (UUID, nullable) - Session context
  • checksum (VARCHAR, required) - HMAC signature for tamper detection
  • signed_at (TIMESTAMPTZ, required) - Timestamp included in signature
  • created_at (TIMESTAMPTZ, default now())

Relationships:

  • AuditEvent → Org (*, many-to-one)
  • AuditEvent → Account (*, many-to-one, optional)
  • AuditEvent → User (*, many-to-one, nullable)
  • AuditEvent → [Any Domain Entity] (*, many-to-one)

Lifecycle:

  • Created: On sensitive operations (auth changes, financial transactions, delegation, data exports)
  • Never Updated or Deleted: Permanent immutable record
  • Retention: Minimum 7 years (compliance requirement)
  • Storage: WORM (Write-Once-Read-Many) compliant storage

Business Rules:

  • HMAC signature computed over: org_id|user_id|action|resource_type|resource_id|signed_at|checksum_payload
  • Signature verified on read for tamper detection
  • Deletions logged as new AuditEvent (never removed from database)
  • before and after fields mandatory for update and delete actions
  • Time-based partitioning by month for compliance audits
  • Redactions (GDPR right to erasure) logged with redacted fields marked in payload
  • All permission changes, role assignments, and delegation grants must create AuditEvent

Partitioning Strategy:

  • Monthly partitions: audit_events_YYYY_MM (e.g., audit_events_2025_10)
  • Automatic partition creation via maintenance job
  • Partition retention: 7 years minimum (compliance)
  • Archive to cold storage after 2 years (hot access period)

Entity: AnalyticsSnapshot (Pre-computed Metrics)​

Purpose: Pre-aggregated metrics for fast dashboard rendering and trend analysis. Denormalized time-series data avoiding expensive real-time aggregations.

Key Attributes:

  • id (UUID, primary key)
  • org_id (UUID, foreign key → organizations.id)
  • account_id (UUID, nullable, foreign key → accounts.id)
  • metric_key (VARCHAR, required) - Metric identifier (e.g., bookings_per_day, revenue_gross, occupancy_rate)
  • metric_value (NUMERIC, required) - Aggregated value
  • dimensions (JSONB, nullable) - Breakdown dimensions (e.g., {"region": "US", "channel": "direct"})
  • time_bucket (TIMESTAMPTZ, required) - Aggregation period start (hour/day/week/month)
  • time_granularity (VARCHAR, required) - hour, day, week, month
  • computed_at (TIMESTAMPTZ, default now()) - When aggregation ran
  • created_at (TIMESTAMPTZ, default now())

Relationships:

  • AnalyticsSnapshot → Org (*, many-to-one)
  • AnalyticsSnapshot → Account (*, many-to-one, optional)
  • Derived from Event stream (aggregation job)

Lifecycle:

  • Created: By scheduled aggregation jobs (hourly for day metrics, nightly for week/month)
  • Updated: Never (new snapshots created for corrections)
  • Retention: 24 months online, then archived

Business Rules:

  • Snapshots are append-only (recalculations create new records)
  • metric_key follows naming convention: domain_metric_unit (e.g., booking_count_total, revenue_gross_usd)
  • Dimensions stored as JSONB for flexible drill-down queries
  • Composite unique constraint on (org_id, metric_key, time_bucket, time_granularity, dimensions_hash)
  • Time-series index on (metric_key, org_id, time_bucket) for fast range queries
  • MVP.1+ feature (not included in MVP.0)

Entity: Report (Saved Report Configurations)​

Purpose: User-defined report templates for recurring analytics queries. Enables saved filters, custom dashboards, and scheduled report generation.

Key Attributes:

  • id (UUID, primary key)
  • org_id (UUID, foreign key → organizations.id)
  • account_id (UUID, nullable, foreign key → accounts.id)
  • created_by (UUID, foreign key → users.id)
  • name (VARCHAR, required) - Display name
  • description (TEXT, nullable)
  • report_type (VARCHAR, required) - dashboard, export, scheduled
  • metric_keys (TEXT[], required) - Array of metrics to include
  • filters (JSONB, nullable) - Date ranges, dimensions, thresholds
  • schedule (JSONB, nullable) - Cron expression and delivery config (for scheduled reports)
  • visibility (VARCHAR, required) - private, org, account
  • status (VARCHAR, default 'active') - active, paused, archived
  • last_run_at (TIMESTAMPTZ, nullable)
  • created_at, updated_at (TIMESTAMPTZ)

Relationships:

  • Report → Org (*, many-to-one)
  • Report → Account (*, many-to-one, optional)
  • Report → User (*, many-to-one via created_by)

Lifecycle:

  • Created: When user saves report configuration
  • Updated: Filter/schedule modifications
  • Archived: Soft delete via status='archived'

Business Rules:

  • Report names must be unique within org_id scope
  • Scheduled reports generate export files (CSV/PDF) delivered via email or S3
  • Private reports visible only to creator; org/account reports shared within scope
  • V1+ feature (not included in MVP.0)

Workflows​

Workflow: Record System Event​

Trigger: Any significant domain action (booking created, payment processed, space updated)

Steps:

  1. Domain service performs operation (e.g., create booking)
  2. Generate event record with:
    • event_type = booking.created
    • resource_type = booking
    • resource_id = new booking UUID
    • user_id = acting user (or null for system)
    • payload = relevant booking data snapshot
    • trace_id = request correlation ID
    • occurred_at = operation timestamp
  3. Async publish to event stream (non-blocking)
  4. Event processor writes to events table
  5. Fan-out to subscribers (search reindex, analytics aggregation, external webhooks)

Postconditions:

  • Event persisted in database
  • Available for audit queries and analytics
  • Downstream consumers notified

Workflow: Create Audit Trail Entry​

Trigger: Sensitive operation requiring compliance audit (permission change, financial transaction, data export)

Steps:

  1. Before operation: Capture before state snapshot
  2. Perform operation (e.g., update role assignment)
  3. After operation: Capture after state snapshot
  4. Collect context:
    • user_id, ip_address, user_agent, session_id
    • action = update, resource_type = membership, resource_id = membership UUID
  5. Compute HMAC signature:
    • Input: org_id|user_id|action|resource_type|resource_id|timestamp|payload_hash
    • Secret: Organization-specific signing key (rotated quarterly)
  6. Write to audit_events table with signature
  7. Log outcome (success/failure/denied)

Postconditions:

  • Tamper-evident audit record created
  • Signature verifiable for compliance audits
  • Permanent retention (7+ years)

Workflow: Generate Analytics Snapshot (V1+)​

Trigger: Scheduled aggregation job (hourly/nightly)

Steps:

  1. Aggregation worker reads events from last checkpoint
  2. Group by metric definitions:
    • bookings_per_day: COUNT(events WHERE event_type='booking.confirmed' GROUP BY date)
    • revenue_gross: SUM(payload->>'amount' WHERE event_type='payment.succeeded')
    • occupancy_rate: Booking nights / Available nights
  3. For each metric + dimension combination:
    • Compute aggregated value
    • Extract dimensions from event payload (channel, region, space_type)
    • Generate dimensions hash for uniqueness
  4. Write to analytics_snapshots table
  5. Update checkpoint (last processed event timestamp)

Postconditions:

  • Fast dashboard queries against pre-computed snapshots
  • No expensive real-time aggregations needed
  • Historical trends available for charting

Business Rules​

  1. Append-Only Immutability: Events and AuditEvents never updated or deleted (compensating events for corrections)
  2. Time-Based Partitioning: audit_events partitioned by month for compliance query performance
  3. Signature Verification: All AuditEvent signatures verified on read; tamper detection alerts raised
  4. Retention Policies:
    • Events: 12-24 months (configurable by org tier)
    • AuditEvents: 7+ years minimum (compliance requirement)
    • AnalyticsSnapshots: 24 months online, then archived
  5. Event Namespace Convention: domain.action format (e.g., space.updated, booking.canceled)
  6. Trace ID Propagation: All related events share same trace_id for distributed operation correlation
  7. Dead-Letter Queue: Failed event writes retried from DLQ (at-least-once delivery guarantee)
  8. GDPR Right to Erasure: User data redaction logged in AuditEvent; PII masked in payload with [REDACTED] marker
  9. Org Isolation: All queries filtered by org_id (enforced via RLS policies in V1+)

Implementation Notes​

MVP Scope (MVP.0)​

Included:

  • audit_events table with time-based partitioning (monthly)
  • Basic event recording for sync operations (channel syncs, iCal imports)
  • HMAC signature generation for audit trail integrity
  • Append-only event log for bookings, payments, spaces
  • Manual SQL queries for basic audit reporting
  • Retention: 12 months for events, 7 years for audit_events

Deferred to V1:

  • events table (generic event stream) - MVP.0 focuses on audit compliance first
  • analytics_snapshots table and aggregation jobs
  • reports table and scheduled report generation
  • Event stream pub/sub architecture (Kafka/PubSub)
  • Real-time dashboard queries
  • Automated anomaly detection

Deferred to V2:

  • Machine learning on event data (predictive analytics)
  • External BI tool integrations (Looker, Tableau)
  • Cross-org federated analytics (white-label marketplace analytics)
  • Advanced data retention policies with automated archival

Database Indexes​

Critical for performance:

-- Audit Events (MVP)
CREATE INDEX idx_audit_events_org_occurred ON audit_events(org_id, occurred_at DESC);
CREATE INDEX idx_audit_events_resource ON audit_events(resource_type, resource_id);
CREATE INDEX idx_audit_events_user ON audit_events(user_id, occurred_at DESC);
CREATE INDEX idx_audit_events_action ON audit_events(org_id, action, occurred_at DESC);

-- Events (V1+)
CREATE INDEX idx_events_org_occurred ON events(org_id, occurred_at DESC);
CREATE INDEX idx_events_type ON events(event_type, occurred_at DESC);
CREATE INDEX idx_events_resource ON events(resource_type, resource_id);
CREATE INDEX idx_events_trace ON events(trace_id);

-- Analytics Snapshots (V1+)
CREATE INDEX idx_snapshots_metric_time ON analytics_snapshots(metric_key, org_id, time_bucket DESC);
CREATE INDEX idx_snapshots_org_time ON analytics_snapshots(org_id, time_bucket DESC);
CREATE INDEX idx_snapshots_computed ON analytics_snapshots(computed_at DESC);

-- Reports (V1+)
CREATE INDEX idx_reports_org ON reports(org_id, status);
CREATE INDEX idx_reports_creator ON reports(created_by, status);

Partitioning Strategy (Audit Events)​

Time-based monthly partitions for compliance and performance:

-- Parent table (partitioned)
CREATE TABLE audit_events (
  id UUID NOT NULL,
  org_id UUID NOT NULL,
  -- ... other columns ...
  signed_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (id, signed_at)
) PARTITION BY RANGE (signed_at);

-- Automatic partition creation
CREATE TABLE audit_events_2025_10 PARTITION OF audit_events
  FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');

CREATE TABLE audit_events_2025_11 PARTITION OF audit_events
  FOR VALUES FROM ('2025-11-01') TO ('2025-12-01');

-- Maintenance job creates partitions 3 months in advance
-- Retention policy archives partitions older than 2 years to cold storage
-- Compliance requirement: 7-year minimum retention

Benefits:

  • Fast queries scoped to recent months
  • Efficient compliance audits (query specific time ranges)
  • Easy archival (detach old partitions, move to S3 Glacier)
  • Parallel query execution across partitions

Constraints​

Enforce data integrity:

-- Audit Events
ALTER TABLE audit_events ADD CONSTRAINT audit_events_action_check
  CHECK (action IN ('create', 'update', 'delete', 'access', 'export'));
ALTER TABLE audit_events ADD CONSTRAINT audit_events_outcome_check
  CHECK (outcome IN ('success', 'failure', 'denied'));
ALTER TABLE audit_events ADD CONSTRAINT audit_events_checksum_length
  CHECK (length(checksum) = 64); -- HMAC-SHA256

-- Events (V1+)
ALTER TABLE events ADD CONSTRAINT events_source_check
  CHECK (source IN ('api', 'ui', 'system', 'integration', 'worker'));

-- Analytics Snapshots (V1+)
ALTER TABLE analytics_snapshots ADD CONSTRAINT snapshots_granularity_check
  CHECK (time_granularity IN ('hour', 'day', 'week', 'month'));
ALTER TABLE analytics_snapshots ADD CONSTRAINT snapshots_unique_metric
  UNIQUE (org_id, metric_key, time_bucket, time_granularity, md5(dimensions::text));

-- Reports (V1+)
ALTER TABLE reports ADD CONSTRAINT reports_type_check
  CHECK (report_type IN ('dashboard', 'export', 'scheduled'));
ALTER TABLE reports ADD CONSTRAINT reports_visibility_check
  CHECK (visibility IN ('private', 'org', 'account'));

Future Enhancements​

V1.0: Full Event Stream & Analytics​

Scope:

  • Generic events table for all domain actions (not just audits)
  • analytics_snapshots with automated aggregation jobs
  • reports table with saved configurations
  • Real-time dashboard queries against pre-computed snapshots
  • Event stream pub/sub architecture (Redis Streams or Kafka)
  • Cross-domain event correlation via trace_id

Use Cases:

  • Real-time operations dashboards
  • Booking funnel analysis (quote → hold → booking conversion)
  • Revenue trend reporting by channel/region
  • Performance monitoring (API latency, job duration)

V1.1: Advanced Analytics & BI Integration​

Scope:

  • Scheduled report generation (CSV/PDF exports)
  • Email delivery for recurring reports
  • External BI tool connectors (Looker, Tableau, Metabase)
  • Custom metric definitions (user-defined aggregations)
  • Anomaly detection alerts (booking drops, payment failures)

Use Cases:

  • Weekly revenue reports emailed to stakeholders
  • Occupancy forecasting for pricing optimization
  • Channel performance comparison dashboards
  • Fraud detection (unusual payment patterns)

V2.0: Machine Learning & Predictive Analytics​

Scope:

  • ML models trained on event data
  • Predictive occupancy and revenue forecasting
  • Cancellation risk scoring
  • Dynamic pricing recommendations
  • Anomaly detection automation
  • A/B test result tracking

Use Cases:

  • "Users who viewed this space also booked..." recommendations
  • "Booking likely to cancel" early warning system
  • "Optimal price point" suggestions based on historical trends
  • Seasonal demand predictions for inventory planning

V2.1: Data Warehouse & Lake Integration​

Scope:

  • Streaming ETL to BigQuery/Snowflake/Redshift
  • Event replay for data lake backfill
  • Historical analytics on archived data
  • Cross-org federated analytics (white-label marketplace)
  • Long-term data retention with tiered storage (hot/warm/cold)

Use Cases:

  • Multi-year trend analysis
  • Market research across entire platform
  • Compliance audits with fast retrieval from archives
  • Partner analytics dashboards (aggregated across white-label sites)

Physical Schema​

See 001_initial_schema.sql for complete CREATE TABLE statements.

Summary:

  • MVP.0: 1 table (audit_events with monthly partitioning)
  • V1: 3 tables (events, analytics_snapshots, reports)
  • V2: Additional warehouse sync tables
  • 12+ indexes for query performance
  • 8+ constraints for data integrity
  • HMAC signature validation for audit trail integrity
  • Row-Level Security policies defined (enforced in V1+)