Database Schema and Permissions

This page is for teams whose database is managed by a DBA group: what the CritterWatch console creates, when it runs DDL, the smallest set of rights it can run with, and how to produce SQL scripts for review.

It covers the console's own database only. Your monitored services get no new tables from CritterWatch; the Wolverine.CritterWatch client package reads the storage each service already has.

The short version

The console manages its own schema objects for as long as it runs, not only at install time. It creates tables on first use, adds and drops monthly partitions, and applies schema changes after an upgrade. A deployment where the console's database account has no DDL rights at all is not supported today.

What is supported, and what we recommend, is confining the console to two schemas that its own role owns. Your DBA team creates the role and the schemas; the console manages only what is inside them. On PostgreSQL that ownership is the whole grant. SQL Server needs four database-level permissions on top of it.

What the console creates

Flavour	Where	What
PostgreSQL (`CritterWatch`)	schema `critterwatch`	The console's data: event store tables (`mt_events`, `mt_streams`, `mt_event_progression`), one `mt_doc_*` table per stored document type, their functions and indexes, and a sequence
	schema `critterwatch_wolverine`	Wolverine's durability tables for the console's own messaging: `wolverine_incoming_envelopes`, `wolverine_outgoing_envelopes`, `wolverine_dead_letters`, `wolverine_nodes`, `wolverine_node_assignments`, `wolverine_node_records`, `wolverine_agent_restrictions`, `wolverine_control_queue`
SQL Server (`CritterWatch.SqlServer`)	schemas `critterwatch` and `critterwatch_wolverine`	The same split, with `pc_*` table names for the console's data, plus a table type and stored procedures in the durability schema
	database level	One partition function and one partition scheme for the metrics table (`pf_pc_doc_metricssample_bucket_end`, `ps_pc_doc_metricssample_bucket_end`). SQL Server does not put these inside a schema
SQLite (`CritterWatch.Sqlite`)	one file	Everything in a single database file. SQLite has no schemas, so the durability tables carry a `critterwatch_wolverine_` name prefix instead

The schema name is the schemaName argument to AddCritterWatch(...) and defaults to critterwatch. The durability schema is always that name with _wolverine appended.

Two things add to this list:

A database-backed transport. If the console listens on a Wolverine PostgreSQL or SQL Server queue instead of a message broker, Wolverine creates that transport's queue tables as well.
Embedded mode. An embedded console keeps its own durability tables alongside the host application's. See Embedded deployment.

The metrics table is partitioned

On PostgreSQL and SQL Server, the metrics history table (mt_doc_metricssample on PostgreSQL, pc_doc_metricssample on SQL Server) is range-partitioned by month. The window is derived from SampleRetentionPeriod: enough months behind the current one to honour the retention setting, three months ahead, and a default partition that catches anything outside the window.

SQLite has no partitioning. Retention there is a row-by-row delete.

When the console runs DDL

When	What runs	If it is refused
Every startup	Applies outstanding changes to the Wolverine durability tables	The host does not start
Every startup (PostgreSQL)	Makes sure the metrics table exists and creates any monthly partitions the window now needs	The console starts. System health reports the metrics store as degraded, and the System Throughput chart and metrics retention do not work until it is fixed
First use of each document type	Creates that type's table (`mt_doc_` or `pc_doc_`) if it is still missing, or alters it after an upgrade. On SQL Server this is also when the metrics table and its partition function are created	The read or write that needed the table fails
Hourly retention pass	Drops metrics partitions that have aged out of `SampleRetentionPeriod`	A warning is logged and retention falls back to deleting rows, which reclaims storage more slowly

The practical consequence is in the third row: the console does not create everything at startup. An idle console creates only part of its schema, and the rest appears as telemetry arrives and features are used. Every one of those tables is in the db-dump script, though (see below), so a database built from the script already has them.

Recommended: a role that owns its two schemas

