# DATABASE_STANDARDIZATION.md
> **Version**: 1.0 (2026-07-03)
> **Classification**: NEED_TO_KNOW — This file NEVER leaves the local workstation
> **Location**: `Y:\documents\DATABASE_STANDARDIZATION.md` (LOCAL ONLY per OPS-09)
> **Cross-references**: [ENGINE_STANDARDIZATION.md](ENGINE_STANDARDIZATION.md), [SCHEMA_MAP.md](SCHEMA_MAP.md), [PORT_STANDARDIZATION.md](PORT_STANDARDIZATION.md), [WIREGUARD_MAP.md](WIREGUARD_MAP.md)

---

## Database Inventory

| Database | Node | IP:Port | MySQL Bind | Tunnel Access | Purpose |
|----------|------|---------|:----------:|:-------------:|---------|
| `engine` | Engine (669427) | 127.0.0.1:3306 | localhost only | forge (172.28.50.1) via C++ API | Payments, fulfillment, trust scoring, tool audit, node telemetry |
| `store` | Origin (558201) | 127.0.0.1:3306 | localhost only | lantern (172.28.41.x) via PHP | E-commerce: products, orders, customers, marketing, storefronts |
| `shared_engine` | Engine (669427) | 172.28.50.1:3306 | Forge tunnel | forge (MIGRATION NEEDED) | Tool execution logs — **SHOULD MOVE TO Engine DB** |

> **CRITICAL MISALIGNMENT**: `shared_engine` exists on Origin (558201), but per OPS-03, ALL tool execution logs should go to Engine DB (669427). The `shared_engine` DB on Origin is the old architecture where Origin was the aggregation point. Per the approved OPS-03 change, Origin should be **kept in the dark** on operational tooling data.
>
> **Fix**: Migrate `shared_engine.sec_tool_runs` to `engine.sec_tool_runs` on 669427. Update log_to_db.sh to target Engine via forge tunnel.

---

## Database Architecture (Target State)

```
ENGINE DB (669427) — MySQL 127.0.0.1:3306

  engine.artifacts          — Tool execution artifacts
  engine.payments           — Payment processing
  engine.payment_methods    — Stripe/XMR/USDT/USDC methods
  engine.email_queue        — DKIM-signed email dispatch
  engine.fulfillment        — Canada Post fulfillment
  engine.canada_post_tracking
  engine.sessions           — Trust-scored sessions
  engine.strikes            — IP/fingerprint strikes
  engine.ip_reputation      — IP reputation scoring
  engine.node_health        — Fleet node telemetry
  engine.logs               — Partitioned general logs
  engine.security_events    — Security event log
  engine.waf_rules          — WAF rule management
  engine.ai_inferences      — Ollama/Mistral inference log
  engine.file_integrity     — File integrity baselines

  ── NEW TABLES (OPS-03 tunnel-per-purpose logging) ──
  engine.sec_tool_runs      — Tool execution audit (forge tunnel)
  engine.sec_ssh_sessions   — SSH session audit (wg7 tunnel)
  engine.sec_vault_calls    — Vault key pull audit (compass tunnel)
  engine.sec_dashboard_ops  — Dashboard action audit (lantern)
  engine.sec_payment_events — Payment event audit (rain tunnel)

  ── SCLS TABLES (Deployed 2026-07-04) ──
  engine.sec_audit_log        — Encrypted tool audit (AES-256-CBC, partitioned monthly)
  engine.sec_audit_decryptions — Decryption access audit trail
  engine.sec_audit_rate_limits — Per-admin decryption rate limiting
  engine.sec_tool_registry    — Tool catalog (60+ tools seeded)

  engine.unified_orders     — Cross-store order view
  engine.unified_customers  — Cross-store customer view
  engine.unified_inventory  — Cross-store inventory
  engine.unified_transactions
  engine.unified_audit_log
  engine.node_store_map     — NODE_ID to store mapping
  engine.sync_queue         — Cross-node sync queue
  engine.unified_notifications

  TOTAL: 32 tables (20 existing + 5 sec_* + 4 sec_audit_* + 3 unified)

  ACCESS PATHS:
  - C++ Engine daemon (localhost) → engine_admin user
  - Bash tools via forge → C++ API → engine_write user
  - Dashboard via lantern → C++ API → engine_read user
  - MySQL CLI (local only) → root

  NEVER: Direct MySQL access from any remote node
  ALWAYS: Through C++ API (HMAC-authenticated, tunnel-isolated)
```

