Why We Replaced Security-Barrier Views with analytics.* + SECURITY DEFINER RPCs

Forecast anxiety is rarely a sales problem. It is almost always a data pipeline problem. We learned that the hard way while scaling InvoicifyAI's analytics stack.

For years we leaned on a three-tier security-barrier view (SBV) pattern. It kept data safe, but the complexity taxed every engineer touching the pipeline. Refreshes were inconsistent, test fixtures were awkward, and the performance story was muddy.

This year we rebuilt analytics around a cleaner architecture: materialized views inside an analytics.* schema, surfaced through a thin layer of SECURITY DEFINER RPCs that validate company context. The result: faster refreshes, simpler reasoning, and fewer “this view is special” surprises.

Architecture at a glance

analytics.* schema
├── dashboard_metrics_mv
├── pipeline_health_mv
└── revenue_trends_mv

public schema
├── get_dashboard_metrics()
├── get_pipeline_health()
└── get_revenue_trends()

Each RPC has a single job: look up the caller's company ID, assert membership, and return only the rows that match.

CREATE FUNCTION public.get_dashboard_metrics()
RETURNS TABLE (...)
SECURITY DEFINER
SET search_path = public
LANGUAGE plpgsql
AS $
DECLARE
  v_company_id uuid;
BEGIN
  v_company_id := get_user_company_id_secure(auth.uid());

  RETURN QUERY
  SELECT *
  FROM analytics.dashboard_metrics_mv
  WHERE company_id = v_company_id;
END;
$;

The SECURITY DEFINER flag lets the function access the analytics schema, while the explicit search_path prevents privilege escalation.

Materialized views that behave

Putting views inside analytics.* keeps them out of the public schema clutter and makes ownership explicit. We added supporting indexes so we could refresh them concurrently:

CREATE UNIQUE INDEX dashboard_metrics_mv_company_date
  ON analytics.dashboard_metrics_mv (company_id, metric_date);

With UNIQUE indexes in place, REFRESH MATERIALIZED VIEW CONCURRENTLY finishes without locking readers.

A refresh registry that earns trust

We store refresh order in a simple registry table:

CREATE TABLE public.dashboard_materialized_views (
  view_name text primary key,
  description text,
  refresh_function text,
  priority integer
);

Views with refresh_function IS NULL refresh directly.
Others call custom logic if they have dependencies.
Lower priority runs first.

Our dashboard “Refresh now” button triggers a stored procedure that walks this registry so we always know what runs and in which order.

Watch-outs we hit

`search_path` drift — SECURITY DEFINER functions inherit the creator's search path. Always set it explicitly.
Grants — revoke everything from public, then grant execute back to the minimal roles (authenticated for client access, service_role where necessary).
Testing — we record fixtures via the RPC layer, not by hitting the materialized views directly. It keeps tests aligned with production access.

Wins after the rebuild

Faster refresh — prioritised registry plus concurrent refresh trimmed runtime without raising lock contention.
Cleaner ergonomics — new engineers see the same pattern everywhere instead of bespoke view stacks.
Safer access control — every path to analytics data passes through a company validation guard rail.

Want the 1-pager?

I keep a one-page primer with the exact registry schema, example RPCs, and refresh scripts. Reply “analytics” on our LinkedIn post or email us.

Questions about migrating your own analytics stack? Reach out at hello@invoicifyai.com.