Skip to main content

Database Copilot Instructions

Document Version: 2.0.0 | Last Updated: May 2026
Scope: Database architecture, schema design, and data conventions
Technology: MSSQL Server 2022+ / PostgreSQL 15+, Multi-Tenant Architecture

Database Architecture Overview​

Axiom Genesis uses a two-database architecture for clear separation of concerns:

┌─────────────────────────────────────────────────────────────────┐
│                    MSSQL Server 2022+ / PostgreSQL 15+          │
├─────────────────────────────┬───────────────────────────────────┤
│       RADICS_CFG            │          RADICS_DATA              │
│   (Configuration Database)  │    (Transactional Database)       │
├─────────────────────────────┼───────────────────────────────────┤
│ • Tenant definitions        │ • Business transactions           │
│ • User accounts             │ • Application-specific data       │
│ • Role configurations       │ • Document records                │
│ • Object registries         │ • Workflow executions             │
│ • Page configurations       │ • File attachments                │
│ • Workflow definitions      │ • Integration data                │
│ • DataView configurations   │                                   │
│ • Navigation structures     │                                   │
│ • Connection strings        │                                   │
│ • Value sets (enums)        │                                   │
└─────────────────────────────┴───────────────────────────────────┘

Key Characteristics​

FeatureImplementation
Multi-TenancyLogical isolation via {prefix}_tenant_id columns
Primary KeysBIGINT IDENTITY (not INT)
Soft Deletes{prefix}_is_deleted flag (TINYINT: 0=active, 1=deleted)
Audit TrailSingle JSON column ({prefix}_audit_data)
Status CodesINT (1=Active, 0=Inactive, -1=Archived)
Column Prefixes3-letter table prefix (e.g., usr_, tnt_, nvc_)
JSON StorageNative json type (MSSQL 2022+ / PostgreSQL JSONB)
CachingRedis with entity-specific TTL

Naming Conventions​

Table Names​

CategoryPrefixExamples
Core Platformapp_app_users, app_tenants, app_roles
Applicationapt_apt_projects, apt_tasks, apt_files
Workflow Enginewft_wft_instances, wft_logs, wft_nodes

Column Prefixes (Required)​

Every column MUST use the table's 3-letter prefix:

TablePrefixExample Columns
app_tenantstnt_tnt_id, tnt_tenant_code, tnt_status
app_usersusr_usr_id, usr_email_id, usr_tenant_id
app_rolesrol_rol_id, rol_code, rol_permissions
app_object_registriesobj_obj_id, obj_code, obj_payload
app_object_profilesprf_prf_id, prf_code, prf_policy
app_page_configspgc_pgc_id, pgc_code, pgc_schema
app_dataview_configsdvc_dvc_id, dvc_code, dvc_config
app_navigation_configsnvc_nvc_id, nvc_code, nvc_config
app_workflow_configswfc_wfc_id, wfc_code, wfc_config
app_value_setvst_vst_id, vst_code, vst_values
app_connectionscon_con_id, con_code, con_connection_string
apt_projectsprj_prj_id, prj_code, prj_doc_config
apt_taskstsk_tsk_id, tsk_code, tsk_status
apt_filesfst_fst_id, fst_code, fst_blob_url
wft_instanceswfi_wfi_id, wfi_status, wfi_input_data
wft_logswfl_wfl_id, wfl_message, wfl_level

Column Standards​

Required Columns (All Tables)​

Every table MUST include these columns:

-- MSSQL 2022+
CREATE TABLE app_example (
  exm_id            BIGINT IDENTITY(1,1) PRIMARY KEY,  -- BIGINT, not INT
  exm_tenant_id     BIGINT NOT NULL,                   -- Multi-tenant isolation
  exm_is_deleted    TINYINT DEFAULT 0,                 -- Soft delete flag
  exm_audit_data    json NULL,                         -- Native JSON audit trail
  -- ... table-specific columns
);

-- PostgreSQL 15+
CREATE TABLE app_example (
  exm_id            BIGSERIAL PRIMARY KEY,             -- Auto-increment BIGINT
  exm_tenant_id     BIGINT NOT NULL,                   -- Multi-tenant isolation
  exm_is_deleted    SMALLINT DEFAULT 0,                -- Soft delete flag
  exm_audit_data    JSONB,                             -- JSONB for performance
  -- ... table-specific columns
);

Audit Data JSON Schema​

The {prefix}_audit_data column stores all audit information in a single JSON object:

{
  "created_by": "user_id",
  "created_at": "2026-05-22T10:30:00Z",
  "updated_by": "user_id",
  "updated_at": "2026-05-22T15:45:00Z",
  "version": 3,
  "change_history": [
    {
      "timestamp": "2026-05-22T12:00:00Z",
      "user": "user_id",
      "action": "update",
      "changes": { "field": ["old_value", "new_value"] }
    }
  ]
}

Status Code Standards​

ValueMeaningUsage
1ActiveDefault for new records
0InactiveDisabled but not deleted
-1ArchivedHistorical data, read-only

