← Back to Kontasks

gvAgent SQLite Schema - Session Storage

Full SQLite schema for session storage from gvAgentDesign.md Section 9

✓ Completed
on 6 Jan 2026, 7:24 am

Output

gvAgent SQLite Schema

From gv/docs/gvAgentDesign.md Section 9.2

📁 Storage Layout

~/.local/share/gvagent/
├── sessions.db              # SQLite - session metadata
├── events/
│   ├── sess_abc123.jsonl    # Event journal (immutable)
│   └── sess_def456.jsonl
├── artifacts/
│   └── sess_abc123/
├── snapshots/               # Periodic state snapshots
└── cache/
    └── embeddings.db        # Future: vector store

📋 sessions Table

CREATE TABLE sessions (
    id                 TEXT PRIMARY KEY,
    flow_id            TEXT,
    agent_id           TEXT NOT NULL,
    provider           TEXT NOT NULL,
    model              TEXT NOT NULL,
    status             TEXT NOT NULL DEFAULT 'idle',
    created_at         TEXT NOT NULL,
    updated_at         TEXT NOT NULL,
    last_activity_at   TEXT NOT NULL,
    total_turns        INTEGER DEFAULT 0,
    total_tokens       INTEGER DEFAULT 0,
    total_cost_usd     REAL DEFAULT 0,
    working_directory  TEXT,
    git_branch         TEXT
);

-- Indexes
CREATE INDEX idx_sessions_status ON sessions(status);
CREATE INDEX idx_sessions_flow ON sessions(flow_id);
CREATE INDEX idx_sessions_activity ON sessions(last_activity_at);

🔄 turns Table

CREATE TABLE turns (
    id            TEXT PRIMARY KEY,
    session_id    TEXT NOT NULL REFERENCES sessions(id),
    sequence      INTEGER NOT NULL,
    status        TEXT NOT NULL,
    created_at    TEXT NOT NULL,
    completed_at  TEXT,
    input_tokens  INTEGER,
    output_tokens INTEGER,
    cost_usd      REAL,
    UNIQUE(session_id, sequence)
);

CREATE INDEX idx_turns_session ON turns(session_id);

📊 event_index Table

-- For fast range queries into JSONL files
CREATE TABLE event_index (
    session_id  TEXT NOT NULL,
    seq         INTEGER NOT NULL,
    type        TEXT NOT NULL,
    timestamp   TEXT NOT NULL,
    PRIMARY KEY(session_id, seq)
);

📸 snapshots Table

-- For fast replay of long sessions
CREATE TABLE snapshots (
    session_id  TEXT NOT NULL,
    seq         INTEGER NOT NULL,
    state       BLOB NOT NULL,  -- Compressed session state
    created_at  TEXT NOT NULL,
    PRIMARY KEY(session_id, seq)
);

💡 Design Notes

  • SQLite + JSONL hybrid — Metadata in SQLite for fast queries, full events in append-only JSONL
  • Snapshots every 1000 events OR 5 min — Enables fast replay without reading entire journal
  • event_index — Allows type/time-based queries without parsing JSONL
  • TEXT timestamps — ISO 8601 format for portability

What's Next?

Choose an action:

Quick Actions

Original Request

No layout configured

Details

Type General
Status Completed
Scope vibetools
Tags gvagentstoragesqliteschema
Created 6 Jan 2026, 7:24 am
Updated 6 Jan 2026, 7:24 am
Timing
⏱️ 46.6s
0 tools
● TTFE 0% ● Thinking 100% ● Tools 0%
Created By claude

Raw Data