```
STORE DB (558201) — MySQL 127.0.0.1:3306

  store.admin_users         — Dashboard admin accounts
  store.products            — Product catalog
  store.product_variants    — Sizes, colors, pricing
  store.categories          — Product categories
  store.orders              — Customer orders
  store.order_items         — Line items per order
  store.payments            — Store-side payment records
  store.users               — Customer accounts
  store.stores              — Multi-store configs
  store.inventory           — Stock levels per product/store
  store.inventory_movements — Stock change audit trail
  store.email_queue         — Store-side email queue
  store.email_log           — Email delivery log
  store.blacklist           — IP blacklist
  store.visitors            — Visitor tracking
  store.security_events     — Store-side security events
  store.honeypot_hits       — Deception trap log
  store.deception_tokens    — Canary tokens
  store.device_bindings     — Customer device fingerprints
  store.crypto_transactions — Crypto payment records
  store.fulfillment_stages  — Fulfillment state machine
  store.merkle_anchors      — Tamper-proof audit anchors
  store.marketing_campaigns — Ad campaign management
  store.nodes               — Fleet node registry
  store.fleet_node_meta     — Node metadata
  store.node_commands       — Node command dispatch
  ... (+52 more tables — see SCHEMA_MAP.md for full list)

  TOTAL: 77 base tables + 6 views + 8 unified tables

  ACCESS PATHS:
  - PHP Dashboard (localhost) → ops365 user
  - img-pipeline.sh (localhost) → store user
  - mailctl (localhost) → agent user
  - direct-ip-detect.sh (MIGRATE to Engine) → tooling user

  CRITICAL: Store DB has NO direct access from Engine
  Data flows Origin → lantern → Engine (not reverse)
```

---

## MySQL Users and Grants

### Engine DB (669427)

| User | Host | Database | Permissions | Purpose |
|------|------|----------|:-----------:|---------|
| `engine_admin` | localhost | engine.* | ALL | C++ daemon full access |
| `engine_admin` | 10.69.69.6 | engine.* | ALL | WG self-access (backup) |
| `engine_read` | localhost | engine.* | SELECT | Read-only queries |
| `engine_read` | 10.69.69.% | engine.* | SELECT | Remote read via WG |
| `engine_write` | localhost | engine.* | SELECT, INSERT, UPDATE | Daemon write ops |
| `engine_write` | 10.69.69.% | engine.* | SELECT, INSERT, UPDATE | Remote write via WG |
| `scls_ingest` | 10.69.69.% | engine.sec_audit_log | INSERT | SCLS audit writes via forge |
| `scls_ingest` | 10.69.69.% | engine.sec_tool_registry | SELECT | Tool registry lookups |

> Per OPS-03, no remote node should connect directly to Engine MySQL (3306). All access goes through the C++ API on port 8301. The `10.69.69.%` grants exist as fallback only.

### Store DB (558201)

| User | Host | Database | Permissions | Purpose |
|------|------|----------|:-----------:|---------|
| `ops365` | localhost | s3rv1c3_admin.* | ALL | Dashboard PHP |
| `ops365` | 127.0.0.1 | s3rv1c3_admin.* | ALL | Dashboard PHP (IPv4) |
| `tooling` | 10.69.69.% | shared_engine.* | INSERT, SELECT | Tool log writes — **DECOMMISSION** |
| `agent` | localhost | store.* | SELECT | mailctl read-only |
| `store` | localhost | store.* | ALL | img-pipeline |

> **WARNING**: The `tooling` user on Origin accesses `shared_engine`. This is the OLD architecture. Per OPS-03, this user and database should be decommissioned on Origin. Tool logs go to Engine DB via forge tunnel + C++ API.

---

## Tool to Database Mapping

### Current State (MISALIGNED)

| Tool | DB Target | Host | DB Name | User | Tunnel | Status |
|------|-----------|------|---------|------|:------:|:------:|
| log_to_db.sh (f6a9) | Engine | 172.28.50.1 | shared_engine | tooling | forge | WRONG |
| direct-ip-detect.sh | Engine | 172.28.50.1 | shared_engine | tooling | forge | WRONG |
| telemetry-cron.sh | Engine | 172.28.50.1 | shared_engine | tooling | forge | WRONG |
| mailctl | DNS? | 10.69.69.5 | store | agent | wg7 | REVIEW |
| img-pipeline.sh | Local | localhost | store | store | none | OK |
| C++ Engine | Local | 127.0.0.1 | engine | engine_admin | none | OK |
| Dashboard PHP | Local | localhost | store + engine (via API) | ops365 | none | OK |

### Target State (ALIGNED)