Multi-Tenant Isolation​

Critical Rule​

EVERY query MUST include tenant filtering:

// ✅ CORRECT - Always filter by tenant
const result = await db.request()
  .input('tenantId', sql.BigInt, tenantId)
  .query(`
    SELECT * FROM app_users 
    WHERE usr_tenant_id = @tenantId 
      AND usr_is_deleted = 0
  `);

// ❌ WRONG - Missing tenant filter (security vulnerability)
const result = await db.query('SELECT * FROM app_users WHERE usr_is_deleted = 0');

Index Pattern​

Always create filtered indexes including tenant_id:

-- MSSQL
CREATE INDEX idx_users_tenant_email 
ON app_users(usr_tenant_id, usr_email_id) 
WHERE usr_is_deleted = 0;

-- PostgreSQL
CREATE INDEX idx_users_tenant_email 
ON app_users(usr_tenant_id, usr_email_id) 
WHERE usr_is_deleted = 0;

JSON Column Patterns​

Using Native JSON Type​

MSSQL 2022+ and PostgreSQL 15+ support native JSON:

-- MSSQL 2022+ - Use native json type
CREATE TABLE app_page_configs (
  pgc_id          BIGINT IDENTITY(1,1) PRIMARY KEY,
  pgc_tenant_id   BIGINT NOT NULL,
  pgc_code        NVARCHAR(100) NOT NULL,
  pgc_config      json NOT NULL,           -- Native JSON type
  pgc_is_deleted  TINYINT DEFAULT 0,
  pgc_audit_data  json NULL
);

-- Querying JSON (MSSQL 2022+)
SELECT pgc_code, 
       JSON_VALUE(pgc_config, '$.pageType') AS page_type,
       JSON_QUERY(pgc_config, '$.fields') AS fields
FROM app_page_configs
WHERE pgc_tenant_id = @tenantId;

-- PostgreSQL - Use JSONB for performance
CREATE TABLE app_page_configs (
  pgc_id          BIGSERIAL PRIMARY KEY,
  pgc_tenant_id   BIGINT NOT NULL,
  pgc_code        VARCHAR(100) NOT NULL,
  pgc_config      JSONB NOT NULL,          -- JSONB type
  pgc_is_deleted  SMALLINT DEFAULT 0,
  pgc_audit_data  JSONB
);

-- Querying JSONB (PostgreSQL)
SELECT pgc_code, 
       pgc_config->>'pageType' AS page_type,
       pgc_config->'fields' AS fields
FROM app_page_configs
WHERE pgc_tenant_id = $1;

Common JSON Schemas​

Object Registry Payload (obj_payload)​

{
  "fields": [
    { "name": "usr_email_id", "type": "string", "required": true },
    { "name": "usr_first_name", "type": "string", "required": true }
  ],
  "relationships": [
    { "field": "usr_tenant_id", "references": "app_tenants.tnt_id" }
  ],
  "indexes": ["usr_email_id", "usr_tenant_id"]
}

Profile Policy (prf_policy)​

{
  "projection": { 
    "type": "exclude", 
    "fields": ["usr_password_hash", "usr_salt"] 
  },
  "masking": { 
    "usr_email_id": "email_mask",
    "usr_phone": "phone_mask"
  },
  "rowFilters": { 
    "usr_tenant_id": "@tenant_id" 
  }
}
{
  "features": {
    "showSidebar": true,
    "showBreadcrumbs": true,
    "allowDownload": false
  },
  "renderer": {
    "projectSlug": "knowledge-base",
    "defaultView": "tree"
  }
}

Query Patterns​

Parameterized Queries (Required)​

ALWAYS use parameterized queries - never concatenate values:

// ✅ CORRECT - Parameterized
const result = await db.request()
  .input('tenantId', sql.BigInt, tenantId)
  .input('email', sql.NVarChar, email)
  .query(`
    SELECT usr_id, usr_email_id, usr_first_name, usr_last_name
    FROM app_users
    WHERE usr_tenant_id = @tenantId
      AND usr_email_id = @email
      AND usr_is_deleted = 0
  `);

// ❌ WRONG - SQL injection vulnerability
const result = await db.query(
  `SELECT * FROM app_users WHERE usr_email_id = '${email}'`
);

Standard CRUD Operations​

// CREATE
async function createUser(tenantId, userData) {
  const auditData = JSON.stringify({
    created_by: userData.createdBy,
    created_at: new Date().toISOString(),
    version: 1
  });
  
  return await db.request()
    .input('tenantId', sql.BigInt, tenantId)
    .input('email', sql.NVarChar, userData.email)
    .input('firstName', sql.NVarChar, userData.firstName)
    .input('auditData', sql.NVarChar, auditData)
    .query(`
      INSERT INTO app_users (usr_tenant_id, usr_email_id, usr_first_name, usr_audit_data)
      OUTPUT INSERTED.*
      VALUES (@tenantId, @email, @firstName, @auditData)
    `);
}

