Architecture

Who this document is for

• Developers evaluating or contributing to SQL Cor
• Security reviewers auditing how SQL Cor handles privileged operations
• Architects deciding whether to deploy SQL Cor in their environment
• Administrators who want to understand what happens under the hood

This document explains the why behind design decisions — not line-by-line code.

---

System overview

SQL Cor is a single Creatio package. No external services. No microservices. No separate deployment. The entire system installs and runs inside the Creatio runtime.

┌──────────────────────────────────────────────────────────────┐
│                      SQL Cor Package                         │
│                                                              │
│  ┌─────────────────────────────────────────────────────┐    │
│  │                  Browser (Frontend)                  │    │
│  │                                                      │    │
│  │  ┌─────────────────────┐  ┌──────────────────────┐  │    │
│  │  │  DnSqlTerminalPage  │  │  DnSqlSettingsPage   │  │    │
│  │  │  Terminal UI (JS)   │  │  Administration UI   │  │    │
│  │  └──────────┬──────────┘  └──────────┬───────────┘  │    │
│  └─────────────┼────────────────────────┼──────────────┘    │
│                │        HTTP / JSON      │                    │
│  ┌─────────────▼────────────────────────▼──────────────┐    │
│  │           Creatio Server (Backend)                   │    │
│  │                                                      │    │
│  │         DnSqlTerminalService (entry point)           │    │
│  │              9-step execution pipeline               │    │
│  │                                                      │    │
│  │  ┌──────────────┐  ┌────────────────┐               │    │
│  │  │ AccessChecker│  │  QueryParser   │               │    │
│  │  └──────────────┘  └────────────────┘               │    │
│  │  ┌──────────────┐  ┌────────────────┐               │    │
│  │  │SettingsReader│  │  AuditLogger   │               │    │
│  │  └──────────────┘  └────────────────┘               │    │
│  └──────────────────────────────┬───────────────────────┘    │
│                                 │                             │
│  ┌──────────────────────────────▼───────────────────────┐    │
│  │              Database (PostgreSQL or MSSQL)           │    │
│  │  SysUserInRole · DnSqlAccessRule · DnSqlBlacklist    │    │
│  │  DnSqlQueryLog · DnSqlSettings · Application data    │    │
│  └──────────────────────────────────────────────────────┘    │
└──────────────────────────────────────────────────────────────┘

Package contents:

• 4 C# backend services
• 2 JavaScript UI modules
• 1 navigation redirect handler
• 3 database objects (tables)
• 6 system settings entries
• 1 section registration (sidebar entry)

---

Backend architecture ^backend

Component roles

DnSqlTerminalService.cs — Orchestrator

The single REST endpoint the frontend calls. Receives the query, the user context, and execution flags. Coordinates all other services in a fixed pipeline order.

Responsibility: Orchestration only. Contains no business logic.

---

DnSqlAccessChecker.cs — Authorization ^access-checker

Determines whether the current user may:

• Use SQL Cor at all
• Execute the specific operation type requested

SysAdmin detection: Uses SysUserInRole table — not SysUserInfo.UserType (that field is not exposed in modern Creatio versions).

Hardcoded SysAdmin role GUID: A29A3BA5-4B0D-DE11-9A51-005056C00008

Access rule lookup: Queries DnSqlAccessRule for active rules matching the current user or any of their roles. If multiple rules exist, the highest access level wins.

Responsibility: Answer “can this user perform this action right now?”

---

DnSqlQueryParser.cs — Query Analysis ^query-parser

Parses incoming SQL to determine:

1. What operation type it is (ReadOnly / DML / DDL)
2. Whether it matches any blacklist pattern
3. Whether safety modifications are needed (LIMIT injection)

CTE awareness: The parser understands Common Table Expressions. A query like:

WITH temp AS (SELECT "Id" FROM "Contact")
DELETE FROM "Contact" USING temp WHERE ...

Is correctly classified as DML even though it starts with WITH.

Blacklist sources (two layers):

