Codex - Master Data Management¶

Port: 5010 Database: PostgreSQL Repository: hivematrix-codex Version: 1.0

Table of Contents¶

Overview
Architecture
Design Philosophy
Technology Stack
Data Model
Core Features
Company Management
Contact Management
Asset Tracking
Ticket Management
Billing Plans
User Preferences
External Integrations
PSA Systems
Datto RMM
Sync Jobs
API Reference
Company Endpoints
Contact Endpoints
Asset Endpoints
Ticket Endpoints
PSA Endpoints
Billing Plan Endpoints
Agent Endpoints
Public Endpoints
Database Schema
Core Models
PSA Models
Sync Models
Relationships
Sync Scripts
PSA Sync
Datto Sync
Account Number Management
Configuration
Environment Variables
PSA Configuration
Datto Configuration
Development
Running Locally
Database Initialization
Adding PSA Providers
Monitoring & Observability
Health Checks
Logging
Sync Job Monitoring
Troubleshooting
See Also

Overview¶

Codex is the central data repository for the entire HiveMatrix platform. It serves as the single source of truth for all company, contact, asset, and ticket information, integrating data from multiple PSA systems and RMM tools.

Design Philosophy¶

Single Source of Truth: - All services query Codex for master data - Eliminates data duplication across services - Centralized sync logic for external systems - Consistent data across entire platform

API-First Architecture: - Comprehensive REST APIs for all data types - Service-to-service authentication - Bulk endpoints for performance - Public endpoints for user preferences

Think of Codex as: - 📇 Master rolodex (companies & contacts) - 🖥️ Asset inventory system - 🎫 Ticket aggregation hub - 🔄 Sync orchestration service - 💰 Billing data provider

Architecture¶

Technology Stack¶

Framework: Flask 3.0.0
Database: PostgreSQL with SQLAlchemy ORM
PSA Integration: Pluggable provider system (Freshservice, Superops)
RMM Integration: Datto RMM API client
Sync Engine: Background threading with job tracking
Rate Limiting: Flask-Limiter (per-user)
Logging: Structured JSON logging with correlation IDs
API Documentation: Flasgger (OpenAPI/Swagger)
Health Checks: Custom HealthChecker library

Production Features¶

Codex includes enterprise-ready features:

Per-User Rate Limiting¶

Rate limits: 500 requests/day, 100 requests/hour
Applied per user (JWT subject), not IP
Suitable for high-traffic environments

Structured Logging¶

JSON-formatted logs with correlation IDs
Centralized logging to Helm
Configurable log levels

RFC 7807 Problem Details¶

Standardized error responses
Machine-readable error format

Background Sync Jobs¶

Non-blocking sync execution
Job status tracking in database
Automatic retry on failure
Progress monitoring

Bulk API Endpoints¶

/api/companies/bulk - All companies with nested data
Optimized for services like Ledger
Reduces API call overhead

Data Model¶

Codex manages several core entity types:

┌─────────────┐
│  Companies  │  Master company records
└──────┬──────┘
       │
       ├──────> Contacts (many-to-many)
       ├──────> Assets (one-to-many)
       ├──────> Locations (one-to-many)
       ├──────> Datto Site Links (one-to-many)
       └──────> Feature Overrides (one-to-many)

┌─────────────┐
│   Tickets   │  From PSA systems
└──────┬──────┘
       │
       └──────> Companies (via external_company_id)

┌─────────────┐
│    Agents   │  PSA technicians/agents
└──────┬──────┘
       │
       └──────> HiveMatrix Users (via email mapping)

┌─────────────┐
│Billing Plans│  Pricing templates
└──────┬──────┘
       │
       ├──────> Plan Features (one-to-many)
       └──────> Feature Options (one-to-many)

Key Relationships: - Companies ↔ Contacts: Many-to-many (one contact can work at multiple companies) - Companies → Assets: One-to-many (assets belong to one company) - Companies → Tickets: One-to-many (via PSA external_company_id) - Agents ↔ HiveMatrix Users: Mapped via email address

Core Features¶

Company Management¶

Purpose: Master database of client and vendor companies

Key Fields: - account_number (Primary Key): Unique identifier (e.g., "COMP-001") - external_id: PSA system company ID - external_source: Which PSA this came from (freshservice, superops) - name: Company name - psa_provider: Default PSA for this company - domains: JSON array of email domains - Contract information (start date, end date, term length) - Billing details (plan, managed users/devices/network) - Contact information (main phone, address)

Capabilities: - Create/Read/Update/Delete companies - Hierarchy support (parent/child companies) - Bulk operations for performance - PSA sync integration - Custom field mapping

Web UI: - /companies - Company list - /companies/<account_number> - Company detail page - /companies/add - Create new company - /companies/<account_number>/edit - Edit company

API Endpoints: - GET /api/companies - List all companies - GET /api/companies/bulk - Bulk export with nested data - GET /api/companies/<account_number> - Get specific company - POST /api/companies - Create company - PUT /api/companies/<account_number> - Update company - DELETE /api/companies/<account_number> - Delete company

Contact Management¶

Purpose: People associated with companies