| Tool | DB Target | Via | Tunnel | Log Category |
|------|-----------|-----|:------:|:------------:|
| log_to_db.sh (f6a9) | Engine API | se_curl_post 172.28.50.1:8301 | forge | sec_tool_runs |
| direct-ip-detect.sh | Engine API | se_curl_post 172.28.50.1:8301 | forge | sec_tool_runs |
| telemetry-cron.sh | Engine API | se_curl_post 172.28.50.1:8301 | forge | sec_tool_runs |
| hello.sh / goodbye | Engine API | se_curl_post 172.28.50.1:8301 | forge | sec_ssh_sessions |
| decrypt-run.sh | Vault API | compass tunnel | compass | sec_vault_calls |
| mailctl | Local only | localhost:3306 | none | — |
| img-pipeline.sh | Local only | localhost:3306 | none | — |
| C++ Engine | Local only | 127.0.0.1:3306 | none | — |
| Dashboard PHP | Engine API | lantern 172.28.41.2:8301 | lantern | sec_dashboard_ops |
| audit_logger.sh (SCLS) | Engine API | se_curl_post 172.28.50.1:8301 | forge | sec_audit_log |

> **mailctl** currently targets `10.69.69.5` (DNS node?) for DB `store`. This needs investigation. The `store` DB is on Origin (558201 = 10.69.69.2). mailctl should run on Origin only or be updated.

---

## New sec_* Table Schemas (OPS-03)

```sql
-- sec_tool_runs: Tool execution audit (forge tunnel)
CREATE TABLE IF NOT EXISTS sec_tool_runs (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id         VARCHAR(6) NOT NULL,
    tool_id         VARCHAR(8) NOT NULL,
    tool_function   VARCHAR(64) NOT NULL,
    exec_id         VARCHAR(32) NOT NULL,
    status          ENUM('ok','error','timeout','skipped') NOT NULL,
    exit_code       INT DEFAULT 0,
    duration_ms     INT DEFAULT 0,
    tunnel          VARCHAR(16) DEFAULT 'forge',
    submitted_by    VARCHAR(32) NULL,
    stdout_hash     VARCHAR(64) NULL,
    stderr_hash     VARCHAR(64) NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_node (node_id),
    INDEX idx_tool (tool_id),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- sec_ssh_sessions: SSH session audit (wg7 tunnel)
CREATE TABLE IF NOT EXISTS sec_ssh_sessions (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id         VARCHAR(6) NOT NULL,
    session_id      VARCHAR(64) NOT NULL,
    action          ENUM('check_in','goodbye','timeout','rotate') NOT NULL,
    source_ip       VARCHAR(45) NOT NULL,
    tunnel          VARCHAR(16) DEFAULT 'wg7',
    signing_code    VARCHAR(32) NULL,
    duration_sec    INT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_node (node_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- sec_vault_calls: Vault key retrieval audit (compass tunnel)
CREATE TABLE IF NOT EXISTS sec_vault_calls (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id         VARCHAR(6) NOT NULL,
    key_type        VARCHAR(32) NOT NULL,
    action          ENUM('pull','verify','rotate','revoke') NOT NULL,
    tunnel          VARCHAR(16) DEFAULT 'compass',
    result          ENUM('ok','denied','timeout','error') NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_node (node_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- sec_dashboard_ops: Dashboard action audit (lantern tunnel)
CREATE TABLE IF NOT EXISTS sec_dashboard_ops (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    admin_id        INT NOT NULL,
    action          VARCHAR(64) NOT NULL,
    endpoint        VARCHAR(128) NOT NULL,
    method          VARCHAR(8) NOT NULL,
    tunnel          VARCHAR(16) DEFAULT 'lantern',
    source_ip       VARCHAR(45) NULL,
    response_code   INT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin (admin_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- sec_payment_events: Payment event audit (rain tunnel)
CREATE TABLE IF NOT EXISTS sec_payment_events (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    payment_id      BIGINT NOT NULL,
    event_type      ENUM('init','confirm','webhook','refund','dispute','timeout') NOT NULL,
    method          ENUM('stripe','xmr','usdt-trc','usdc-poly','etransfer') NOT NULL,
    tunnel          VARCHAR(16) DEFAULT 'rain',
    amount          DECIMAL(10,2) NULL,
    currency        CHAR(3) DEFAULT 'CAD',
    status          VARCHAR(20) NOT NULL,
    metadata        JSON NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_payment (payment_id),
    INDEX idx_type (event_type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

---

## Migration Plan: shared_engine to engine

| Step | Action | Risk |
|------|--------|:----:|
| 1 | Create sec_* tables in engine DB on 669427 | NONE |
| 2 | Update log_to_db.sh: use se_curl_post via forge instead of direct MySQL | LOW |
| 3 | Update direct-ip-detect.sh: same migration | LOW |
| 4 | Update telemetry-cron.sh: same migration | LOW |
| 5 | Add `/tool-log` endpoint to C++ engine | MEDIUM |
| 6 | Test: tool execution → forge → Engine API → sec_tool_runs | VERIFY |
| 7 | Decommission `shared_engine` DB on Origin | LOW |
| 8 | Revoke `tooling` user grants on Origin | LOW |

> **Key architectural change**: Tools no longer write directly to MySQL. They use `se_curl_post()` to send HMAC-signed JSON to Engine's C++ API via forge tunnel. Engine daemon validates HMAC and inserts into its local MySQL. This keeps MySQL bound to localhost-only on both nodes.

---

## SCLS Deployment Status (2026-07-04)

| Component | Status | Notes |
|-----------|:------:|-------|
| `sec_audit_log` table | ✅ Schema ready | Partitioned by month, 06-scls.sql |
| `sec_audit_decryptions` table | ✅ Schema ready | Decryption audit trail |
| `sec_audit_rate_limits` table | ✅ Schema ready | 10/min sliding window |
| `sec_tool_registry` table | ✅ Schema ready | 16 tools seeded |
| `audit_logger.sh` (node-side) | ✅ Code ready | AES-256-CBC + HMAC + spool |
| `gen_audit_keys.sh` | ✅ Code ready | Per-node key generation |
| PHP API endpoints | ✅ Code ready | log, list, decrypt, stream |
| Dashboard Audit tab | ✅ Code ready | Dark theme, SSE, decrypt modal |
| Key deployment | ⏳ Pending | Run gen_audit_keys.sh on Engine |
| DB migration | ⏳ Pending | Run 06-scls.sql on Engine MariaDB |
| Node tool deployment | ⏳ Pending | SCP audit_logger.sh to all nodes |

---

## Data Flow Diagram

```
  Any Node (tool)              Engine (669427)              Dashboard (Origin)
  ┌──────────┐   forge tunnel  ┌──────────────┐  lantern   ┌──────────────┐
  │ Tool     │───────────────►│ C++ API :8301 │◄───────────│ PHP          │
  │ se_curl  │  HMAC-signed   │      │        │ HMAC-signed│ EngineClient │
  └──────────┘                │      ▼        │            └──────────────┘
                              │  engine DB    │
                              │  (localhost)  │
                              │  sec_*        │
                              └───────────────┘

  Origin NEVER sees raw sec_tool_runs data.
  Engine controls what Dashboard reads via API endpoints.