Layer	Source	Can be changed?
Hardcoded	Compiled into DnSqlQueryParser.cs	No — requires new package version
Database	DnSqlBlacklistEntry table	Yes — via Administration → Blacklist tab

Hardcoded blocked functions/keywords:

Pattern	Reason
xp_cmdshell	SQL Server OS shell execution
pg_read_file	Reading server filesystem
pg_sleep	Intentional blocking of DB process
pg_terminate_backend	Killing database connections
OPENROWSET	External data source access
DROP DATABASE	Catastrophic data destruction
Any DnSql* table modification	Protecting SQL Cor’s own config

Literal masking: Before checking against blacklist patterns, the parser replaces all content inside single quotes with spaces:

Input:  SELECT * FROM "Contact" WHERE "Notes" = 'DROP TABLE test'
Masked: SELECT * FROM "Contact" WHERE "Notes" = '               '

This prevents false positives — the word DROP inside a string literal should not trigger the blacklist. The original query (unmasked) is sent to the database — masking is only used for the safety analysis step.

Multi-statement protection (ReadOnly users): For ReadOnly access level, the parser detects semicolons between statements. If multiple statements are found, the entire query is rejected before execution. This prevents ReadOnly users from appending DML/DDL after a SELECT.

Responsibility: Understand what the user is trying to do. Validate safety.

---

DnSqlSettingsReader.cs — Configuration

Reads runtime configuration from Creatio’s SysSettings table.

Setting key	Default	Purpose
DnSqlMaxRowLimit	1000	Max rows per SELECT
DnSqlDefaultTimeout	30s	Query execution timeout
DnSqlLogRetentionDays	90	Audit log retention
DnSqlDmlEnabled	true	Master DML switch
DnSqlDdlEnabled	false	Master DDL switch
DnSqlBlacklistEnabled	true	Blacklist enforcement switch

Returns sensible defaults if a setting is missing or corrupted.

Responsibility: Provide settings to other components reliably.

---

The 9-step execution pipeline ^pipeline

Every query execution follows this exact sequence. Any step can short-circuit with an error — subsequent steps do not run. Step 9 (audit log) always runs, even for blocked or failed queries.

Request arrives at DnSqlTerminalService
         │
         ▼
[Step 1]  Read system settings
         │  — Timeout, MaxRows, feature flags
         ▼
[Step 2]  Check user authentication
         │  — Is the Creatio session valid?
         │  — Short-circuit: 403 if not authenticated
         ▼
[Step 3]  Check user has an access rule
         │  — DnSqlAccessChecker: SysAdmin check OR active DnSqlAccessRule
         │  — Short-circuit: "Access denied" if no valid rule
         ▼
[Step 4]  Parse query
         │  — DnSqlQueryParser: determine operation type (ReadOnly/DML/DDL)
         │  — Apply literal masking
         ▼
[Step 5]  Validate operation type vs access level
         │  — ReadOnly user + DML query → reject
         │  — DML user + DDL query → reject
         │  — Short-circuit: "Access denied. This operation requires X access."
         ▼
[Step 6]  Check master feature flags
         │  — DmlEnabled=false + DML query → reject
         │  — DdlEnabled=false + DDL query → reject
         │  — Short-circuit: feature is disabled
         ▼
[Step 7]  Run blacklist checks
         │  — Hardcoded patterns (always enforced)
         │  — Database patterns from DnSqlBlacklistEntry (if BlacklistEnabled=true)
         │  — Short-circuit: "This query is blocked. Reason: X"
         ▼
[Step 8]  Apply safety modifications
         │  — ReadOnly/SELECT without LIMIT: inject LIMIT X (PG) or TOP X (MSSQL)
         │  — Apply MaxRowLimit setting
         ▼
[Step 9]  Execute query
         │  — Dry Run=true: open transaction → execute → collect results → ROLLBACK
         │  — Dry Run=false + ReadOnly: DBExecutor.ExecuteScalar / Reader
         │  — Dry Run=false + DML/DDL on Cloud: reflection workaround (see below)
         │  — Dry Run=false + DML/DDL on On-Premise: DBExecutor.Execute
         │  — Apply timeout setting
         │  — Unwrap TargetInvocationException → surface real DB error to user
         ▼