Key Fields: - id (Primary Key): Auto-increment integer - external_id: PSA system contact ID - name: Full name - email: Email address (unique) - phone: Phone number - mobile: Mobile phone - job_title: Role/position - primary: Boolean (is this the primary contact?) - active: Boolean (is contact still active?)

Relationships: - Many-to-many with Companies (via contact_company_link table) - Many-to-many with Assets (via asset_contact_link table)

Capabilities: - Link contacts to multiple companies - Assign contacts to assets - PHI/CJIS filtering for sensitive data - Primary contact designation - PSA sync integration

Web UI: - /contacts - Contact list - /contacts/<id> - Contact detail - /contacts/add - Create contact - /contacts/<id>/edit - Edit contact

API Endpoints: - GET /api/contacts - List all contacts - GET /api/contacts/<id> - Get specific contact - POST /api/contacts - Create contact - PUT /api/contacts/<id> - Update contact - DELETE /api/contacts/<id> - Delete contact

Asset Tracking¶

Purpose: Equipment and software inventory

Key Fields: - id (Primary Key): Auto-increment integer - hostname: Device hostname - company_account_number: Foreign key to company - hardware_type: Server, workstation, laptop, etc. - operating_system: OS and version - datto_site_name: Datto RMM site association - last_logged_in_user: Current user - int_ip_address, ext_ip_address: Network info - online: Boolean (device status) - last_seen: Last check-in timestamp - patch_status: Update compliance - UDF fields (udf1-udf30): Custom Datto fields

Capabilities: - Track all company assets - Datto RMM integration - IP address management - Warranty tracking - Contact assignment - Custom field support (30 UDF fields)

Web UI: - /assets - Asset list - /assets/<id> - Asset detail - /assets/add - Create asset - /assets/<id>/edit - Edit asset

API Endpoints: - GET /api/assets - List all assets - GET /api/assets/<id> - Get specific asset - POST /api/assets - Create asset - PUT /api/assets/<id> - Update asset - DELETE /api/assets/<id> - Delete asset - GET /api/rmm/devices - List devices from RMM provider (vendor-agnostic) - GET /api/rmm/device/<device_id> - Get device details from RMM provider (vendor-agnostic)

Ticket Management¶

Purpose: Aggregate tickets from PSA systems for dashboard display

Key Fields: - id (Primary Key): Auto-increment integer - external_id: PSA ticket ID - external_source: Which PSA (freshservice, superops) - external_company_id: PSA company ID (links to Company.external_id) - subject: Ticket subject line - description: Ticket description (HTML) - status: Ticket status code - status_text: Human-readable status - priority: Priority level (1-4) - priority_text: "Urgent", "High", "Medium", "Low" - requester_id, requester_name: Customer who created ticket - responder_id, agent_name: Assigned technician - group_id: PSA group/team - created_at, updated_at: Timestamps - due_by: SLA due date - fr_due_by: First response SLA

Categorization: Tickets are categorized for Beacon dashboard: - Section 1: Open tickets (new, awaiting first response) - Section 2: Customer replied (awaiting agent response) - Section 3: Needs agent / overdue (SLA breached, critical priority) - Section 4: Other active (on hold, pending, waiting on third party)

Capabilities: - Sync from multiple PSA systems - Real-time categorization for dashboards - SLA tracking and alerting - Agent assignment tracking - Filtering by company, agent, status, priority

API Endpoints: - GET /api/tickets - List all tickets with filtering - GET /api/tickets/active - Get categorized active tickets (for Beacon) - GET /api/ticket/<ticket_id> - Get specific ticket details - POST /sync/tickets - Trigger ticket sync from PSA

Billing Plans¶

Purpose: Pricing templates for managed services

Key Fields: - id (Primary Key): Auto-increment integer - plan_name: Name (e.g., "Gold", "Silver", "Bronze") - term_length: Contract duration ("1-Year", "3-Year") - per_user_cost: Price per managed user - per_workstation_cost: Price per device - per_server_cost: Price per server - per_network_cost: Price per network device - managed_users, managed_workstations, etc.: Included quantities - total_cost: Calculated total price

Features System: - Plans can have multiple features (email, phone, backup, etc.) - Each feature has options (e.g., Email: "Microsoft 365", "Google Workspace") - Features can be required or optional - Per-user or per-company pricing

Capabilities: - Template-based pricing - Feature customization - Term-based pricing (1-year, 3-year) - Bulk import from CSV - Association with companies

Web UI: - /billing-plans - List all plans - /billing-plans/<id> - Plan details - /billing-plans/add - Create plan - /billing-plans/import - CSV import

API Endpoints: - GET /api/billing-plans - List all plans - GET /api/billing-plans/<id> - Get specific plan - POST /api/billing-plans - Create plan - PUT /api/billing-plans/<id> - Update plan - DELETE /api/billing-plans/<id> - Delete plan

User Preferences¶

Purpose: Store HiveMatrix user preferences (theme, home page)

Storage: agents table (represents HiveMatrix users)

Key Fields: - email: User email (matches Keycloak username) - name: Display name - theme_preference: "light" or "dark" - home_page_preference: Service slug ("codex", "beacon", etc.) - active: Boolean (synced from Keycloak)