```

---

## Verification Queries

```sql
-- Check tool log flow
SELECT node_id, tool_id, COUNT(*) as runs, MAX(created_at) as last_run
FROM sec_tool_runs GROUP BY node_id, tool_id ORDER BY last_run DESC;

-- Check vault key pulls (should correlate with tool runs)
SELECT node_id, key_type, action, COUNT(*) as calls
FROM sec_vault_calls GROUP BY node_id, key_type, action;

-- Cross-tunnel forensics for a specific node
SELECT 'ssh' as tunnel, created_at, action as detail
  FROM sec_ssh_sessions WHERE node_id='333941'
UNION ALL
SELECT 'vault', created_at, CONCAT(key_type, ':', action)
  FROM sec_vault_calls WHERE node_id='333941'
UNION ALL
SELECT 'forge', created_at, CONCAT(tool_id, ':', status)
  FROM sec_tool_runs WHERE node_id='333941'
ORDER BY created_at DESC LIMIT 50;
```

---

## Cross-Reference

| Document | Relevant Section |
|----------|-----------------|
| [ENGINE_STANDARDIZATION.md](file:///Y:/documents/ENGINE_STANDARDIZATION.md) | API endpoints, HMAC protocol, tunnel-per-purpose logging |
| [SCHEMA_MAP.md](file:///Y:/documents/SCHEMA_MAP.md) | Full table definitions for store DB (77+ tables) |
| [WIREGUARD_MAP.md](file:///Y:/documents/WIREGUARD_MAP.md) | Tunnel registry, forge/compass/lantern IPs and ports |
| [PORT_STANDARDIZATION.md](file:///Y:/documents/PORT_STANDARDIZATION.md) | Port 3306 (MySQL), 8301 (Engine API) |
| [TOOLING_STANDARDIZATION.md](file:///Y:/documents/TOOLING_STANDARDIZATION.md) | TOOL_IDs, log_to_db.sh (f6a9), engine.json tool_map |
| [rules.md](file:///Y:/documents/rules.md) | OPS-03 (logging), NET-02 (tunnel isolation) |