{
  "id": "742df11d-5293-47bd-a4d2-57a7a26babbd",
  "type": "general",
  "status": "completed",
  "title": "gvAgent SQLite Schema - Session Storage",
  "description": "Full SQLite schema for session storage from gvAgentDesign.md Section 9",
  "context": {
    "output": "<div style=\"background:#1e1e1e;color:#e0e0e0;padding:20px;border-radius:8px;font-family:'Consolas','Monaco',monospace;\">\n<h2 style=\"font-family:sans-serif;color:#fff;margin:0 0 16px 0;\">gvAgent SQLite Schema</h2>\n<p style=\"color:#9ca3af;margin:0 0 16px 0;\">From <code style=\"background:#3d3d3d;padding:2px 6px;border-radius:3px;\">gv/docs/gvAgentDesign.md</code> Section 9.2</p>\n\n<div style=\"background:#2d2d2d;padding:16px;border-radius:6px;margin-bottom:16px;\">\n<h3 style=\"color:#98c379;margin:0 0 12px 0;\">📁 Storage Layout</h3>\n<pre style=\"background:#3d3d3d;padding:12px;border-radius:4px;overflow-x:auto;line-height:1.6;margin:0;\">\n<span style=\"color:#56b6c2;\">~/.local/share/gvagent/</span>\n├── <span style=\"color:#e5c07b;\">sessions.db</span>              <span style=\"color:#7f848e;\"># SQLite - session metadata</span>\n├── <span style=\"color:#61afef;\">events/</span>\n│   ├── <span style=\"color:#e5c07b;\">sess_abc123.jsonl</span>    <span style=\"color:#7f848e;\"># Event journal (immutable)</span>\n│   └── <span style=\"color:#e5c07b;\">sess_def456.jsonl</span>\n├── <span style=\"color:#61afef;\">artifacts/</span>\n│   └── <span style=\"color:#61afef;\">sess_abc123/</span>\n├── <span style=\"color:#61afef;\">snapshots/</span>               <span style=\"color:#7f848e;\"># Periodic state snapshots</span>\n└── <span style=\"color:#61afef;\">cache/</span>\n    └── <span style=\"color:#e5c07b;\">embeddings.db</span>        <span style=\"color:#7f848e;\"># Future: vector store</span>\n</pre>\n</div>\n\n<div style=\"background:#2d2d2d;padding:16px;border-radius:6px;margin-bottom:16px;\">\n<h3 style=\"color:#e5c07b;margin:0 0 12px 0;\">📋 sessions Table</h3>\n<pre style=\"background:#3d3d3d;padding:12px;border-radius:4px;overflow-x:auto;line-height:1.6;margin:0;font-size:0.85rem;\">\n<span style=\"color:#c678dd;\">CREATE TABLE</span> <span style=\"color:#e5c07b;\">sessions</span> (\n    <span style=\"color:#61afef;\">id</span>                 <span style=\"color:#56b6c2;\">TEXT PRIMARY KEY</span>,\n    <span style=\"color:#61afef;\">flow_id</span>            <span style=\"color:#56b6c2;\">TEXT</span>,\n    <span style=\"color:#61afef;\">agent_id</span>           <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">provider</span>           <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">model</span>              <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">status</span>             <span style=\"color:#56b6c2;\">TEXT NOT NULL DEFAULT</span> <span style=\"color:#98c379;\">'idle'</span>,\n    <span style=\"color:#61afef;\">created_at</span>         <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">updated_at</span>         <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">last_activity_at</span>   <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">total_turns</span>        <span style=\"color:#56b6c2;\">INTEGER DEFAULT</span> <span style=\"color:#d19a66;\">0</span>,\n    <span style=\"color:#61afef;\">total_tokens</span>       <span style=\"color:#56b6c2;\">INTEGER DEFAULT</span> <span style=\"color:#d19a66;\">0</span>,\n    <span style=\"color:#61afef;\">total_cost_usd</span>     <span style=\"color:#56b6c2;\">REAL DEFAULT</span> <span style=\"color:#d19a66;\">0</span>,\n    <span style=\"color:#61afef;\">working_directory</span>  <span style=\"color:#56b6c2;\">TEXT</span>,\n    <span style=\"color:#61afef;\">git_branch</span>         <span style=\"color:#56b6c2;\">TEXT</span>\n);\n\n<span style=\"color:#7f848e;\">-- Indexes</span>\n<span style=\"color:#c678dd;\">CREATE INDEX</span> idx_sessions_status <span style=\"color:#c678dd;\">ON</span> sessions(status);\n<span style=\"color:#c678dd;\">CREATE INDEX</span> idx_sessions_flow <span style=\"color:#c678dd;\">ON</span> sessions(flow_id);\n<span style=\"color:#c678dd;\">CREATE INDEX</span> idx_sessions_activity <span style=\"color:#c678dd;\">ON</span> sessions(last_activity_at);\n</pre>\n</div>\n\n<div style=\"background:#2d2d2d;padding:16px;border-radius:6px;margin-bottom:16px;\">\n<h3 style=\"color:#61afef;margin:0 0 12px 0;\">🔄 turns Table</h3>\n<pre style=\"background:#3d3d3d;padding:12px;border-radius:4px;overflow-x:auto;line-height:1.6;margin:0;font-size:0.85rem;\">\n<span style=\"color:#c678dd;\">CREATE TABLE</span> <span style=\"color:#e5c07b;\">turns</span> (\n    <span style=\"color:#61afef;\">id</span>            <span style=\"color:#56b6c2;\">TEXT PRIMARY KEY</span>,\n    <span style=\"color:#61afef;\">session_id</span>    <span style=\"color:#56b6c2;\">TEXT NOT NULL REFERENCES</span> sessions(id),\n    <span style=\"color:#61afef;\">sequence</span>      <span style=\"color:#56b6c2;\">INTEGER NOT NULL</span>,\n    <span style=\"color:#61afef;\">status</span>        <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">created_at</span>    <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">completed_at</span>  <span style=\"color:#56b6c2;\">TEXT</span>,\n    <span style=\"color:#61afef;\">input_tokens</span>  <span style=\"color:#56b6c2;\">INTEGER</span>,\n    <span style=\"color:#61afef;\">output_tokens</span> <span style=\"color:#56b6c2;\">INTEGER</span>,\n    <span style=\"color:#61afef;\">cost_usd</span>      <span style=\"color:#56b6c2;\">REAL</span>,\n    <span style=\"color:#c678dd;\">UNIQUE</span>(session_id, sequence)\n);\n\n<span style=\"color:#c678dd;\">CREATE INDEX</span> idx_turns_session <span style=\"color:#c678dd;\">ON</span> turns(session_id);\n</pre>\n</div>\n\n<div style=\"background:#2d2d2d;padding:16px;border-radius:6px;margin-bottom:16px;\">\n<h3 style=\"color:#c678dd;margin:0 0 12px 0;\">📊 event_index Table</h3>\n<pre style=\"background:#3d3d3d;padding:12px;border-radius:4px;overflow-x:auto;line-height:1.6;margin:0;font-size:0.85rem;\">\n<span style=\"color:#7f848e;\">-- For fast range queries into JSONL files</span>\n<span style=\"color:#c678dd;\">CREATE TABLE</span> <span style=\"color:#e5c07b;\">event_index</span> (\n    <span style=\"color:#61afef;\">session_id</span>  <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">seq</span>         <span style=\"color:#56b6c2;\">INTEGER NOT NULL</span>,\n    <span style=\"color:#61afef;\">type</span>        <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">timestamp</span>   <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#c678dd;\">PRIMARY KEY</span>(session_id, seq)\n);\n</pre>\n</div>\n\n<div style=\"background:#2d2d2d;padding:16px;border-radius:6px;margin-bottom:16px;\">\n<h3 style=\"color:#56b6c2;margin:0 0 12px 0;\">📸 snapshots Table</h3>\n<pre style=\"background:#3d3d3d;padding:12px;border-radius:4px;overflow-x:auto;line-height:1.6;margin:0;font-size:0.85rem;\">\n<span style=\"color:#7f848e;\">-- For fast replay of long sessions</span>\n<span style=\"color:#c678dd;\">CREATE TABLE</span> <span style=\"color:#e5c07b;\">snapshots</span> (\n    <span style=\"color:#61afef;\">session_id</span>  <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#61afef;\">seq</span>         <span style=\"color:#56b6c2;\">INTEGER NOT NULL</span>,\n    <span style=\"color:#61afef;\">state</span>       <span style=\"color:#56b6c2;\">BLOB NOT NULL</span>,  <span style=\"color:#7f848e;\">-- Compressed session state</span>\n    <span style=\"color:#61afef;\">created_at</span>  <span style=\"color:#56b6c2;\">TEXT NOT NULL</span>,\n    <span style=\"color:#c678dd;\">PRIMARY KEY</span>(session_id, seq)\n);\n</pre>\n</div>\n\n<div style=\"background:#2d2d2d;padding:16px;border-radius:6px;\">\n<h4 style=\"margin:0 0 8px 0;color:#fff;\">💡 Design Notes</h4>\n<ul style=\"margin:0;padding-left:20px;color:#9ca3af;line-height:1.8;\">\n<li><strong>SQLite + JSONL hybrid</strong> — Metadata in SQLite for fast queries, full events in append-only JSONL</li>\n<li><strong>Snapshots every 1000 events OR 5 min</strong> — Enables fast replay without reading entire journal</li>\n<li><strong>event_index</strong> — Allows type/time-based queries without parsing JSONL</li>\n<li><strong>TEXT timestamps</strong> — ISO 8601 format for portability</li>\n</ul>\n</div>\n\n<div style=\"background:#2d2d2d;padding:16px;border-radius:6px;margin-top:16px;\">\n<h4 style=\"margin:0 0 8px 0;color:#fff;\">What's Next?</h4>\n<p style=\"color:#9ca3af;margin:0;\">Choose an action:</p>\n</div>\n</div>",
    "requestedAt": "2026-01-06T12:05:00Z",
    "requestId": "9e97d270-2125-42eb-87a1-2635ca298406",
    "choices": [
      {
        "label": "Show JSONL format",
        "value": "Show me the Event Journal JSONL format from gvAgentDesign.md Section 9.3",
        "primary": true
      },
      {
        "label": "Show snapshot strategy",
        "value": "Show me the session snapshot strategy and replay logic from gvAgentDesign.md Section 9.5"
      },
      {
        "label": "Back to overview",
        "value": "Show me the gvAgent documentation coverage summary again"
      }
    ],
    "turnTiming": {
      "totalMs": 46581,
      "ttfeMs": 91,
      "thinkingMs": 46490,
      "toolExecutionMs": 0,
      "toolCallCount": 0,
      "thinkingPct": 100,
      "toolsPct": 0,
      "ttfePct": 0
    }
  },
  "createdBy": "claude",
  "createdAt": "2026-01-05T21:24:22.735Z",
  "updatedAt": "2026-01-05T21:24:28.176Z",
  "requestId": "9e97d270-2125-42eb-87a1-2635ca298406",
  "scope": "vibetools",
  "tags": [
    "gvagent",
    "storage",
    "sqlite",
    "schema"
  ],
  "targetUser": "claude"
}
DashboardReportsKontasksSessionsTelemetryLogs + Go