Capabilities: - Theme persistence across sessions - Home page customization - Keycloak synchronization - Public API (no auth required for theme/home page lookup)

API Endpoints: - GET /api/public/user/theme?email=<email> - Get user theme - GET /api/public/user/home-page?email=<email> - Get preferred home page - PUT /api/my/settings - Update current user's settings (requires auth) - GET /agents - List all HiveMatrix users - POST /agents/sync - Sync users from Keycloak

External Integrations¶

PSA Systems¶

Codex integrates with Professional Services Automation (PSA) platforms:

Supported PSA Providers¶

1. Freshservice - Full CRUD operations - Company, contact, ticket, agent sync - Custom field mapping - Change request support

2. Superops - Company and ticket sync - Agent mapping - Ticket categorization

PSA Provider Architecture¶

Base Interface (app/psa/base.py):

class PSAProvider:
    def get_companies(self) -> List[Dict]:
        """Fetch all companies from PSA"""

    def get_contacts(self) -> List[Dict]:
        """Fetch all contacts from PSA"""

    def get_tickets(self, **filters) -> List[Dict]:
        """Fetch tickets with optional filtering"""

    def get_agents(self) -> List[Dict]:
        """Fetch all agents/technicians"""

Provider Implementations: - app/psa/freshservice.py - Freshservice integration - app/psa/superops.py - Superops integration

Adding New Providers: 1. Create app/psa/your_psa.py implementing PSAProvider 2. Add provider to instance/codex.conf:

[psa]
enabled_providers = freshservice,superops,your_psa

[your_psa]
api_key = your-api-key
domain = your-domain.com

3. Update app/psa/__init__.py to import new provider 4. Restart Codex

PSA Sync Process¶

Manual Sync:

cd hivematrix-codex
source pyenv/bin/activate

# Sync companies, contacts, and agents only (fast)
python sync_psa.py --provider freshservice --type base

# Sync tickets only (slow - can take hours)
python sync_psa.py --provider freshservice --type tickets

# Sync everything
python sync_psa.py --provider freshservice --type all

Automated Sync (Cron):

# Every 4 hours: base sync (companies, contacts, agents)
0 */4 * * * cd /path/to/hivematrix-codex && pyenv/bin/python sync_psa.py --provider freshservice --type base >> /var/log/codex_base_sync.log 2>&1

# Daily at 2am: ticket sync
0 2 * * * cd /path/to/hivematrix-codex && pyenv/bin/python sync_psa.py --provider freshservice --type tickets >> /var/log/codex_ticket_sync.log 2>&1

Via API (Web UI):

# Trigger base sync (companies, contacts, agents)
POST /sync/psa

# Trigger ticket sync
POST /sync/tickets

Field Mapping¶

Freshservice → Codex:

{
    'id': ticket['id'],  # external_id
    'subject': ticket['subject'],
    'description': ticket['description'],
    'status': ticket['status'],
    'priority': ticket['priority'],
    'requester_id': ticket['requester_id'],
    'responder_id': ticket['responder_id'],
    'group_id': ticket['group_id'],
    'created_at': ticket['created_at'],
    'updated_at': ticket['updated_at'],
    'due_by': ticket['due_by'],
    # ... additional fields
}

Custom Fields: Freshservice custom fields mapped to Codex company fields: - cf_plan_selected → plan_selected - cf_managed_users → managed_users - cf_contract_term → contract_term - etc.

RMM Integration (Vendor-Agnostic)¶

Codex provides vendor-agnostic RMM integration supporting multiple RMM providers.

Current Provider: Datto RMM Supported Providers: Datto, SuperOps (framework ready for additional providers)

API Endpoints: - GET /api/rmm/devices - List all devices (works with any RMM provider) - GET /api/rmm/device/<device_id> - Get device details (works with any RMM provider)

Datto Integration Features¶

Device Sync: - Automatic device discovery - Asset inventory updates - Online/offline status - Last seen timestamps - User-defined field (UDF) sync

Site Linking: - Map Datto sites to HiveMatrix companies - Account number synchronization - Bidirectional updates

Monitoring: - Device online status - Patch compliance - Last reboot times - Backup usage

Datto Sync Process¶

Manual Sync:

cd hivematrix-codex
source pyenv/bin/activate
python pull_datto.py

Automated Sync (Cron):

# Every 6 hours
0 */6 * * * cd /path/to/hivematrix-codex && pyenv/bin/python pull_datto.py >> /var/log/datto_sync.log 2>&1

Via API:

POST /sync/datto

Datto Configuration¶

In instance/codex.conf:

[datto]
api_key = your-datto-api-key
api_secret = your-datto-api-secret
api_url = https://your-instance.centrastage.net

Account Number Push:

# Push account numbers to Datto site UDFs
cd hivematrix-codex
source pyenv/bin/activate
python push_account_nums_to_datto.py

Sync Jobs¶

All sync operations are tracked in the database for monitoring.

Sync Job Model:

{
    'id': 'uuid',
    'script': 'psa' | 'tickets' | 'datto',
    'provider': 'freshservice' | 'superops' | 'datto',
    'sync_type': 'base' | 'tickets' | 'all',
    'status': 'running' | 'completed' | 'failed',
    'success': True | False,
    'started_at': '2025-11-22T10:00:00Z',
    'completed_at': '2025-11-22T10:15:00Z',
    'output': 'Last 1000 chars of stdout',
    'error': 'Last 1000 chars of stderr'
}

Job Lifecycle: 1. User triggers sync via API 2. Job created with status running 3. Background thread executes sync script 4. Job updated with completed or failed status 5. Output/error captured for review

Monitoring Jobs:

# View recent sync jobs
GET /admin/sync-jobs

# Get specific job status
GET /admin/sync-jobs/<job_id>

Timeouts: - PSA base sync: 10 minutes - Ticket sync: 2 hours (can process thousands of tickets) - Datto sync: 10 minutes

API Reference¶

Company Endpoints¶

`GET /api/companies`¶

List all companies.

Query Parameters: - include_assets (boolean): Include asset counts - include_contacts (boolean): Include contact counts

Response:

[
  {
    "account_number": "COMP-001",
    "name": "Example Corp",
    "external_id": 12345,
    "external_source": "freshservice",
    "psa_provider": "freshservice",
    "plan_selected": "Gold",
    "managed_users": "50",
    "managed_devices": "75",
    "contract_start_date": "2024-01-01",
    "contract_end_date": "2025-01-01",
    "asset_count": 75,
    "contact_count": 12
  }
]

`GET /api/companies/bulk`¶

Bulk export of all companies with nested data. Optimized for services like Ledger.

Query Parameters: - include_tickets (boolean): Include ticket data - year (integer): Filter tickets by year

Response:

{
  "companies": [
    {
      "account_number": "COMP-001",
      "name": "Example Corp",
      "assets": [...],
      "contacts": [...],
      "locations": [...],
      "tickets": [...]  // if include_tickets=true
    }
  ],
  "total_count": 150
}

Performance: - Single query for all data - Reduces API overhead - Suitable for monthly billing runs

`GET /api/companies/<account_number>`¶

Get specific company with full details.

Response:

{
  "account_number": "COMP-001",
  "name": "Example Corp",
  "external_id": 12345,
  "description": "Client description",
  "plan_selected": "Gold",
  "contract_term": "1-Year",
  "contract_start_date": "2024-01-01",
  "contract_end_date": "2025-01-01",
  "managed_users": "50",
  "managed_devices": "75",
  "assets": [...],
  "contacts": [...],
  "locations": [...]
}

`POST /api/companies`¶

Create new company.

Request Body:

{
  "account_number": "COMP-001",
  "name": "Example Corp",
  "description": "Client description",
  "plan_selected": "Gold",
  "managed_users": "50"
}

Response: 201 Created with company object

`PUT /api/companies/<account_number>`¶

Update existing company.

Request Body:

{
  "plan_selected": "Platinum",
  "managed_users": "100"
}

Response: 200 OK with updated company object

`DELETE /api/companies/<account_number>`¶

Delete company and all associated data (cascade delete).

Response: 204 No Content

Cascade Deletes: - All assets - All feature overrides - All locations - All Datto site links - Contact associations (not contacts themselves)

Contact Endpoints¶

`GET /api/contacts`¶

List all contacts.

Query Parameters: - company (string): Filter by company account number

Response:

[
  {
    "id": 123,
    "external_id": 45678,
    "name": "John Doe",
    "email": "john@example.com",
    "phone": "555-1234",
    "job_title": "IT Manager",
    "primary": true,
    "active": true,
    "companies": ["COMP-001", "COMP-002"]
  }
]

`POST /api/contacts`¶

Create new contact.

Request Body:

{
  "name": "John Doe",
  "email": "john@example.com",
  "phone": "555-1234",
  "job_title": "IT Manager",
  "companies": ["COMP-001"]
}

Response: 201 Created

Asset Endpoints¶

`GET /api/assets`¶

List all assets.

Query Parameters: - company (string): Filter by company account number - online (boolean): Filter by online status

Response:

[
  {
    "id": 456,
    "hostname": "WS-001",
    "company_account_number": "COMP-001",
    "hardware_type": "Workstation",
    "operating_system": "Windows 11 Pro",
    "online": true,
    "last_seen": "2025-11-22T10:30:00Z",
    "int_ip_address": "192.168.1.100"
  }
]

Ticket Endpoints¶

`GET /api/tickets`¶

List all tickets with filtering.

Query Parameters: - company_id (integer): PSA company ID - status (integer): Filter by status code - priority (integer): Filter by priority - agent_id (integer): Filter by assigned agent - limit (integer): Maximum results (default: 100)

Response:

[
  {
    "id": 789,
    "external_id": 12345,
    "subject": "Password reset request",
    "status": 2,
    "status_text": "Open",
    "priority": 2,
    "priority_text": "Medium",
    "requester_name": "Jane Smith",
    "agent_name": "John Doe",
    "created_at": "2025-11-22T09:00:00Z"
  }
]

`GET /api/tickets/active`¶

Get categorized active tickets for Beacon dashboard.

Response:

{
  "section1": [...],  // Open tickets
  "section2": [...],  // Customer replied
  "section3": [...],  // Needs agent / overdue
  "section4": [...],  // Other active
  "last_sync_time": "2025-11-22T10:30:00Z"
}

Categorization Logic: - Section 1: Status = Open, no SLA issues - Section 2: Customer has replied, awaiting agent - Section 3: SLA critical/breached OR urgent priority - Section 4: All other active (on hold, pending, etc.)

PSA Endpoints¶

`GET /api/psa/config`¶

Get PSA configuration for URL templates.

Response:

{
  "default_provider": "freshservice",
  "providers": {
    "freshservice": {
      "ticket_url_template": "https://domain.freshservice.com/helpdesk/tickets/{ticket_id}",
      "domain": "domain.freshservice.com"
    }
  }
}

Usage: - Beacon uses this to construct ticket links - Other services can determine which PSA is active

`GET /api/psa/agents`¶

List all PSA agents (technicians).

Response:

[
  {
    "id": 123,
    "external_id": 19000234567,
    "name": "John Doe",
    "email": "john@msp.com",
    "active": true
  }
]

Usage: - Beacon uses this for agent filter dropdown - Maps external_id to display name

Billing Plan Endpoints¶

`GET /api/billing-plans`¶

List all billing plans.

Response:

[
  {
    "id": 1,
    "plan_name": "Gold",
    "term_length": "1-Year",
    "per_user_cost": 100.00,
    "per_workstation_cost": 50.00,
    "managed_users": 50,
    "total_cost": 8750.00,
    "features": [...]
  }
]

`POST /api/billing-plans`¶

Create new billing plan.

Request Body:

{
  "plan_name": "Platinum",
  "term_length": "3-Year",
  "per_user_cost": 85.00,
  "per_workstation_cost": 45.00,
  "managed_users": 100
}

Response: 201 Created

Agent Endpoints¶

`GET /agents`¶

List all HiveMatrix users (agents).

Response:

[
  {
    "id": 1,
    "name": "Admin User",
    "email": "admin@hivematrix.local",
    "theme_preference": "dark",
    "home_page_preference": "codex",
    "active": true
  }
]

`POST /agents/sync`¶

Sync agents from Keycloak.

Process: 1. Fetches all users from Keycloak 2. Creates or updates agents in Codex 3. Sets active flag based on Keycloak status

Response:

{
  "success": true,
  "synced": 15,
  "created": 2,
  "updated": 13
}

Usage: - Run after adding new users to Keycloak - Enables theme/home page preferences - Required before users can customize settings

Public Endpoints¶

These endpoints don't require authentication (used by Nexus for user preferences).

`GET /api/public/user/theme`¶

Get user's theme preference.

Query Parameters: - email (required): User email address

Response:

{
  "theme": "dark"
}

Default: Returns "light" if user not found

`GET /api/public/user/home-page`¶

Get user's preferred home page.

Query Parameters: - email (required): User email address

Response:

{
  "home_page": "codex"
}

Default: Returns null if user not found (Nexus uses fallback logic)

`PUT /api/my/settings`¶

Update current user's settings (requires auth).

Request Body:

{
  "theme_preference": "dark",
  "home_page_preference": "beacon"
}

Response: 200 OK

Process: 1. Extracts user email from JWT 2. Finds or creates agent record 3. Updates preferences 4. Returns success

`GET /health`¶

Comprehensive health check.

Response (200 OK):

{
  "service": "codex",
  "status": "healthy",
  "checks": {
    "database": {
      "status": "healthy",
      "connection_time_ms": 5
    },
    "disk": {
      "status": "healthy",
      "usage_percent": 45.67
    },
    "dependencies": {
      "core": {"status": "healthy"},
      "helm": {"status": "healthy"}
    }
  }
}

`GET /docs`¶

OpenAPI/Swagger documentation.

Access: https://localhost/codex/docs

Database Schema¶

Core Models¶

Company¶

CREATE TABLE companies (
    account_number VARCHAR(50) PRIMARY KEY,
    external_id BIGINT UNIQUE,
    external_source VARCHAR(50) DEFAULT 'freshservice',
    psa_provider VARCHAR(50) DEFAULT 'freshservice',
    name VARCHAR(150) NOT NULL,
    description TEXT,
    plan_selected VARCHAR(100),
    managed_users VARCHAR(100),
    managed_devices VARCHAR(100),
    contract_start_date VARCHAR(100),
    contract_end_date VARCHAR(100),
    -- ... many more fields
);

Contact¶

CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    external_id BIGINT,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) UNIQUE,
    phone VARCHAR(50),
    job_title VARCHAR(150),
    primary BOOLEAN DEFAULT FALSE,
    active BOOLEAN DEFAULT TRUE
);

Asset¶

CREATE TABLE assets (
    id SERIAL PRIMARY KEY,
    hostname VARCHAR(150) NOT NULL,
    company_account_number VARCHAR(50) REFERENCES companies(account_number),
    hardware_type VARCHAR(100),
    operating_system VARCHAR(100),
    int_ip_address VARCHAR(50),
    online BOOLEAN,
    last_seen VARCHAR(50),
    -- ... UDF fields udf1-udf30
);

