Skip to main content

Framework M: BI Integration Guide

Framework M is designed for operational workflows and lightweight analytics out of the box. For true Analytics and High-Fidelity Business Intelligence (BI), we embrace an "Exit Hatch" philosophy: rather than building a heavy BI engine inside the application, we expose operational data to purpose-built external tools (Metabase, Superset, Tableau, PowerBI, etc.).

This guide outlines the standard integration patterns for connecting Framework M to external BI platforms.

The most robust and performant pattern for BI integration is using database-level replication to create a dedicated Read Replica (or data warehouse export) for your BI tools.

Why this pattern?​

  • Performance: Heavy analytical queries (aggregations, large table scans) will not impact the performance of your operational application.
  • Direct SQL Access: BI tools can leverage raw SQL against the underlying PostgreSQL database, utilizing standard schemas without API rate limits.
  • Freshness: Streaming replication (e.g., PostgreSQL logical replication) provides near real-time data to the BI layer.

Implementation Steps​

  1. Provision a Read Replica: Configure PostgreSQL to maintain a hot standby replica.
  2. Setup a BI User: Create a dedicated bi_read_only user on the replica with strictly SELECT permissions on your application tables and _history shadow tables.
  3. Connect your BI Tool: Connect Metabase, Superset, or Tableau directly to the replica using standard PostgreSQL connectors.

2. Airbyte / Fivetran ELT Pipelines​

For Enterprise setups where data needs to be aggregated with other sources (CRM, Marketing, etc.) into a centralized Data Warehouse (Snowflake, BigQuery, Redshift), use ELT tools like Airbyte or Fivetran.

Why this pattern?​

  • Data Consolidation: Combine Framework M data with external systems.
  • Transformation: Use dbt (Data Build Tool) downstream to transform raw Framework M operational schemas into optimized star schemas (Fact & Dimension tables).
  • Historical Snapshots: Build complex slowly changing dimensions (SCDs) that go beyond standard _history tables.

Implementation Steps​

  1. Connect ELT Tool to Database: Use PostgreSQL CDC (Change Data Capture) via logical replication slots to stream changes.
  2. Target Data Warehouse: Sync raw data directly into a staging schema in your warehouse.
  3. Transform: Use dbt models to flatten JSONB fields, resolve document references, and clean data for BI consumption.

3. Data Lake / S3 Export via Scheduled Jobs​

If you prefer file-based ingestion into a Data Lake (e.g., AWS S3, Azure Data Lake) or a lakehouse architecture, you can leverage Framework M's built-in AutoReport feature to periodically push datasets.

Why this pattern?​

  • Decoupled Architecture: No direct database access required from the data warehouse.
  • Serverless Analytics: Enables querying via Athena, DuckDB, or Presto directly on CSV/Parquet files.

Implementation Steps​

  1. Create SavedViews: Define SavedView DocTypes in Framework M targeting specific aggregates or filtered lists.
  2. Configure AutoReports: Create AutoReport schedules to periodically generate CSV exports of these views.
  3. Automate Delivery: In future versions, or via a custom worker plugin, upload the generated CSV artifacts to an S3 bucket instead of (or in addition to) emailing them.

4. API-Driven Data Extraction​

For lightweight scenarios or when integrating with BI tools that primarily consume REST APIs, you can use Framework M's standard APIs.

Why this pattern?​

  • No Infrastructure Changes: Can be done purely over HTTPS.
  • Security: Respects Framework M's application-level security, tenant binds, and Role-Level Security (RLS) policies automatically.

Implementation Steps​

  1. Use SavedView API: Call GET /api/v1/SavedView/{name}/execute to retrieve pre-configured datasets.
  2. Use Meta/CRUD APIs: Call GET /api/meta/{doctype} with query parameters for ad-hoc fetching.
  3. Pagination Handling: Implement cursor or offset pagination in your extraction scripts to handle large datasets effectively.

[!WARNING] API extraction is not recommended for massive data dumps (e.g., millions of rows) due to HTTP overhead and potential application load. For large datasets, use Database Replication or ELT pipelines.

Working with Framework M Specifics​

Querying JSONB Fields​

Many dynamic fields or generic metadata in Framework M are stored as JSONB. Most modern BI tools support extracting values from JSONB in PostgreSQL using the ->> operator.

Example in Metabase:

SELECT id, name, metadata->>'industry' as industry 
FROM company

Shadow Tables for Auditing​

Framework M automatically maintains {table}_history shadow tables for DocTypes with auditing enabled. BI tools can query these tables directly to build timelines, calculate state duration, or perform forensic analysis.