Have your DBA team create a dedicated login and the two schemas, owned by that login. Pick your database:

PostgreSQLSQL Server

sql

CREATE ROLE critterwatch_app LOGIN PASSWORD '...';

CREATE SCHEMA critterwatch AUTHORIZATION critterwatch_app;
CREATE SCHEMA critterwatch_wolverine AUTHORIZATION critterwatch_app;

sql

CREATE LOGIN critterwatch_app WITH PASSWORD = '...';

USE critterwatch;
CREATE USER critterwatch_app FOR LOGIN critterwatch_app;
GO
CREATE SCHEMA critterwatch AUTHORIZATION critterwatch_app;
GO
CREATE SCHEMA critterwatch_wolverine AUTHORIZATION critterwatch_app;
GO

GRANT CREATE TABLE        TO critterwatch_app;
GRANT CREATE TYPE         TO critterwatch_app;
GRANT CREATE PROCEDURE    TO critterwatch_app;
GRANT ALTER ANY DATASPACE TO critterwatch_app;

SQLite needs none of this: the console only needs read and write access to its database file and the directory that holds it.

PostgreSQL

The three statements are the whole grant. The role needs no CREATE on the database, no rights in public or any other schema, and no elevated attributes. With the schemas in place the console starts cleanly, creates its tables on demand, maintains the metrics partitions, and applies its own changes after an upgrade.

Three details matter:

The role must own the objects, not merely hold grants on them. PostgreSQL only lets a table's owner add a partition to it, alter it, or drop it. GRANT CREATE ON SCHEMA on top of tables another role owns is enough until the first new partition is needed, and then startup logs 42501: must be owner of table mt_doc_metricssample.
Create both schemas up front, or grant CREATE on the database. If a schema is missing and the role cannot create it, the host fails to start with 42501: permission denied for database.
Give the console its own database where you can. The deployment guide already advises against sharing the console's database with anything performance-sensitive; a dedicated database also makes the permission boundary trivial to audit.

SQL Server

Schema ownership is necessary on SQL Server but not sufficient. Creating an object there takes a database-level CREATE permission as well as control of the schema it goes into, and partition functions are not schema objects at all. That is what the four GRANT statements are for:

Permission	What needs it	Without it
`CREATE TABLE`	Every table in both schemas	The host does not start: `CREATE TABLE permission denied in database`
`CREATE TYPE`	The `EnvelopeIdList` table type in the durability schema	The host does not start
`CREATE PROCEDURE`	Wolverine's durability stored procedures	The host does not start
`ALTER ANY DATASPACE`	The partition function and scheme behind the metrics table	The console starts, but the metrics table cannot be created: `User does not have permission to perform this action`

The three CREATE permissions are database-wide in name only. Each one also requires ALTER on the target schema, and the login has that only on the two schemas it owns. ALTER ANY DATASPACE is genuinely database-wide, which is one more reason to give the console a database of its own.

These recipes were exercised against PostgreSQL 14 and SQL Server 2025.

Generating SQL scripts for review

The console host exposes the standard JasperFx database commands, the same ones a Marten or Wolverine application has. They are available when the host's Program.cs ends with return await app.RunJasperFxCommands(args); rather than app.Run(); (see CLI for the wiring).

PostgreSQLSQL Server

bash

dotnet run -- db-list                                             # the databases the host manages
dotnet run -- db-dump console.sql    -d marten://store/           # full DDL for the console's data
dotnet run -- db-dump durability.sql -d wolverine://messages/main # full DDL for Wolverine durability
dotnet run -- db-patch patch.sql     -d marten://store/           # differences against the live database
dotnet run -- db-assert                                           # non-zero exit if anything differs

bash

dotnet run -- db-list                # the databases the host manages
dotnet run -- db-dump console.sql    # full DDL for the console's data
dotnet run -- db-patch patch.sql     # differences against the live database
dotnet run -- db-assert              # non-zero exit if anything differs

