The Database
The Rule Repository keeps everything in one relational database: source objects and their versions, rules and rule versions, drafts, tests, value templates, notes, the activity history, AI conversations and, when you use Audit, every recorded evaluation. RR, Audit and the MCP server all use the same database.
You need it because RR keeps no data anywhere else: not in the browser, not in files, not with Code Effects. The database is yours. You create it, run it, secure it, back it up and decide who can reach it.
Supported Databases
| Database |
Versions |
Provider package |
| Microsoft SQL Server |
2012 and later |
CodeEffects.Rule.Connection.SqlServer |
| PostgreSQL |
14 and later |
CodeEffects.Rule.Connection.Postgres |
Both are tested end to end with every feature. Support for MySQL and Oracle is planned for a later release. The libraries send only portable SQL and do the same work on both databases; you choose by what your organization runs.
The database can be on the same server as other databases of yours, on a separate server, or in a managed cloud service, as long as your web application can open a connection to it with a standard connection string.
What Is in the Database
The install script creates twenty tables with their keys, indexes and constraints, and one row that records the schema version. It creates no stored procedures, functions, triggers, views, logins, users, roles or schemas. Every statement the libraries send at run time is a plain SELECT, INSERT, UPDATE or DELETE on those tables; the libraries never create, alter or drop anything.
| Area |
Tables |
| Source objects |
SourceRecord, SourceVersion, SourceMember |
| Rules |
RuleRecord, RuleVersion, RuleReference, RuleDraft |
| Tests |
TestRecord, ValueTemplate |
| History and people |
ActivityRecord, NoteRecord, UserRecord |
| Production evaluations (Audit) |
EvaluationRecord, EvaluationValue, EvaluationCount |
| AI features |
ConversationRecord, ConversationMessage, ConversationAttachment, ConfirmationRecord |
| Settings |
SettingRecord (the schema version rows and the signing key for request tokens) |
Getting the Scripts
The database scripts are delivered through the Code Effects Portal as a zip archive, one for each database type. They are not inside the NuGet packages, and no library ever runs them. Each script is named NNNNN.Name.sql: a five-digit number, then a name. The first one, 00001.Database.sql, creates the whole database for version 7. Later releases add update scripts, numbered on from it (00002..., 00003...); see Updating the Database.
Creating the Database on SQL Server
The person who does this needs rights to create a database and tables (for example, dbcreator on the server, or db_owner on a database a DBA has already created).
Create an empty database, with the name, file locations, recovery model and collation your policies call for. For example:
-- An example only: use your organization's own standards
CREATE DATABASE CodeEffectsRules;
The server's default collation is fine; the repository was built and tested on it.
Run 00001.Database.sql against it. In SQL Server Management Studio, open the script, select the new database and execute. From a command line or a deployment pipeline, use sqlcmd, which understands the script's GO separators:
sqlcmd -S your-server -d CodeEffectsRules -E -b -i 00001.Database.sql
-E signs in with Windows authentication; use -U and -P, or -G for Microsoft Entra ID, if your server requires it. -b stops at the first error.
Check the result. The last statement of the script writes the schema version:
SELECT SettingName, SettingValue, CreatedOn FROM dbo.SettingRecord WHERE SettingName LIKE 'Schema.%';
-- Schema.00001 | 00001.Database.sql; 7.0 | (when it ran)
The SQL Server script creates its tables in the dbo schema. Keep dbo unless a policy requires another schema. If one does, create that schema, replace [dbo] with your schema's name throughout the script before you run it, give the same name to the database provider (see Connecting to the Database), and make the same replacement in every update script you run later.
Creating the Database on PostgreSQL
Create an empty database with your standards for owner and locale. Use UTF-8, so text in any language can be stored:
createdb --encoding=UTF8 codeeffects_rules
Run 00001.Database.sql with psql, stopping at the first error:
psql -h your-server -U your-admin -d codeeffects_rules -v ON_ERROR_STOP=1 -f 00001.Database.sql
Check the result:
SELECT settingname, settingvalue, createdon FROM settingrecord WHERE settingname LIKE 'Schema.%';
The PostgreSQL script creates its objects in the first schema of the search_path, which is public by default. To use another schema, create it and set the search path in the same session before running the script, then give the database provider the same schema name:
CREATE SCHEMA rr;
SET search_path TO rr;
-- then run 00001.Database.sql in this session
Give the provider the schema name exactly as PostgreSQL stored it: an unquoted CREATE SCHEMA rr stores it in lower case. Text comparisons in PostgreSQL are case-sensitive, so a user ID or a source alias that differs only in letter case is a different value there; on SQL Server's default collation it is the same value. Keep the IDs your application sends consistent.
Your Security Policies and Logins
The Rule Repository brings no accounts of its own. The scripts create no logins, users, roles or permissions, and the libraries connect with whatever connection string your application gives them. Everything about access follows your organization's policies:
- Authentication. Windows integrated authentication, SQL Server logins, Microsoft Entra ID, PostgreSQL roles with passwords, certificates or managed identities: whatever your connection string can express and your standards allow.
- Two accounts are recommended.
- A deployment account that can create and alter tables. Your DBA or your pipeline uses it to run
00001.Database.sql and later update scripts. The application never uses it.
- An application account that can only read and write rows in the repository's tables:
SELECT, INSERT, UPDATE and DELETE. The libraries need nothing more, because they never change the schema and use no stored procedures.
- Write access for the application alone. The
SettingRecord table holds the key that signs RR's anti-forgery tokens and sealed test runs. Anyone who can write to the database can read or replace that key and change the history, so give write access only to the application account and to the people who already administer the database.
- Network, encryption, backups and auditing (firewall rules, TLS to the database, encryption at rest, backup schedules, retention, database auditing) are yours to set, as for any other business database.
An example of an application account, to adapt to your own policy:
-- SQL Server: a contained database user for the web application (example only)
CREATE USER RulesApp WITH PASSWORD = '...'; -- or FROM LOGIN, or FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER RulesApp;
ALTER ROLE db_datawriter ADD MEMBER RulesApp;
-- PostgreSQL: a login role for the web application (example only)
CREATE ROLE rules_app LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE codeeffects_rules TO rules_app;
GRANT USAGE ON SCHEMA public TO rules_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rules_app;
-- tables added by later update scripts need the same grant, or set default privileges for the deployment account:
ALTER DEFAULT PRIVILEGES FOR ROLE your_admin IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rules_app;
On PostgreSQL, the identity columns need no separate sequence grants: INSERT on the table is enough.
Keep the connection string in your secret store (Azure Key Vault, AWS Secrets Manager, user secrets in development, environment variables), not in source control.
Checking the Schema Version at Startup
Each release of Repository and Audit needs a certain schema version. Your application checks it once at startup and decides what to do: stop, or log and carry on.
using CodeEffects.Rule.Connection;
using CodeEffects.Rule.Repository;
SchemaCheckResult schema = Context.CheckDatabase(db);
if (schema.IsCurrent)
logger.LogInformation("Rule Repository: {Message}", schema.Message);
else
logger.LogError("Rule Repository: {Message}", schema.Message); // or throw, to stop the application
SchemaCheckResult tells you:
| Member |
Meaning |
CurrentVersion |
The highest Schema.NNNNN number recorded in the database, or null when there is none (not created by the install script). |
RequiredVersion |
The version this library needs. |
IsCurrent |
true when the database is at the required version or later. A later version is fine: schema changes within a major version only add. |
Message |
What was found, in words, safe to log or show. When the database is behind, it names the update scripts to run, in order. |
An application that uses Audit checks with AuditRecorder.CheckDatabase(db) as well; each library checks for the version it needs. The check reads one row. Nothing is cached, and nothing is checked on ordinary requests.
If you skip the check and the database is behind, the first statement that touches a missing table or column fails with control number CE19004, whose message names the scripts to run, in place of the database's own error.
Sizing and Maintenance
- The repository's own data (sources, rules, tests, history) stays small: thousands of rules take megabytes.
- Evaluations grow with production traffic when you use Audit: one row per evaluation, plus one row for each analytics value you choose to keep. Plan storage and retention for them as for any log table. See Audit Settings.
- Standard index maintenance and statistics jobs are enough; the schema needs no special jobs.