[Step 10] Write audit log entry (ALWAYS — even for blocked/failed queries)
         │  — DnSqlQueryLog: user, timestamp, query text, status, rows, duration
         ▼
Response returned to frontend

i

Note Step 10 Always Runs The audit log captures every attempt — including blocked queries and failures. This is intentional: knowing that someone

tried to run a blocked operation is as important as knowing what succeeded.

---

Cloud DML workaround ^cloud-workaround

This is the most architecturally significant part of SQL Cor.

The problem: Creatio Cloud blocks DBExecutor.Execute(string) at the platform security layer. Even SysAdmin users hit this restriction for DML/DDL.

The workaround: SQL Cor uses CustomQuery.ExecuteNonQuery(DBExecutor) via .NET reflection:

Standard path (blocked in Cloud):
  DBExecutor.Execute("UPDATE ...") → ❌ Platform security blocks this

SQL Cor's path (works in Cloud):
  new CustomQuery(connection, "UPDATE ...")
    → GetType().GetMethod("ExecuteNonQuery", BindingFlags)
    → method.Invoke(query, new object[] { dbExecutor })
    → ✅ Executes successfully

§

Important This is Not a Security Bypass This uses a different internal Creatio mechanism that the platform’s security layer permits. All role-based restrictions still apply at the database level. The workaround is specifically for getting past the application-layer restriction on

DBExecutor.Execute.

Exception unwrapping: .NET reflection wraps real exceptions in TargetInvocationException. SQL Cor explicitly unwraps these so the user sees the actual PostgreSQL/MSSQL error, not a confusing reflection wrapper.

On-premise: The reflection path is not needed. SQL Cor detects the environment and uses the direct DBExecutor.Execute() path automatically.

Timeout handling:

• dbExecutor.CommandTimeout — works ✅
• CustomQuery.CommandTimeout — does not exist in this Creatio version ❌

SQL Cor sets timeout via dbExecutor.CommandTimeout before passing the executor to the query.

---

Frontend architecture ^frontend

Two modules, one experience

SQL Cor has two UI surfaces:

Module	File	Handles
Terminal	DnSqlTerminalPage.js	Editor, execution, results, footer
Administration	DnSqlSettingsPage.js	All 4 admin tabs

Both are classic AMD-style modules (not Freedom UI components). This was a deliberate choice — AMD modules have more direct DOM control, which was necessary for the mount strategy described below.

---

Soft-swap navigation ^soft-swap

Switching between Terminal and Administration does not trigger a full page reload.

Both modules register themselves as globals on mount:

window.__DnSqlTerminalPage = { render, destroy, ... };
window.__DnSqlSettingsPage = { render, destroy, ... };

Shared navigation state:

window.DnSqlNav = {
    container: <DOM reference>,
    terminalState: { query: '...', cursorPos: ... }
};

When the user clicks “Administration”: Terminal calls window.__DnSqlSettingsPage.render(). When the user clicks “SQL Terminal”: Settings calls window.__DnSqlTerminalPage.render().

The editor content is preserved in terminalState across the switch.

---

Mount strategy: inside the center panel ^mount-strategy

§

Important Counter-intuitive but critical design decision

Naive approach (wrong): Mount SQL Cor as a full-viewport overlay with position: fixed; z-index: 99999.

Why it’s wrong: Creatio’s navigation, header, and sidebar must remain functional. A full overlay would block them entirely, breaking navigation.

SQL Cor’s approach: Mount inside #centerPanelContainer — Creatio’s designated content area:

┌──────────────────────────────────────────────────────┐
│  Creatio Header                                      │
├────────┬──────────────────────────────┬──────────────┤
│        │                              │              │
│Sidebar │   #centerPanelContainer      │ Right panel  │
│        │   ← SQL Cor mounts HERE       │              │
│        │   position: absolute         │              │
│        │   inset: 0; z-index: 1       │              │
└────────┴──────────────────────────────┴──────────────┘

