> ## Documentation Index
> Fetch the complete documentation index at: https://gladlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database schema

# Poindexter Database Schema

This document defines the database schema Poindexter runs on. The
entire system uses PostgreSQL 16 with the pgvector extension through
asyncpg. There is no ORM — queries are hand-written SQL, and schema
changes are tracked as migration files in
`src/cofounder_agent/services/migrations/` (the history is squashed
into `0000_baseline.py`, re-rolled most recently by the Phase F squash
on 2026-06-22 (which folded the Phase E baseline + 73 post-E migrations
and retired the `pipeline_tasks.category` column); new migrations use a
UTC timestamp prefix per poindexter#378).

**Last Updated:** 2026-06-22
**Version:** 0.1.x (alpha)
**Architecture:** PostgreSQL 16 + pgvector + FastAPI + asyncpg background workers

***

## **PostgreSQL Database Module Overview**

The system uses a modular database architecture with 6 specialized database modules, each handling domain-specific operations:

1. **UsersDatabase** - User accounts, OAuth, authentication
2. **TasksDatabase** - Task CRUD, filtering, status management
3. **ContentDatabase** - Posts, articles, publishing metadata
4. **AdminDatabase** - Logging, financial tracking, system health
5. **WritingStyleDatabase** - Writing samples for RAG-based style matching
6. **EmbeddingsDatabase** - pgvector storage and cosine-similarity search (writer-segregated across `brain`, `audit`, `posts`, `memory`, `claude_sessions`, `issues`, `samples`)

All six are reached through `services/database_service.py::DatabaseService` — callers don't import them directly. See [`../reference/services.md`](../reference/services) for service-level details and [GH-106](https://github.com/Glad-Labs/poindexter/issues/106) for the stale-embedding retention policy.

***

## **Core Database Tables**

### 1. `users` Table

Stores user accounts and authentication data.

```sql theme={null}
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(500),
    oauth_provider VARCHAR(50),
    oauth_id VARCHAR(255),
    full_name VARCHAR(255),
    avatar_url TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_oauth UNIQUE(oauth_provider, oauth_id)
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
```

### 2. `pipeline_tasks` Table

Stores the content-generation task queue. The Prefect-orchestrated flow
(`services/flows/content_generation.py`) claims rows via `SELECT ... FOR
UPDATE SKIP LOCKED`; each task carries a `template_slug` that picks the
LangGraph template (`canonical_blog` / `dev_diary`) to run.

```sql theme={null}
CREATE TABLE pipeline_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    template_slug VARCHAR(100) NOT NULL,  -- canonical_blog / dev_diary / ...
    title VARCHAR(500) NOT NULL,
    description TEXT,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    priority VARCHAR(20) DEFAULT 'medium',
    task_type VARCHAR(50),
    niche_slug VARCHAR(100),  -- added 2026-05; supersedes the retired category column (dropped by Phase F squash 2026-06-22)
    task_metadata JSONB DEFAULT '{}',
    quality_score FLOAT,
    auto_cancelled_at TIMESTAMP,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_pipeline_tasks_user_id ON pipeline_tasks(user_id);
CREATE INDEX idx_pipeline_tasks_status ON pipeline_tasks(status);
CREATE INDEX idx_pipeline_tasks_created_at ON pipeline_tasks(created_at DESC);
CREATE INDEX idx_pipeline_tasks_template_slug ON pipeline_tasks(template_slug);
```

### 3. `posts` Table

Stores published blog posts and the awaiting-approval queue. The
canonical content table — the older `content` example was never the
production shape.

```sql theme={null}
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(500) NOT NULL,
    slug VARCHAR(500) NOT NULL UNIQUE,
    content TEXT,
    excerpt VARCHAR(1000),
    cover_image_url TEXT,
    featured BOOLEAN DEFAULT FALSE,
    status VARCHAR(50) DEFAULT 'draft',  -- draft / awaiting_approval / published / archived
    quality_score FLOAT DEFAULT 0,
    seo_title VARCHAR(255),
    seo_description VARCHAR(500),
    seo_keywords TEXT,
    author VARCHAR(255),
    tags TEXT[],
    category VARCHAR(100),
    metadata JSONB DEFAULT '{}',  -- metadata->>'pipeline_task_id' is the canonical seam back to the source pipeline_tasks row (backfilled 2026-05-28)
    view_count INTEGER DEFAULT 0,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_published_at ON posts(published_at DESC);
```

### 4. `audit_log` Table

Canonical historical record — every significant pipeline transition,
QA decision, writer-fallback event, etc. lands here. Queried by
`routes/pipeline_events_routes.py` despite the legacy URL prefix.

```sql theme={null}
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type VARCHAR(100) NOT NULL,
    user_id UUID REFERENCES users(id),
    severity VARCHAR(20) DEFAULT 'info',  -- info / warning / error / critical
    details JSONB DEFAULT '{}',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_log_event_type ON audit_log(event_type);
CREATE INDEX idx_audit_log_created_at ON audit_log(created_at DESC);
```

### 5. `writing_samples` Table

Stores user writing samples for RAG-based style matching.

```sql theme={null}
CREATE TABLE writing_samples (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(500),
    content TEXT NOT NULL,
    embedding bytea,
    style_attributes JSONB,
    quality_score FLOAT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_writing_samples_user_id ON writing_samples(user_id);
CREATE INDEX idx_writing_samples_created_at ON writing_samples(created_at DESC);
```

***

## **API Endpoints Reference**

All content endpoints are served by the FastAPI backend at `http://localhost:8002`.
See the live OpenAPI spec at `/api/openapi.json` for the full endpoint catalog.

Key content routes:

* **Tasks**: `/api/tasks` (GET, POST, PUT, DELETE)
* **Content**: `/api/content` (GET, POST, PUT)
* **Posts**: `/api/posts` (GET, POST, PUT, DELETE)
* **Categories**: `/api/categories` (GET, POST, PUT, DELETE)

***

**Maintainer:** Poindexter is built and maintained by Glad Labs LLC.
For questions, open an issue on the [public repo](https://github.com/Glad-Labs/poindexter).