// READ (with tenant isolation)
async function getUserById(tenantId, userId) {
  return await db.request()
    .input('tenantId', sql.BigInt, tenantId)
    .input('userId', sql.BigInt, userId)
    .query(`
      SELECT * FROM app_users
      WHERE usr_tenant_id = @tenantId
        AND usr_id = @userId
        AND usr_is_deleted = 0
    `);
}

// UPDATE (with audit trail)
async function updateUser(tenantId, userId, updates, updatedBy) {
  return await db.request()
    .input('tenantId', sql.BigInt, tenantId)
    .input('userId', sql.BigInt, userId)
    .input('firstName', sql.NVarChar, updates.firstName)
    .input('updatedBy', sql.NVarChar, updatedBy)
    .query(`
      UPDATE app_users
      SET usr_first_name = @firstName,
          usr_audit_data = JSON_MODIFY(
            ISNULL(usr_audit_data, '{}'),
            '$.updated_by', @updatedBy
          )
      WHERE usr_tenant_id = @tenantId
        AND usr_id = @userId
        AND usr_is_deleted = 0
    `);
}

// DELETE (soft delete)
async function deleteUser(tenantId, userId, deletedBy) {
  return await db.request()
    .input('tenantId', sql.BigInt, tenantId)
    .input('userId', sql.BigInt, userId)
    .input('deletedBy', sql.NVarChar, deletedBy)
    .query(`
      UPDATE app_users
      SET usr_is_deleted = 1,
          usr_audit_data = JSON_MODIFY(
            ISNULL(usr_audit_data, '{}'),
            '$.deleted_by', @deletedBy
          )
      WHERE usr_tenant_id = @tenantId
        AND usr_id = @userId
    `);
}

Table Reference​

RADICS_CFG Tables (35 total)​

CategoryTablePrefixPurpose
Tenant & Userapp_tenantstnt_Tenant/organization definitions
app_usersusr_User accounts
app_rolesrol_Role definitions
app_subscriptionssub_Subscription plans
app_connectionscon_External database connections
Object & Configapp_object_registriesobj_Entity metadata
app_object_profilesprf_Field-level security
app_value_setvst_Dropdown/enum values
app_applicationsapp_Application definitions
app_templatestpl_Reusable templates
app_config_versionscfv_Configuration versioning
app_patch_trackerpht_Database patch tracking
UI/UX Configapp_page_configspgc_Page/form layouts
app_dataview_configsdvc_Data grid configurations
app_navigation_configsnvc_Menu structure
app_dashboard_configsdsc_Dashboard layouts
app_scrumboard_configssbc_Kanban board configs
Workflow Engineapp_workflow_configswfc_Workflow definitions
app_workflow_schedulerswfs_Scheduled workflows
wft_templateswtt_Workflow templates
wft_instanceswfi_Running instances
wft_nodeswfn_Node definitions
wft_stepswfs_Execution steps
wft_node_stateswns_Node state tracking
wft_approvalswfa_Approval requests
wft_logswfl_Execution logs
wft_ai_usagewau_AI token usage
wft_agent_memorywam_AI agent memory
Collaborationapt_projectsprj_Project definitions
apt_taskstsk_Task management
apt_scb_cardsscb_Scrumboard cards
apt_messagesmsg_Messaging
apt_notesnot_Notes
apt_filesfst_File storage
apt_eventsevt_Calendar events

Database-Specific Notes​

MSSQL 2022+​

-- Native JSON type (not NVARCHAR(MAX))
CREATE TABLE example (
  config json NOT NULL
);

-- JSON_VALUE for scalar values
SELECT JSON_VALUE(config, '$.name') FROM example;

-- JSON_QUERY for objects/arrays
SELECT JSON_QUERY(config, '$.items') FROM example;

-- OPENJSON for parsing arrays
SELECT * FROM OPENJSON(@json, '$.items');

PostgreSQL 15+​

-- JSONB for performance (recommended)
CREATE TABLE example (
  config JSONB NOT NULL
);

-- Arrow operators for access
SELECT config->>'name' FROM example;      -- text
SELECT config->'items' FROM example;      -- jsonb

-- Containment queries (indexed)
SELECT * FROM example WHERE config @> '{"status": "active"}';

-- GIN index for JSONB
CREATE INDEX idx_config ON example USING GIN (config);

Common Gotchas​

  1. Always use BIGINT for IDs - INT is insufficient for scale
  2. Use native JSON type - Never NVARCHAR(MAX) for JSON storage
  3. Single audit column - {prefix}_audit_data contains all audit info
  4. Table name is singular - app_value_set not app_value_sets
  5. Correct prefixes: dvc_ (not dv_), nvc_ (not nav_), vst_ (not avs_)
  6. Always parameterize - Never concatenate SQL strings
  7. Always filter by tenant - Every query needs tenant_id filter
  8. Soft delete default - Use is_deleted = 0 in WHERE clauses

See Also​