Database Schema

Database Schema

Overview

Oh My Workers uses PostgreSQL (hosted on Neon) with the native pg driver — no ORM. All queries use parameterized statements.

Two database connections:

Connection	Purpose
DATABASE_URL	Main app data (kpi, diary, cleanup_log, ai_news)
COMPANY_DB_URL	External company database for cleanup operations

Tables

kpi

Daily GitHub activity records combined with manual input.

CREATE TABLE IF NOT EXISTS kpi (
  id             SERIAL PRIMARY KEY,
  github_summary TEXT    NOT NULL,
  commits_count  INTEGER NOT NULL DEFAULT 0,
  prs_count      INTEGER NOT NULL DEFAULT 0,
  activities     TEXT[]  NOT NULL DEFAULT '{}',
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

diary

AI-generated daily KPI reports.

CREATE TABLE IF NOT EXISTS diary (
  id         SERIAL PRIMARY KEY,
  content    TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

cleanup_log

History of company database cleanup operations.

CREATE TABLE IF NOT EXISTS cleanup_log (
  id            SERIAL PRIMARY KEY,
  company_table TEXT    NOT NULL,
  deleted_count INTEGER NOT NULL DEFAULT 0,
  failed_count  INTEGER NOT NULL DEFAULT 0,
  status        TEXT    NOT NULL,
  errors        TEXT[]  NOT NULL DEFAULT '{}',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ai_news

Curated daily AI news articles from the TS/JS/Node.js ecosystem.

CREATE TABLE IF NOT EXISTS ai_news (
  id         SERIAL PRIMARY KEY,
  title      TEXT    NOT NULL,
  url        TEXT    NOT NULL,
  summary    TEXT    NOT NULL,
  sent       BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Initialization

Run once to create all tables:

pnpm run setup

Useful Queries

-- Recent AI news
SELECT * FROM ai_news ORDER BY created_at DESC LIMIT 10;

-- Today's KPI
SELECT * FROM kpi WHERE created_at::date = CURRENT_DATE;

-- Cleanup history
SELECT * FROM cleanup_log ORDER BY created_at DESC LIMIT 5;

-- All diary entries this week
SELECT * FROM diary WHERE created_at >= NOW() - INTERVAL '7 days';

Future: Vector Support

Neon supports the pgvector extension. When ready to add semantic search:

CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE ai_news ADD COLUMN embedding vector(1536);

Then query by similarity:

SELECT title, summary
FROM ai_news
ORDER BY embedding <=> $1
LIMIT 10;