PSA Models¶

TicketDetail¶

CREATE TABLE ticket_details (
    id SERIAL PRIMARY KEY,
    external_id BIGINT,
    external_source VARCHAR(50),
    external_company_id BIGINT,
    subject VARCHAR(255),
    description TEXT,
    status INTEGER,
    status_text VARCHAR(50),
    priority INTEGER,
    priority_text VARCHAR(50),
    requester_id BIGINT,
    requester_name VARCHAR(150),
    responder_id BIGINT,
    agent_name VARCHAR(150),
    group_id BIGINT,
    created_at VARCHAR(100),
    updated_at VARCHAR(100),
    due_by VARCHAR(100),
    fr_due_by VARCHAR(100)
);

PSAAgent¶

CREATE TABLE psa_agents (
    id SERIAL PRIMARY KEY,
    external_id BIGINT,
    name VARCHAR(150),
    email VARCHAR(150),
    active BOOLEAN DEFAULT TRUE
);

Sync Models¶

SyncJob¶

CREATE TABLE sync_jobs (
    id VARCHAR(36) PRIMARY KEY,  -- UUID
    script VARCHAR(50) NOT NULL,  -- 'psa', 'tickets', 'datto'
    provider VARCHAR(50),  -- 'freshservice', 'superops', 'datto'
    sync_type VARCHAR(50),  -- 'base', 'tickets', 'all'
    status VARCHAR(20) NOT NULL,  -- 'running', 'completed', 'failed'
    success BOOLEAN,
    started_at VARCHAR(100),
    completed_at VARCHAR(100),
    output TEXT,
    error TEXT
);

Relationships¶

Many-to-Many:

-- Contacts can belong to multiple companies
CREATE TABLE contact_company_link (
    contact_id INTEGER REFERENCES contacts(id),
    company_account_number VARCHAR(50) REFERENCES companies(account_number),
    PRIMARY KEY (contact_id, company_account_number)
);

-- Assets can be assigned to multiple contacts
CREATE TABLE asset_contact_link (
    asset_id INTEGER REFERENCES assets(id),
    contact_id INTEGER REFERENCES contacts(id),
    PRIMARY KEY (asset_id, contact_id)
);

One-to-Many: - Company → Assets (via company_account_number) - Company → Locations - Company → Feature Overrides - Company → Datto Site Links

Sync Scripts¶

PSA Sync¶

Script: sync_psa.py

Purpose: Synchronize companies, contacts, tickets, and agents from PSA systems

Usage:

python sync_psa.py --provider <provider> --type <type>

Parameters: - --provider: PSA provider (freshservice, superops) - --type: What to sync - base: Companies, contacts, agents only (fast, ~2-5 minutes) - tickets: Tickets only (slow, can take 2+ hours for thousands of tickets) - all: Everything (base + tickets)

Process:

1. Base Sync:

# Fetch companies from PSA
companies = psa_client.get_companies()

# Update or create in database
for company_data in companies:
    company = Company.query.filter_by(external_id=company_data['id']).first()
    if not company:
        company = Company(account_number=generate_account_number())
    update_company_fields(company, company_data)
    db.session.add(company)

db.session.commit()

2. Ticket Sync:

# Fetch tickets with pagination
offset = 0
while True:
    tickets = psa_client.get_tickets(limit=100, offset=offset)
    if not tickets:
        break

    for ticket_data in tickets:
        ticket = TicketDetail.query.filter_by(external_id=ticket_data['id']).first()
        if not ticket:
            ticket = TicketDetail()
        update_ticket_fields(ticket, ticket_data)
        db.session.add(ticket)

    db.session.commit()
    offset += 100

Performance: - Base sync: 500-1000 records in 2-5 minutes - Ticket sync: 10,000+ tickets can take 2+ hours - Uses pagination to avoid memory issues - Commits in batches for performance

Datto Sync¶

Script: pull_datto.py

Purpose: Sync devices from Datto RMM

Usage:

python pull_datto.py

Process:

# Authenticate with Datto API
datto_client = DattoClient(api_key, api_secret, api_url)

# Get all sites
sites = datto_client.get_sites()

for site in sites:
    # Get devices for this site
    devices = datto_client.get_devices(site['uid'])

    for device_data in devices:
        # Match to company via account number in UDF
        company = find_company_by_datto_udf(device_data)

        # Update or create asset
        asset = Asset.query.filter_by(hostname=device_data['hostname']).first()
        if not asset:
            asset = Asset(company_account_number=company.account_number)

        update_asset_from_datto(asset, device_data)
        db.session.add(asset)

    db.session.commit()

Field Mapping: - hostname → hostname - operatingSystem → operating_system - lastLoggedInUser → last_logged_in_user - extIpAddress → ext_ip_address - intIpAddress → int_ip_address - online → online - lastSeen → last_seen - UDFs 1-30 → udf1-udf30

Account Number Management¶

Script: create_account_numbers.py

Purpose: Generate account numbers for companies that don't have them

Usage:

python create_account_numbers.py

Process:

# Find companies without account numbers
companies = Company.query.filter_by(account_number=None).all()