Mount CSS: position: absolute; inset: 0; z-index: 1 (NOT position: fixed — that would escape the container)

---

CSS sibling-hiding ^sibling-hiding

Once mounted, SQL Cor hides all OTHER content inside #centerPanelContainer without destroying the DOM (which would break Angular’s state management).

Solution: Add body.dn-sql-active class when SQL Cor mounts. Remove it when it unmounts.

CSS rules hide all siblings along the DOM path while preserving the tree:

body.dn-sql-active crt-7x-schema > *:not(#7xContainer)           { display: none !important; }
body.dn-sql-active #7xContainer > *:not(#mainContentWrapper)     { display: none !important; }
body.dn-sql-active #mainContentWrapper > *:not(#ViewModuleContainer) { display: none !important; }
body.dn-sql-active #ViewModuleContainer > *:not(#centerPanelContainer) { display: none !important; }
body.dn-sql-active #centerPanelContainer > *:not(#centerPanel)   { display: none !important; }
body.dn-sql-active #centerPanel > *:not(#dn-root)                { display: none !important; }

!important is required because Creatio has a global * { padding: 0 } reset that conflicts with standard specificity rules.

On unmount: body.dn-sql-active is removed → all hidden siblings become visible again → Angular state is intact.

---

URL watcher and self-recovery ^url-watcher

Creatio’s Application Hub can collapse #centerPanelContainer to 0×0 when switching sections internally. SQL Cor has a recovery mechanism:

setInterval(() => {
    const cpc = document.getElementById('centerPanelContainer');
    const isOurUrl = location.hash.includes('DnSqlTerminalPage')
                  || location.hash.includes('DnSqlSettingsPage');

    if (isOurUrl && cpc.offsetWidth === 0 && cpc.offsetHeight === 0) {
        // Container collapsed while we should be visible
        window.__DnSqlReloadGuard = true;
        window.location.reload();
    }
}, 300ms);

__DnSqlReloadGuard prevents reload loops — after one reload, the URL is intact and the container is no longer collapsed.

i

Note Pragmatic Workaround This is not a “clean” solution — a clean solution would require deeper Creatio platform integration. But it works reliably in practice, and the reload is rare (only when the center panel collapses, which happens on specific navigation patterns).

---

Notification filter ^notification-filter

Creatio Application Hub emits toast notifications like “Entity schema name for data source PDS is not provided”. These are harmless but noisy.

SQL Cor uses a MutationObserver to detect and remove these specific notifications:

new MutationObserver((mutations) => {
    document.querySelectorAll('mat-snack-bar-container').forEach(toast => {
        if (toast.textContent.includes('Entity schema name for data source PDS')
         || toast.textContent.includes('SysFreedomDashboard')) {
            toast.remove();
        }
    });
}).observe(overlayContainer, { childList: true, subtree: true });

Observer is registered on mount, disconnected on unmount.

---

Timer management ^timer-management

The footer shows session duration and memory usage, updated every second.

Without care, timers accumulate across re-renders. SQL Cor uses a global registry:

window.__DnSqlTimers = {
    session: null,
    memory: null
};

Before setting a new interval: clearInterval(window.__DnSqlTimers.session). On unmount: both timers are cleared.

---

Editor implementation ^editor

The query editor is a custom contentEditable div implementation — not CodeMirror or Monaco.

Why not a library? Adding a full editor library would significantly increase package size and create a dependency management problem within Creatio’s package system. For the use case (short queries, not large codebases), a custom implementation is sufficient.

What it provides:

• Line numbers (sibling div, scroll-synchronized)
• Syntax highlighting (regex-based keyword detection)
• Tab indentation
• Autosave to localStorage
• Standard keyboard shortcuts

What it doesn’t provide:

• Autocomplete
• Error squiggles / inline validation
• Code folding
• Multi-cursor editing

These are acceptable tradeoffs for admin tooling where queries are typically short.

---

State persistence ^state-persistence

User preferences stored in localStorage:

Key	Stores
dn-sql-theme	"dark" or "light"
dn-sql-lang	"uk" or "en"
dn-sql-last-query	Last query text (restored on page load)

State is per-browser, per-device. Not synced across sessions or devices.

---

Database schema ^database-schema

SQL Cor creates three tables. All follow Creatio’s standard schema conventions.

DnSqlAccessRule

Stores per-user/role access permissions.

Column	Type	Notes
Id	UUID	Primary key
UserId	UUID?	Specific user (NULL if role-based rule)
RoleId	UUID?	Role (NULL if user-based rule)
AccessLevel	INT	10=ReadOnly · 20=DML · 30=DDL
ValidUntil	TIMESTAMP?	NULL = permanent
Comment	TEXT?	Admin-written reason
IsActive	BOOL	false = Revoked
CreatedOn, ModifiedOn, etc.	standard	Creatio audit columns

Constraint: Exactly one of UserId or RoleId must be set.

---

DnSqlBlacklistEntry

Stores blocked query patterns.

Column	Type	Notes
Id	UUID	Primary key
Pattern	TEXT	The value to match
EntryType	INT	1=Table · 2=Field · 3=Keyword
Source	INT	1=System (built-in) · 2=Manual
Description	TEXT?	Shown to users on block
IsActive	BOOL	false = disabled
CreatedOn, etc.	standard	Audit columns

System rules: Source=1 rows are seeded at install and have no action buttons in the UI.

---

DnSqlQueryLog

Audit log of executed queries.

Column	Type	Notes
Id	UUID	Primary key
QueryText	TEXT	Full query text
UserId	UUID	Who ran it
OperationType	INT	10/20/30 (access level)
Status	INT	1=Success · 2=Error · 3=Blocked
RowsAffected	INT?	NULL for errors/blocked
DurationMs	INT	Execution time
ErrorMessage	TEXT?	Set if Status=Error
BlockReason	TEXT?	Set if Status=Blocked
WasDryRun	BOOL	Whether this was a Dry Run
CreatedOn	TIMESTAMP	When the query ran

Retention: A nightly cleanup job deletes records older than DnSqlLogRetentionDays.

---

Security model ^security-model

What SQL Cor enforces

Check	How	Who controls
User identity	Creatio session authentication	Creatio platform
Role membership	SysUserInRole lookup	Creatio admin
SQL Cor access	DnSqlAccessRule lookup	SQL Cor admin
Operation type	DnSqlQueryParser	SQL Cor admin (levels)
Blacklist (manual)	DnSqlBlacklistEntry	SQL Cor admin
Blacklist (hardcoded)	Compiled in parser	Nobody (immutable)
DnSql* table protection	Compiled in parser	Nobody (immutable)
LIMIT injection	Compiled in service	Nobody (immutable)
Literal masking	Compiled in parser	Nobody (immutable)
Audit logging	Step 10 of pipeline	Admin (Clear Logs only)

What SQL Cor does NOT enforce

• Database-level permissions: If the Creatio DB service account has broad access, SQL Cor cannot restrict below that level
• Schema-level restrictions: SQL Cor doesn’t check which tables you query, only how (via blacklist patterns)
• Row-level security: Queries see what the underlying DB account sees
• Resource quotas: Beyond timeout, no CPU/memory limits per query

Trust boundaries

Browser
  ↓ (Creatio session cookie)
Creatio App Server
  ↓ (role check via SysUserInRole)
DnSqlAccessChecker
  ↓ (query analysis)
DnSqlQueryParser (blacklist + type check)
  ↓ (if all checks pass)
Database (PostgreSQL / MSSQL)

SQL Cor trusts that:

1. Creatio’s authentication is not bypassed
2. SysAdmin role is granted appropriately
3. The DB service account’s permissions are appropriate for the environment

---

Extension points ^extension-points

If you want to fork or extend SQL Cor, these are the natural seams:

Adding a new access level

1. Update OperationType enum (currently 10/20/30)
2. Update DnSqlAccessChecker level comparison logic
3. Update DnSqlQueryParser operation classification
4. Update the Grant Access dialog dropdown in DnSqlSettingsPage.js

Adding new hardcoded blocked patterns

1. Add to the hardcoded pattern list in DnSqlQueryParser.cs
2. Re-compile and re-publish the package

Adding a new blacklist pattern type

Currently: Table / Field / Keyword. To add (e.g., “Schema”):

1. Add enum value to EntryType
2. Add matching logic in DnSqlQueryParser
3. Update the Add Rule dialog type options

Adding a new language

Currently: English (en) / Ukrainian (uk). The language dictionaries (DICT) are inline in the JS modules. To add a new language:

1. Add a new language key to DICT in both JS modules
2. Add a new flag/label to the language button renderer
3. Update dn-sql-lang localStorage value handling

For a proper Marketplace release, these should be migrated to Creatio’s SysTranslation system instead.

Custom audit log destinations

Currently logs go to DnSqlQueryLog only. To add a secondary destination (e.g., Splunk):

1. Create an interface IDnSqlAuditWriter
2. Implement it for your destination
3. Wire into Step 10 of the pipeline in DnSqlTerminalService

---

File structure ^file-structure

DnSqlTerminal/                         ← Creatio package root
│
├── DnSqlAccessChecker.cs              ← Role and access verification
├── DnSqlQueryParser.cs                ← SQL parsing, blacklist, LIMIT injection
├── DnSqlSettingsReader.cs             ← SysSettings reader with defaults
├── DnSqlTerminalService.cs            ← REST endpoint + 9-step pipeline
│
├── DnSqlTerminalPage.js               ← Terminal UI (editor, results, footer)
├── DnSqlSettingsPage.js               ← Administration UI (4 tabs)
├── DnSQLTerminal_ListPage.js          ← Navigation redirect handler
│
├── Object: DnSqlAccessRule            ← Access rules table
├── Object: DnSqlBlacklistEntry        ← Blacklist patterns table
├── Object: DnSqlQueryLog              ← Audit log table
│
├── Section: DnSQLTerminal             ← Sidebar section registration
│
└── SysSettings:
    ├── DnSqlMaxRowLimit
    ├── DnSqlDefaultTimeout
    ├── DnSqlLogRetentionDays
    ├── DnSqlDmlEnabled
    ├── DdlEnabled
    └── DnSqlBlacklistEnabled

---

Honest engineering notes

Things I’d do differently if rebuilding:

i

Note Frontend mount complexity Mounting inside

#centerPanelContainer rather than full-page took significant trial and error. Freedom UI components integrate more naturally with Creatio’s routing. For a future version, migrating to Freedom UI would reduce the mount complexity considerably.

i

Note Cloud workaround fragility The reflection-based DML workaround depends on internal Creatio implementation details that could change between versions. The ideal fix is Creatio publishing an official API for this use case.

i

Note Audit log retention simplicity The current nightly-delete approach is fine for most cases, but compliance environments would want soft-delete with an archive table. Not urgent, but worth noting for future roadmap.

i

Note AST-based blacklist Regex matching against SQL text catches most dangerous patterns, but a proper SQL AST parser would be more reliable. For example, “DELETE without WHERE clause” is hard to express as a regex but trivial as an AST check. Future enhancement candidate.

---

Cross-references

Topic	See
Access level behavior (user perspective)	User Guide
Access level configuration	Admin Guide
Blacklist configuration	Admin Guide
System settings	Admin Guide
Cloud DML error troubleshooting	Troubleshooting
Blank page troubleshooting	Troubleshooting

---

SQL Cor — Secure SQL Workbench for Creatio. Free and open source. License: MIT.

Document v1.0 · Applies to SQL Cor v1.0 · Last updated 1 June 2026