The PostgreSQL console manages two databases in the command line's terms, one per schema, so db-dump and db-patch need the -d flag. Use the SubjectUri value that db-list prints. Use that value and not the DatabaseUri: both entries share a connection, so the shorter DatabaseUri matches the wrong one.
The SQL Server console lists a single database (polecat://store/), so -d is not needed there. See the limits below for what that leaves out.
On SQL Server and SQLite, db-dump does not connect to the database, so you can run it on a build machine. On PostgreSQL it currently does: listing Marten's schema objects also provisions the mt_hilo table and its function, which one of the console's documents (the metrics buckets, with a numeric id) needs. Point it at a database where that is acceptable, such as the console's own or a scratch copy (marten#5665).
db-patch connects, reads the catalog, and writes two files: the patch and a matching .drop.sql rollback. It changes nothing.

If your DBA team applies a script themselves, the objects must still end up owned by the console's role. Run the script as that role (SET ROLE critterwatch_app; first), or transfer ownership afterwards.

What the scripts do not cover

Treat the generated scripts as a review artifact and a head start. They are not a complete substitute for the console managing its own schema.

Fixed in 1.1.0-rc.1: every document table is in the script

Through 1.1.0-beta.8 the console registered only the document types its projections publish, and created the rest on first use. db-dump left those tables out (mt_doc_agenthealthstate, mt_doc_nodeheartbeat, mt_doc_messagingmetricsbucket, the alert defaults and about twenty more, plus mt_hilo / pc_hilo), db-patch did not report them, and db-assert passed on a database that lacked them (GH-1410). Every document type is registered up front now, on all three flavours, so all three commands see them. If you scripted a database from an earlier version, run db-patch against it to get the missing tables.

The gaps that remain:

On SQL Server, the durability schema is not scripted at all. db-list shows only the console's data store, so no command emits the critterwatch_wolverine tables, table type or stored procedures. The console creates them at startup.
Partition names are dated. The script contains the monthly partitions for the window as of the day it was generated. A script generated in October and applied the following March is already behind.

Do not turn off automatic schema creation

Setting AutoCreate.None on the console's store, or ResourceAutoCreate = AutoCreate.None through CritterStackDefaults, does not produce a working scripts-only deployment. On PostgreSQL the console starts, and then every read of a table that the script did not contain fails with 42P01: relation "critterwatch.mt_doc_..." does not exist. On SQL Server the host does not start at all: Invalid object name 'critterwatch_wolverine.wolverine_incoming_envelopes'. Leave auto-creation at its default and restrict the role instead.

Upgrades

With the recommended setup there is nothing to do: a new version applies its durability changes at startup and its document table changes on first use, inside the two schemas it owns.

To let a DBA team see a change before it happens:

Read the Upgrade Notes for the versions you are crossing. Releases that change database objects say so, including any that need a one-time data migration.
Build the new version of the console host and run db-patch with it against a restored copy of production (or against production itself; the command only reads). Run it once per database with -d.
Review the patch. Either let the console apply it on its next start, or have the DBA team apply it first as the console's role.
Start the new version against the restored copy before you start it against production.

The coverage limits above apply here too: on SQL Server the durability schema is not in the patch.

If a release needs rights the role lacks, the failure looks like one of the rows in When the console runs DDL. The one that stops the console is the durability migration at startup; the others degrade a feature and say so in the log and in System health.

Next steps

Installation: creating the database for each flavour.
Configuration Reference: SampleRetentionPeriod and the other retention settings.
Upgrade Notes: per-release changes, including schema changes.

Database Schema and Permissions ​

What the console creates ​

The metrics table is partitioned ​

When the console runs DDL ​

Recommended: a role that owns its two schemas ​

PostgreSQL ​

SQL Server ​

Generating SQL scripts for review ​

What the scripts do not cover ​

Upgrades ​

Next steps ​