for company in companies:
    # Generate sequential number
    max_num = get_max_account_number()
    new_number = f"COMP-{max_num + 1:04d}"

    company.account_number = new_number
    db.session.add(company)

db.session.commit()

Script: push_account_nums_to_datto.py

Purpose: Push HiveMatrix account numbers to Datto site UDFs

Usage:

python push_account_nums_to_datto.py

Process:

# Get all companies with Datto site links
companies = Company.query.filter(Company.datto_site_links.any()).all()

for company in companies:
    for site_link in company.datto_site_links:
        # Update Datto site UDF with account number
        datto_client.update_site_udf(
            site_uid=site_link.site_uid,
            udf_name='Account Number',
            value=company.account_number
        )

Benefits: - Enables bidirectional lookup (Codex ↔ Datto) - Asset sync automatically maps to correct company - Supports multi-site companies

Configuration¶

Environment Variables¶

In .flaskenv (auto-generated):

FLASK_APP=run.py
FLASK_ENV=production
SECRET_KEY=<generated>
SERVICE_NAME=codex

# Service URLs
CORE_SERVICE_URL=http://localhost:5000
HELM_SERVICE_URL=http://localhost:5004

# Logging
LOG_LEVEL=INFO
ENABLE_JSON_LOGGING=true

PSA Configuration¶

In instance/codex.conf:

[psa]
default_provider = freshservice
enabled_providers = freshservice,superops

[freshservice]
domain = your-domain.freshservice.com
api_key = your-api-key

[superops]
domain = your-domain.superops.ai
api_key = your-api-key

Getting API Keys:

Freshservice: 1. Login to Freshservice 2. Profile → Settings → API Key 3. Copy API key

Superops: 1. Login to Superops 2. Settings → Integrations → API 3. Generate API key

Datto Configuration¶

In instance/codex.conf:

[datto]
api_key = your-api-key
api_secret = your-api-secret
api_url = https://your-instance.centrastage.net

Getting API Credentials: 1. Login to Datto RMM 2. Admin → API Credentials 3. Create new API user 4. Copy key and secret

Development¶

Running Locally¶

Prerequisites: - Python 3.9+ - PostgreSQL 12+ - Core service (port 5000) - Helm service (port 5004)

Setup:

cd hivematrix-codex

# Create virtual environment
python3 -m venv pyenv
source pyenv/bin/activate

# Install dependencies
pip install -r requirements.txt

# Initialize database
python init_db.py

# Configure environment
cd ../hivematrix-helm
python config_manager.py write-dotenv codex
cd ../hivematrix-codex

# Run Codex
python run.py

Expected Output:

Loaded 8 services from services.json
Database connected successfully
 * Running on http://127.0.0.1:5010

Access Points: - Dashboard: http://localhost:5010/ - API: http://localhost:5010/api/companies - Health: http://localhost:5010/health - Docs: http://localhost:5010/docs

Database Initialization¶

Script: init_db.py

Purpose: Interactive database setup and schema updates

Features: - Creates all tables - Prompts for database credentials - Tests connection before saving - Handles schema updates (adds new columns without data loss) - No migration files needed

Usage:

python init_db.py

Prompts:

Enter database host [localhost]:
Enter database port [5432]:
Enter database name: codex_db
Enter database user: codex_user
Enter database password: ********

Process: 1. Tests database connection 2. Creates all tables if they don't exist 3. Adds new columns to existing tables 4. Preserves all existing data 5. Saves config to instance/codex.conf

Adding PSA Providers¶

Example: Adding ConnectWise:

1. Create Provider Class:

# app/psa/connectwise.py
from .base import PSAProvider

class ConnectWiseProvider(PSAProvider):
    def __init__(self, config):
        self.company_id = config.get('company_id')
        self.public_key = config.get('public_key')
        self.private_key = config.get('private_key')
        self.api_url = config.get('api_url')

    def get_companies(self):
        # Implement ConnectWise company API call
        response = requests.get(
            f"{self.api_url}/v4_6_release/apis/3.0/company/companies",
            auth=self._get_auth()
        )
        return response.json()

    def get_tickets(self, **filters):
        # Implement ConnectWise ticket API call
        # ...

2. Update Configuration:

# instance/codex.conf
[psa]
enabled_providers = freshservice,superops,connectwise
default_provider = connectwise

[connectwise]
company_id = your-company-id
public_key = your-public-key
private_key = your-private-key
api_url = https://api-na.myconnectwise.net

3. Register Provider:

# app/psa/__init__.py
from .connectwise import ConnectWiseProvider

PROVIDERS = {
    'freshservice': FreshserviceProvider,
    'superops': SuperopsProvider,
    'connectwise': ConnectWiseProvider
}

4. Test:

python sync_psa.py --provider connectwise --type base

Monitoring & Observability¶

Health Checks¶

Endpoint: GET /health

Components Checked: 1. Database connectivity 2. Disk space 3. Core service availability 4. Helm service availability

Response:

{
  "service": "codex",
  "status": "healthy",
  "checks": {
    "database": {
      "status": "healthy",
      "connection_time_ms": 5,
      "total_connections": 10
    },
    "disk": {
      "status": "healthy",
      "usage_percent": 45.67
    },
    "dependencies": {
      "core": {"status": "healthy"},
      "helm": {"status": "healthy"}
    }
  }
}

