Reporting Service

┌──────────────────────────────────────┐
│       Database Events                │
│    (create/update/delete)            │
└──────────────────┬───────────────────┘
                   │
                   ▼
┌──────────────────────────────────────┐
│      Reporting Functions             │
│    (capture all collections)         │
└──────────────────┬───────────────────┘
                   │
              ┌────┴────┐
              ▼         ▼
        ┌─────────┐  ┌────────┐
        │  Topic  │  │ Tasks  │
        └────┬────┘  └───┬────┘
             │           │
             └─────┬─────┘
                   ▼
        ┌──────────────────────┐
        │   Data Warehouse     │
        │  (tables + views)    │
        └──────────────────────┘
                   │
                   ▼
        ┌──────────────────────┐
        │   Query API          │
        │ (secure execution)   │
        └──────────────────────┘

Cloud Functions monitor:
  - Any collection in the database (wildcard matcher)
  - All CRUD operations (create, update, delete)
  - Auth context for sender tracking
  - Event IDs for deduplication
 
Captured metadata:
  - collectionId: Source collection name
  - documentId: Full document path (supports subcollections)
  - item/before/after: Document data snapshots
  - timestamp: Event time for temporal ordering
  - senderId: User who triggered the change
  - eventId: Unique event identifier
 
Event routing:
  - onCreate → FIRESTORE_DOCUMENT_CREATED topic
  - onUpdate → FIRESTORE_DOCUMENT_UPDATED topic
  - onDelete → FIRESTORE_DOCUMENT_DELETED topic
  - Topics → Cloud Tasks queues for reliable delivery

BigQueryTaskService.ensureTableExists():
  - Creates dataset if missing (configurable region)
  - Creates table with standardized schema
  - Enables time partitioning by timestamp (daily partitions)
  - Handles race conditions when multiple tasks create simultaneously
 
Base table schema:
  - id: Document ID (supports nested paths)
  - data: JSON column for full document
  - timestamp: Event timestamp
  - action: CREATE/UPDATE/DELETE
  - event_id: Deduplication key
 
ensureLatestViewsExists():
  - Creates _latest view automatically
  - Uses ROW_NUMBER window function for deduplication
  - Returns most recent version of each document
  - Updates in real-time as data changes

Expanded view system:
  - Extracts all keys from JSON documents
  - Creates typed views from JSON columns
  - Two variants per subcollection:
    - _expanded_raw: All historical records
    - _expanded_latest: Most recent per document
 
Dynamic SQL generation:
  - Discovers keys with JSON_KEYS function
  - Filters keys to valid SQL identifiers
  - Special handling for customerKey (forced string type)
  - Regex-based subcollection filtering
  - Generates column list from discovered schema
 
View update workflow:
  1. Check if view exists for document path
  2. Compare existing view schema to new keys
  3. If new keys found, schedule view update
  4. Merge schemas without data loss
  5. Execute EXECUTE IMMEDIATE to recreate view
  6. Maintain backward compatibility
 
Subcollection support:
  - Parses nested paths (users/123/posts/456)
  - Creates views per subcollection level
  - Regex matching for path filtering
  - Table naming: users__posts_expanded_raw

Security layers:
  1. Operation whitelist enforcement
  2. Table name validation
  3. Column access control
  4. SQL parsing and AST validation
  5. Parameterized query generation
 
operationWhiteListCheck():
  - Parses SQL AST to extract operations
  - Ensures all operations start with "select::"
  - Rejects INSERT, UPDATE, DELETE, DROP, etc.
  - Throws reporting/invalid-operation on violation
 
ensureOnlyAllowedTables():
  - Extracts table names from query AST
  - Validates against schema allowlist
  - Throws reporting/invalid-table-name on violation
  - Prevents access to unauthorized data
 
Query sanitization:
  - Accepts QueryNode AST instead of raw SQL
  - Generates parameterized queries
  - Removes wrapping quotes from parameters
  - Parses back to pure SQL without injection vectors

Permission schema config:
  - Per-table column permissions
  - Column whitelists (no permissions required)
  - Permission-gated columns (require specific permissions)
  - Table visibility based on accessible columns
 
generateSchemaFromPermissions():
  - Takes user permissions array as input
  - Generates runtime schema for query validation
  - Includes whitelist columns automatically
  - Adds permission-gated columns if user has permission
  - Excludes tables with zero accessible columns
  - Supports OR logic (any listed permission grants access)
 
