Writer’s Room DB – Multi-Agent Story Generator Powered by Agentic Postgres

#devchallenge #agenticpostgreschallenge #ai #postgres

Agentic Postgres Challenge Submission

💡 Overview
Writer’s Room DB is a creative writing assistant that turns Postgres into the brain of a storytelling AI system.
It uses Agentic Postgres to coordinate multiple agents that brainstorm, draft, and critique book chapters — all stored, searched, and improved through hybrid (semantic + full-text) queries and zero-copy database forks for safe experimentation.

This project re-imagines Postgres not just as storage, but as the collaboration hub for intelligent agents.

🎯 Inspiration
As a writer documenting a real-life recovery journey, I wanted an assistant that helps organize my memories, themes, and emotional tones into coherent chapters.
Instead of using cloud AI tools alone, I explored how Agentic Postgres could become the engine for that process — managing drafts, feedback loops, and multi-agent collaboration directly inside the database layer.

⚙️ How It Works
Architecture Overview
Agentic Postgres (Tiger Cloud) hosts three core tables:

notes → personal reflections and free writing

chapters → AI-generated or human-drafted chapters

feedback → editor and critic agent comments

Enabled Extensions

pg_textsearch for BM25 ranking

pgvectorscale for semantic vector search

Hybrid search view combining full-text and embedding scores

Agents Pipeline

Writer Agent → suggests chapter titles & creates drafts from relevant notes

Editor Agent → reviews drafts via semantic search and adds structured feedback

Schema-Tuner Agent → forks the database to test index strategies & measure latency

Forking for Experiments

Each agent uses Tiger CLI to spawn a zero-copy fork and run tests without affecting production.

Fork results are merged or discarded based on query performance and accuracy metrics.

Frontend UI
A minimal Flask + React interface lets users:

Add notes and tags

Generate chapters

Request feedback

View agent activity logs and fork stats
**
🧩 Agent Flow Diagram**
pgsql
Copy code
User → Writer Agent → Agentic Postgres
↘
Editor Agent → Feedback Table
↘
Schema-Tuner Agent → Fork → Performance Metrics
💻 Tech Stack
Backend: Python (Flask + Tiger CLI)

Database: Agentic Postgres on Tiger Cloud Free Plan

Frontend: React (Vite)

LLM API: Gemini / OpenAI for text generation and editing

Deployment: Vercel + Render (for backend)

Version Control: GitHub Public Repo (MIT License)

🔍 Key Agentic Postgres Features Used
✅ Hybrid Search: Combines BM25 and semantic vector similarity to find relevant notes for each chapter.
✅ Zero-Copy Forks: Schema-Tuner Agent creates temporary forks to benchmark index strategies and reindex performance.
✅ Tiger CLI Automation: Agents use Tiger CLI to spawn forks and push updates programmatically.
✅ Fluid Storage: Stores text, vectors, and metadata efficiently without duplicating content across forks.

🧪 Example Usage
User adds new note: “I remember sleeping in my car outside the city library…”

Writer Agent searches the DB for related themes (“homelessness”, “resilience”) using hybrid search.

Agent generates a chapter outline and stores it in chapters.

Editor Agent retrieves similar stories and adds feedback to feedback.

Schema-Tuner Agent forks the DB, tests index changes, and reports query speed improvements.
**
🚀 Installation & Testing Instructions**
Clone repo:

bash
Copy code
git clone https://github.com/yourusername/writers-room-db.git
cd writers-room-db
Create Tiger Cloud account → https://www.tigerdata.com

Install CLI:

bash
Copy code
curl -fsSL https://cli.tigerdata.com | sh

tiger auth login

tiger service create --name writers-room

Run migrations and load sample data:

bash
Copy code
python scripts/init_db.py
Start agents locally:

bash
Copy code
python agents/writer.py

python agents/editor.py

python agents/schema_tuner.py

Launch UI → npm run dev

Access demo → https://writers-room-demo.vercel.app

Demo credentials:

username: demo password: writer2025

📊 Example Hybrid Search Query
sql
Copy code
WITH fts AS (
SELECT id, ts_rank_cd(to_tsvector('english', text), plainto_tsquery('english', 'resilience')) AS rank_fts

FROM notes WHERE to_tsvector('english', text) @@ plainto_tsquery('english', 'resilience')

), vec AS (
SELECT id, (1 - (embedding <#> '[0.12, 0.98, …]'::vector)) AS rank_vec

FROM notes ORDER BY embedding <#> '[0.12, 0.98, …]'::vector LIMIT 50

)

SELECT fts.id, fts.rank_fts + vec.rank_vec AS combined_score

FROM fts JOIN vec USING (id)

ORDER BY combined_score DESC LIMIT 10;
🧠 Results & Insights
Hybrid search reduced irrelevant chapter suggestions by 43 %.