Logging¶

Log Format:

{
  "timestamp": "2025-11-22T10:30:00.123Z",
  "level": "INFO",
  "service": "codex",
  "correlation_id": "a1b2c3d4",
  "message": "PSA sync completed",
  "provider": "freshservice",
  "companies_synced": 150,
  "duration_seconds": 45
}

View Logs:

cd hivematrix-helm
python logs_cli.py codex --tail 50

Sync Job Monitoring¶

Web UI: /admin/sync-jobs

Features: - View recent sync jobs - Filter by script type - View output/error messages - Trigger new syncs

API:

# List recent jobs
GET /admin/sync-jobs

# Get specific job
GET /admin/sync-jobs/<job_id>

Troubleshooting¶

Database Connection Errors¶

Symptoms: - Cannot connect to database - "Connection refused" errors

Solutions:

# Check PostgreSQL running
sudo systemctl status postgresql

# Test connection manually
psql -h localhost -U codex_user -d codex_db

# Verify credentials in config
cat instance/codex.conf

# Reinitialize if needed
python init_db.py

PSA Sync Failures¶

Symptoms: - Sync job status: failed - No data updated

Check:

# View sync job output
GET /admin/sync-jobs/<job_id>

# Common issues:
# - Invalid API key
# - Rate limit exceeded
# - Network timeout

# Test API manually
curl -H "Authorization: Basic <api-key>" \
  https://domain.freshservice.com/api/v2/companies

Missing Ticket Data¶

Symptoms: - Beacon dashboard empty - /api/tickets/active returns no tickets

Solutions:

# Check last ticket sync
GET /admin/sync-jobs?script=tickets

# Trigger ticket sync
POST /sync/tickets

# Check ticket count in database
psql codex_db -c "SELECT COUNT(*) FROM ticket_details;"

# Verify PSA has tickets
# Check PSA web UI for ticket count

Agent Sync Issues¶

Symptoms: - Agent filter dropdown empty in Beacon - Settings page shows "User not found"

Solutions:

# Sync agents from Keycloak
POST /agents/sync

# Verify agents exist
GET /agents

# Check Keycloak has users
# Login to Keycloak admin console
# Verify users exist in realm

Codex - Master Data Management¶

Table of Contents¶

Overview¶

Design Philosophy¶

Architecture¶

Technology Stack¶

Production Features¶

Per-User Rate Limiting¶

Structured Logging¶

RFC 7807 Problem Details¶

Background Sync Jobs¶

Bulk API Endpoints¶

Data Model¶

Core Features¶

Company Management¶

Contact Management¶

Asset Tracking¶

Ticket Management¶

Billing Plans¶

User Preferences¶

External Integrations¶

PSA Systems¶

Supported PSA Providers¶

PSA Provider Architecture¶

PSA Sync Process¶

Field Mapping¶

RMM Integration (Vendor-Agnostic)¶

Datto Integration Features¶

Datto Sync Process¶

Datto Configuration¶

Sync Jobs¶

API Reference¶

Company Endpoints¶

GET /api/companies¶

GET /api/companies/bulk¶

GET /api/companies/<account_number>¶

POST /api/companies¶

PUT /api/companies/<account_number>¶

DELETE /api/companies/<account_number>¶

Contact Endpoints¶

GET /api/contacts¶

POST /api/contacts¶

Asset Endpoints¶

GET /api/assets¶

Ticket Endpoints¶

GET /api/tickets¶

GET /api/tickets/active¶

PSA Endpoints¶

GET /api/psa/config¶

GET /api/psa/agents¶

Billing Plan Endpoints¶

GET /api/billing-plans¶

POST /api/billing-plans¶

Agent Endpoints¶

GET /agents¶

POST /agents/sync¶

Public Endpoints¶

GET /api/public/user/theme¶

GET /api/public/user/home-page¶

PUT /api/my/settings¶

GET /health¶

GET /docs¶

Database Schema¶

Core Models¶

Company¶

Contact¶

Asset¶

PSA Models¶

TicketDetail¶

PSAAgent¶

Sync Models¶

SyncJob¶

Relationships¶

Sync Scripts¶

PSA Sync¶

Datto Sync¶

Account Number Management¶

Configuration¶

Environment Variables¶

PSA Configuration¶

`GET /api/companies`¶

`GET /api/companies/bulk`¶

`GET /api/companies/<account_number>`¶

`POST /api/companies`¶

`PUT /api/companies/<account_number>`¶

`DELETE /api/companies/<account_number>`¶

`GET /api/contacts`¶

`POST /api/contacts`¶

`GET /api/assets`¶

`GET /api/tickets`¶

`GET /api/tickets/active`¶

`GET /api/psa/config`¶

`GET /api/psa/agents`¶

`GET /api/billing-plans`¶

`POST /api/billing-plans`¶

`GET /agents`¶

`POST /agents/sync`¶

`GET /api/public/user/theme`¶

`GET /api/public/user/home-page`¶

`PUT /api/my/settings`¶

`GET /health`¶

`GET /docs`¶