Schema application:
  - Schema passed to query builder
  - Enforced during AST parsing
  - Validates column access at build time
  - Prevents unauthorized data exposure
  - Works with complex joins and subqueries

ReportingGlobalConfig.apply():
  - Intercepts all queries before execution
  - Extracts customerKey from JWT claims
  - Injects WHERE clause automatically
  - Handles global filters across all queries
 
Automatic injection:
  - Prepends customerKey filter to conditions
  - Uses table name from query for column reference
  - Applied to primary table in FROM clause
  - Prevents cross-tenant data leakage
  - Transparent to query authors
  - Cannot be bypassed by user input
 
Global filter support:
  - Define filters applied to all queries
  - Combine with tenant filters
  - Support complex filter expressions
  - Maintain filter precedence

Caching strategy:
  - Hash query AST for cache key generation
  - Order-agnostic hashing (unorderedArrays: true)
  - 2-hour TTL for cached results
  - Cache lookup before query execution
  - Automatic cache population on miss
 
Cache key generation:
  - Uses object-hash library
  - Considers full query structure
  - Includes filters, joins, ordering
  - Ignores array and object order
  - Produces stable cache keys
 
Cache flow:
  1. Format query with global filters
  2. Generate hash of formatted query
  3. Check cache for existing result
  4. Return cached data if found
  5. Execute query on cache miss
  6. Store result with TTL

Event sourcing approach:
  - All changes recorded as immutable events
  - Timestamp-based ordering
  - Action tracking (CREATE/UPDATE/DELETE)
  - Never deletes historical data
  - Soft deletes preserve audit trail
 
Data structure:
  - Base table: All events chronologically
  - _latest views: Current state reconstruction
  - Time partitioning for query performance
  - Event IDs for deduplication
 
Historical queries:
  - Point-in-time queries via timestamp filtering
  - Change tracking between time ranges
  - Audit trail for compliance
  - Rollback capability from history

BigQuerySagas coordinate:
  - FirestoreDocumentCreatedEvent → BigQuerySyncCommand
  - FirestoreDocumentUpdatedEvent → BigQuerySyncCommand
  - FirestoreDocumentDeletedEvent → BigQuerySyncCommand
 
Saga pattern benefits:
  - RxJS observable-based event streaming
  - Automatic command generation from events
  - Failure isolation per sync operation
  - Retry logic via RetryCommandHandler
  - Idempotent operation handling
 
BigQuerySyncHandler workflow:
  1. Ensure target table exists
  2. Ensure _latest view exists
  3. Prepare row data with metadata
  4. Insert via streaming API
  5. Schedule expanded view updates if needed
  6. Retry on transient failures

BigQuery Storage Write API:
  - Uses managed writer for efficient streaming
  - JSON writer for schemaless data
  - Proto descriptor conversion for type safety
  - Default stream for automatic committing
  - Connection management and cleanup
 
Insert optimization:
  - Bypasses traditional insert API
  - Lower latency for real-time sync
  - Higher throughput for batch operations
  - Automatic schema evolution support
  - Built-in retry and error handling
 
Data transformation:
  - Stringifies JSON data for storage
  - Converts timestamp formats
  - Handles nullable event IDs
  - Validates against table schema

QueryNode structure supports:
  - SELECT with column lists or expressions
  - FROM tables or subqueries
  - JOIN operations (INNER, LEFT, RIGHT, FULL, CROSS)
  - WHERE filters with complex conditions
  - GROUP BY with aggregations
  - HAVING for post-aggregation filtering
  - ORDER BY for result sorting
  - LIMIT and OFFSET for pagination
  - WITH clauses (CTEs)
  - UNION operations
  - QUALIFY for window function filtering
 
QueryBuilder features:
  - Converts AST to SQL
  - Generates parameterized queries
  - Validates syntax during build
  - Supports nested subqueries
  - Handles complex expressions
  - Type-safe parameter binding
 
parseFromSQL():
  - Reverse operation: SQL → AST
  - Enables query manipulation
  - Allows query optimization
  - Supports query analysis

BigQueryAdminService provides:
  - Upsert views (create or update)
  - Table metadata management
  - View query configuration
  - Existence checking
  - Atomic view updates
 
upsertView():
  - Checks if view exists
  - Creates new view if missing
  - Updates metadata if exists
  - Returns consistent API response
  - Handles concurrent updates
 
Use cases:
  - Creating aggregation views
  - Building materialized views
  - Setting up custom reporting views
  - Managing view permissions
  - Updating view definitions