MySQL Schema
Production-ready schema for persistent conversation memory and fraud event logging.
MySQL 8.0+, auto-increment primary keys, JSON for flexible signal storage.
-- ════════════════════════════════════════════════════
-- NEXUS AI · Persistent Memory & Fraud Schema
-- MySQL 8.0+ · Brown Business Intelligence LLP
-- ════════════════════════════════════════════════════
-- ── Customer master record ─────────────────────────
CREATE TABLE customers (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
customer_id VARCHAR(50) UNIQUE NOT NULL,
full_name VARCHAR(120),
email VARCHAR(255),
tier VARCHAR(20) DEFAULT 'standard',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- ── Conversation sessions ──────────────────────────
CREATE TABLE conversation_sessions (
id CHAR(36) PRIMARY KEY,
customer_id VARCHAR(50) NOT NULL,
channel VARCHAR(20) DEFAULT 'web',
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ended_at DATETIME,
agent_model VARCHAR(40) DEFAULT 'nexus-v3.1',
CONSTRAINT fk_sessions_customer FOREIGN KEY (customer_id)
REFERENCES customers(customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- ── Full message log (immutable audit trail) ───────
CREATE TABLE messages (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
session_id CHAR(36) NOT NULL,
role VARCHAR(12),
content TEXT NOT NULL,
tokens INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_messages_session FOREIGN KEY (session_id)
REFERENCES conversation_sessions(id) ON DELETE CASCADE,
CONSTRAINT chk_role CHECK (role IN ('user','assistant','system'))
) ENGINE=InnoDB;
-- ── AI-generated summaries (the memory layer) ──────
CREATE TABLE conversation_summaries (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
session_id CHAR(36),
customer_id VARCHAR(50) NOT NULL,
summary TEXT NOT NULL,
key_topics JSON,
resolved BOOLEAN DEFAULT TRUE,
sentiment VARCHAR(20),
follow_up_items JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_summaries_session FOREIGN KEY (session_id)
REFERENCES conversation_sessions(id) ON DELETE SET NULL,
CONSTRAINT fk_summaries_customer FOREIGN KEY (customer_id)
REFERENCES customers(customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- ── Fraud detection events ─────────────────────────
CREATE TABLE fraud_events (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
transaction_id VARCHAR(50) UNIQUE NOT NULL,
customer_id VARCHAR(50),
amount DECIMAL(12,2),
merchant TEXT,
risk_score DECIMAL(4,3) CHECK(risk_score BETWEEN 0 AND 1),
signals JSON,
decision VARCHAR(20) DEFAULT 'pending',
reviewed_at DATETIME,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- ── Indexes ────────────────────────────────────────
CREATE INDEX idx_summaries_customer
ON conversation_summaries(customer_id, created_at);
CREATE INDEX idx_messages_session
ON messages(session_id, created_at);
CREATE INDEX idx_fraud_risk
ON fraud_events(risk_score, created_at);
CREATE INDEX idx_fraud_customer
ON fraud_events(customer_id, created_at);
Executed on every new session to prime the agent's context window
-- Load last 5 summaries for returning customer
SELECT
cs.summary,
cs.key_topics,
cs.resolved,
cs.sentiment,
cs.follow_up_items,
cs.created_at,
TIMESTAMPDIFF(SECOND, cs.created_at, NOW()) AS seconds_ago
FROM conversation_summaries cs
WHERE cs.customer_id = ?
AND cs.created_at > NOW() - INTERVAL 90 DAY
ORDER BY cs.created_at DESC
LIMIT 5;
↑
Persist Session Summary
Called when session ends — AI generates the summary automatically
START TRANSACTION;
UPDATE conversation_sessions
SET ended_at = NOW()
WHERE id = ?;
INSERT INTO conversation_summaries
(session_id, customer_id, summary,
key_topics, resolved, sentiment, follow_up_items)
VALUES
(?, ?, ?,
?, ?, ?, ?);
COMMIT;
Memory Architecture
1
Session Start
Query last 5 summaries → inject into the system prompt as compressed context.
Agent greets customer by name and references prior topics naturally.
2
During Session
All messages written in real-time to the messages table.
Full audit trail, immutable, with token counts for cost tracking.
3
Session End
AI summarizes the full conversation, extracts topics, flags unresolved items,
and scores sentiment. Committed to conversation_summaries.
4
Next Visit
Summaries retrieved and prepended to the agent's context window —
enabling natural continuity without the customer repeating themselves.