Building a Reporting and Analytics Layer for SaaS: PostgreSQL Aggregations, Materialized Views, and MENA Reporting Requirements

Most SaaS products do not need a full data warehouse in their first three years. What they need is a reporting layer built directly on PostgreSQL that can answer the operational questions their clients actually ask, without the operational complexity of a separate data warehouse. This is the architecture that works for SaaS businesses serving clients across Lebanon and the MENA region.

Why a separate data warehouse is premature for most MENA SaaS products

Data warehouses like Redshift, BigQuery, and Snowflake solve specific problems: analytical queries across billions of rows, complex cross-table joins with long-running aggregation jobs, and decoupling analytics workloads from OLTP databases. Most SaaS products in Lebanon and the MENA region do not have these problems in their first several years.

The typical MENA SaaS client needs reports that answer operational questions: how many invoices were issued this month, what was the average order value last quarter, which products are selling below expectations. These questions can be answered efficiently with well-indexed PostgreSQL queries and materialized views. Building a data warehouse before you need one adds infrastructure cost, operational complexity, and a data synchronization problem that your team has to maintain indefinitely.

The inflection point where a separate data warehouse becomes worth its cost is when your reporting queries are routinely taking more than 5 seconds, affecting operational database performance, and resisting further optimization through indexes and query rewrites. Most MENA SaaS products reach this point at 50 to 100 million rows in their largest tables.

Materialized views as the core of the reporting layer

PostgreSQL materialized views store the result of a query as a physical table. Unlike regular views, they are not recalculated on every query. The trade-off is that the data is only as fresh as the last time the view was refreshed.

For SaaS reporting, materialized views work best for pre-aggregated metrics that do not need to be real-time. A daily sales summary view, a monthly revenue view by tenant, a weekly active user count view. These views are refreshed on a schedule (nightly for daily summaries, hourly for dashboards that need more freshness) using a background job.

CREATE MATERIALIZED VIEW mv_daily_sales_summary AS
SELECT
    tenant_id,
    date_trunc('day', created_at) AS day,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY tenant_id, date_trunc('day', created_at);

CREATE UNIQUE INDEX ON mv_daily_sales_summary (tenant_id, day);

With a unique index on the materialized view, you can use REFRESH MATERIALIZED VIEW CONCURRENTLY which allows reads to continue against the old version while the refresh runs. This is important for production systems where the dashboard must remain available during a refresh that might take several seconds.

Aggregation tables for high-frequency metrics

For metrics that update frequently (every order, every payment, every login), materialized views refreshed on a schedule may not provide sufficient freshness. Aggregation tables updated incrementally by application code provide sub-minute freshness without the cost of running a full aggregation query.

The pattern: maintain a tenant_daily_stats table that is updated by a background job that processes new events since the last run. The job runs every 5 minutes, processes only events since the last checkpoint, and updates the aggregation table with incremental changes.

CREATE TABLE tenant_daily_stats (
    tenant_id UUID NOT NULL,
    stat_date DATE NOT NULL,
    order_count INT DEFAULT 0,
    total_revenue NUMERIC(15,2) DEFAULT 0,
    active_users INT DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (tenant_id, stat_date)
);

The Go job processes new orders since the last checkpoint using the outbox or a high-watermark cursor on the orders table, and upserts into tenant_daily_stats using ON CONFLICT DO UPDATE.

Report queries that respect tenant isolation

Every reporting query must include the tenant filter. A report that accidentally returns data for all tenants is a data breach. In Go, the repository pattern for reporting queries follows the same tenant-context approach used in operational queries:

func (r *ReportRepository) GetDailySummary(
    ctx context.Context,
    start, end time.Time,
) ([]DailySummary, error) {
    tenant, ok := TenantFromContext(ctx)
    if !ok {
        return nil, errors.New("no tenant context")
    }
    rows, err := r.db.Query(ctx, `
        SELECT day, order_count, total_revenue, avg_order_value
        FROM mv_daily_sales_summary
        WHERE tenant_id = $1
          AND day >= $2
          AND day <= $3
        ORDER BY day
    `, tenant.ID, start, end)
    ...
}

Currency and localization in MENA reporting

MENA SaaS products frequently need to report revenue in multiple currencies. A restaurant chain with locations in Lebanon (LBP/USD), the UAE (AED), and Saudi Arabia (SAR) needs revenue reports that aggregate across currencies and present totals in a reference currency.

Store all monetary values in the database in their original currency with an explicit currency code column. Store a reference exchange rate at the time of the transaction. The reporting query can either aggregate by currency (showing subtotals per currency) or convert to a reference currency using the stored exchange rate.

Never convert currencies at query time using a live exchange rate fetched from an external API. The conversion happens at transaction time and the rate is stored. Reports must be reproducible: running the same report in October and December should produce the same numbers for September transactions, regardless of how exchange rates have moved.

Export formats: what MENA clients actually need

MENA enterprise clients frequently need reports exported in specific formats. Lebanese and Gulf-region accounting departments typically need Excel (XLSX) exports that match the format of their ERP system. Saudi clients with VAT requirements need PDF exports that include the required VAT fields.

In Go, Excel exports are handled with the excelize library. PDF exports use the HTML template plus Gotenberg approach. Both export formats should use the same underlying reporting query. The difference is only in how the result is formatted and delivered.

For large reports (monthly transaction exports with 50,000+ rows), generate the export as a background job, store the result in S3, and send the client a download link via email or webhook. Do not attempt to stream large reports through the HTTP response, as this will time out on mobile connections common in Lebanon and the broader region.

Key lessons from production

Start with PostgreSQL and materialized views. Add a data warehouse when your reporting queries are consistently slow and unoptimizable. Most MENA SaaS products never reach this threshold in their first three years.

Refresh materialized views concurrently. Blocking refreshes lock out dashboard users during the refresh window, which can be several seconds for large datasets.

Store monetary values with their currency code and a snapshot exchange rate at transaction time. Reports must be reproducible regardless of future currency movements.

Always include the tenant filter in every reporting query. A missing tenant filter is a data breach. Test cross-tenant isolation in reporting queries the same way you test it in operational queries.

Generate large exports as background jobs. Streaming 50,000 rows through an HTTP response is unreliable on the variable network conditions common in Lebanon and the Gulf.