Designing a Metric Schema in Postgres That an Agent Can Query Without a Reporting Layer
Store governed metrics in Postgres so agents read one number, not guess.

For API-first and agent-first teams, a headless analytics backend can use Postgres to define metrics and an MCP server to expose them without an opinionated frontend. The essential design is a metric schema that encodes business logic, access boundaries, and pre-calculated results. An agent then retrieves a governed number with a single, deterministic query, using the schema’s calculation from raw tables.
Why agents querying raw tables produce untrustworthy numbers
Querying a database and returning a number worth trusting are distinct capabilities. One agent can write SUM(revenue) while another writes SUM(amount_captured), and both queries can execute successfully against the same schema. Their disagreement arises because the raw tables leave the calculation that gives the business metric undefined.
Without that definition, each query becomes an independent, undocumented interpretation. A human analyst can ask a colleague what “CAC” includes, while an agent must resolve the ambiguity from whatever context it has. Even with valid syntax, a query can use the wrong denominator or the wrong reporting period. At machine speed, those interpretations can multiply faster than human review can catch them. The missing component is therefore not better query generation, but an authoritative definition that callers cannot reconstruct differently.
What a metric schema must encode before an agent ever runs a query
A metric schema must encode three things directly in Postgres, namely business logic, access boundaries, and pre-calculated results. Definitions belong in executable database objects: a view, function, or materialized result must establish what “MRR”, “gross retention”, or “CAC payback” means before an agent requests it. Row- and column-level permissions must also be in place before the connection, while stored results let the caller use a single deterministic SELECT without building a chain of joins.
The canonical metric table structure: columns that carry meaning, not just data
A metric row needs to explain its value without a metadata join. Its columns establish meaning, time, and provenance. A minimal table declaration is CREATE TABLE metric_results (metric_name text NOT NULL, metric_value numeric NOT NULL, period_start timestamptz NOT NULL, period_end timestamptz NOT NULL, calculated_at timestamptz NOT NULL, grain text NOT NULL, dimension_key text, dimension_value text, definition_version text NOT NULL);. This deliberately simple structure stores a result together with the context needed to interpret it. It leaves the agent responsible only for selecting a row.
The stable lookup key is metric_name, a snake_case identifier independent of changeable display labels. metric_value holds the pre-calculated result, with NUMERIC appropriate for currency and INTEGER for count-specific tables. period_start and period_end make the measurement window clear, while calculated_at says when the result was materialized. The separate grain column records the aggregation level so that multiple time granularities can share one table.
Optional dimension_key and dimension_value columns provide breakdowns by plan, region, or acquisition channel without adding pivot columns. definition_version keeps a history of the business logic that produced each row so old and new ARR definitions stay distinct. The metric set can span revenue, retention, efficiency, cash, and product, including ARR, gross retention, CAC payback, runway, and weekly active accounts. Each metric should support current-value, prior-period, and benchmark reads, with the relevant comparison available rather than treating an isolated number as actionable.
Naming conventions that make metric intent machine-readable
The recommended naming structure is {domain}_{measure}_{aggregation}_{grain}. Names become part of the query contract. In revenue_arr_sum_monthly, the segments identify revenue as the domain, ARR as the measure, sum as the aggregation, and monthly as the grain. retention_logo_churn_rate_monthly identifies a monthly logo-churn rate, while product_wau_count_weekly identifies a weekly product-usage count. These names make metric intent available before a query, although abbreviations still require canonical expansions.
The reverse is just as important for interpretation. An abbreviation such as nrr without a canonical expansion leaves meaning unresolved, while mrr_hero_number ties a database identifier to a presentation choice. A table called monthly_metrics cannot substitute for an explicit grain column when a query must span granularities. Similarly, mrr_v2 adds a second identifier where definition_version would keep the link between versions. A single owner for each metric definition provides the final authority needed to prevent naming drift from reopening the ambiguity the schema removed.
Materialized views and metric tables: when to pre-calculate and when a view is enough
A plain view stores a definition. A view can be appropriate for low-cardinality lookups where computation is trivial, but an aggregate view still performs its calculation when queried. It also provides no stored materialization time for calculated_at to describe. A single SELECT against such a view may look simple while still triggering the expensive work the metric schema was meant to settle in advance.
A materialized view persists the calculated result and suits metrics refreshed daily or hourly when that refresh window permits some staleness. Agent reads then select already-computed rows. A dedicated metric table is more suitable when several grains or dimensions must coexist, or when historical rows must retain their original definition versions. It also accommodates results inserted from sources beyond one Postgres instance, including a billing system or bank feed. Background jobs or triggers can populate that table without placing calculation responsibility on the agent.
Pre-calculation is therefore both an optimization and part of the access contract. Supabase’s best-practice guidance names query performance, connection management, and security and RLS as key areas to get right. Reading a stored result avoids aggregation work during the request and reduces the connection time needed to answer it. Date-range partitioning lets queries for recent periods avoid scanning years of older snapshots in large metric tables.
Row-level security and read-only roles that enforce access boundaries at the schema layer
Database permissions remain effective when application guards are bypassed. This makes Postgres the foundation of the boundary. An agent-readable schema needs a dedicated read-only role with SELECT access to metric objects, but no grants on source transactional tables. For example, CREATE ROLE metric_reader NOLOGIN; establishes a role to receive those permissions, and GRANT SELECT ON metric_results TO metric_reader; grants the table read. That grant defines what the role can read, leaving the identity a request represents unspecified.
RLS narrows the permitted rows within that surface. The table can enable it with ALTER TABLE metric_results ENABLE ROW LEVEL SECURITY;. A concrete policy is CREATE POLICY metric_reader_scope ON metric_results FOR SELECT TO metric_reader USING (metric_name LIKE 'retention_%' OR metric_name LIKE 'product_%');. This limits the illustrated role’s access to retention and product metrics, excluding cash or burn. The database filters rows as the query runs, so a query can run even if it would otherwise reach a row the role is not allowed to read.
Column permissions provide a separate, narrower boundary where needed. If the table also holds internal cost data, the role should get GRANT SELECT only on the named columns it is allowed to read. Fine-grained access must also use the identity of whoever the agent acts for, so each requester has their own access boundaries even when requests share a service account. Even with a Supabase service-role key, this restricted role is required, and RLS must be enabled deliberately. Using the restricted role through a connection pooler addresses connection pressure separately from the permissions that govern its reads.
Exposing the metric schema through an MCP server instead of a direct connection
For teams adding usage analytics to an AI agent without a dashboard UI, an MCP server is a way to deliver metrics with controls on access. It exposes an interface limited to metric requests. A direct connection remains subject to the database role and RLS policies that actually apply to it, but a shared credential does not by itself distinguish the requesting users’ entitlements. On every request, the MCP server must check who is asking and enforce what they are allowed to access, while keeping the database restrictions in place beneath it.
MCP’s three primitives have specific roles in this design. Resources show the agent the table schema, metric-name registry, and definition-version log so it can find what is available. Tools perform the bounded reads. A get_metric tool accepts metric_name, grain, and period, then runs a fixed, audited query and returns the corresponding permitted metric row. Prompts provide reusable workflows, including weekly KPI summaries and cohort-retention requests, that use consistent query patterns.
The tool boundary also gives the server a place to block actions that are not allowed before they run. Before the Tool Call: Deterministic Pre-Action Authorization for Autonomous AI Agents, the March 2026 OAP paper, distinguishes that control from containing an action after it occurs. The paper reports that social engineering succeeded against the model 74.6% of the time under a permissive policy. Model behavior is not an authorization mechanism. Rate limits and per-agent quotas likewise belong at the MCP server so concurrent requests do not leave the database responsible for absorbing uncontrolled demand.
Why pre-modeled datasets behind the MCP server protect production Postgres
Correct permissions do not remove the cost of live analytical queries. Production connections remain a shared, finite resource. An MCP server that proxies agent requests to the transactional instance still makes analytical traffic compete with application traffic for pool slots. On smaller compute tiers, that competition can cascade into timeouts. Supabase’s performance guidance notes that rapidly scaling transient workflows, including serverless functions, can particularly benefit from switching from direct database connections to connection pooling.
To remove that competition from the agent read path, the MCP server can serve a governed analytical copy in a format queryable without a production connection. Governed Parquet files queried through DuckDB provide that separation. Each metric row has a matching row in a Parquet partition, with the same metric_name, grain, and period fields. The agent request then reads the analytical dataset rather than the transactional instance. That separation must preserve the requesting identity’s access boundaries instead of treating an exported file as unrestricted data.
The copy’s provenance is as important as its speed. Its calculated_at timestamp establishes freshness, while definition_version identifies the logic behind the number. A snapshot with those fields and a known row count can be audited independently of production. The architecture does not require a dedicated data team or a separate reporting layer, because the schema defines the output once and both humans and agents read that governed output.
What a founder or small team needs to do first to put this in place
For a Supabase founder or small team, the starting sequence is organizational before it is technical. Definitions must precede the database objects that encode them. The first step is to name the five to ten metrics that drive decisions across revenue, retention, cash, and product usage, then assign one owner to each. Each definition should state its period bounds, aggregation method, and exclusions in plain language. For example, “MRR excludes one-time professional services revenue, calculated as of the last day of the calendar month” settles an exclusion and a measurement point before either becomes an agent’s guess.
Once those definitions are settled, the team can create the metric table and populate its first rows manually from known-good numbers. Automation comes after the structure has been validated. The next step is to create the read-only role, apply RLS, and test that the role cannot select from source transactional tables. The team can then expose get_metric through an MCP server and compare its answer for a named metric with the founder’s dashboard.
The first version should remain deliberately small. One metric tree, a handful of data sources, daily refresh, and one reviewer provide a bounded starting point. The team should wait to expand until that version has run for a month without surprising anyone. A narrow schema that both the founder and the agent rely on is more useful than comprehensive coverage that leaves either one questioning the result.


