candle-annotator/openspec/specs/postgres-data-layer/spec.md

ADDED Requirements

Requirement: PostgreSQL connection via Drizzle ORM

The Next.js application SHALL connect to PostgreSQL using Drizzle ORM with the node-postgres (pg) driver. The connection SHALL use a pool with a configurable maximum number of connections (default: 10). The connection string SHALL be read from the DATABASE_URL environment variable.

Scenario: Successful connection

• WHEN the application starts with a valid DATABASE_URL pointing to a running PostgreSQL instance
• THEN Drizzle ORM establishes a connection pool and the db export is ready for queries

Scenario: Missing DATABASE_URL

• WHEN the DATABASE_URL environment variable is not set
• THEN the application SHALL fail to start with an error message indicating the missing variable

Scenario: Database unreachable

• WHEN the PostgreSQL instance is not reachable at the configured URL
• THEN the application SHALL fail to start with a connection error

Requirement: PostgreSQL schema definitions

The Drizzle schema SHALL define all frontend tables using pgTable from drizzle-orm/pg-core. The following tables SHALL be defined: users, charts, candles, annotation_types, annotations, span_label_types, span_annotations. All tables except users and candles SHALL include a user_id column (uuid, foreign key to users.id, not null). The candles table inherits user scope through its chart_id foreign key to charts.

Scenario: Charts table schema

• WHEN the schema is loaded
• THEN the charts table has columns: id (serial, primary key), name (text, not null), user_id (uuid, foreign key to users.id, not null), created_at (timestamp, not null, default now), with a unique index on (user_id, name)

Scenario: Candles table schema

• WHEN the schema is loaded
• THEN the candles table has columns: id (serial, primary key), chart_id (integer, foreign key to charts.id, not null), time (timestamp, not null), open (double precision, not null), high (double precision, not null), low (double precision, not null), close (double precision, not null), with a unique index on (chart_id, time)

Scenario: Annotation types table schema

• WHEN the schema is loaded
• THEN the annotation_types table has columns: id (serial, primary key), name (text, not null), display_name (text, not null), color (text, not null), category (text, not null), icon (text, nullable), is_active (boolean, not null, default true), user_id (uuid, foreign key to users.id, not null), created_at (timestamp, not null, default now), with a unique index on (user_id, name)

Scenario: Annotations table schema

• WHEN the schema is loaded
• THEN the annotations table has columns: id (serial, primary key), chart_id (integer, foreign key to charts.id, not null), timestamp (timestamp, not null), label_type (text, not null), geometry (jsonb, nullable), color (text, default '#3b82f6'), user_id (uuid, foreign key to users.id, not null), created_at (timestamp, not null, default now)

Scenario: Span label types table schema

• WHEN the schema is loaded
• THEN the span_label_types table has columns: id (serial, primary key), name (text, not null), display_name (text, not null), color (text, not null), hotkey (text, nullable), is_active (boolean, not null, default true), sort_order (integer, not null, default 0), user_id (uuid, foreign key to users.id, not null), created_at (timestamp, not null, default now), with a unique index on (user_id, name)

Scenario: Span annotations table schema

• WHEN the schema is loaded
• THEN the span_annotations table has columns: id (serial, primary key), chart_id (integer, foreign key to charts.id, not null), start_time (timestamp, not null), end_time (timestamp, not null), label (text, not null), confidence (integer, nullable), outcome (text, nullable), notes (text, nullable), sub_spans (jsonb, nullable), color (text, not null, default '#2196F3'), source (text, not null, default 'human'), model_prediction (jsonb, nullable), user_id (uuid, foreign key to users.id, not null), created_at (timestamp, not null, default now)

Requirement: PostgreSQL migrations via Drizzle Kit

The project SHALL use Drizzle Kit to generate and apply PostgreSQL migrations. The drizzle.config.ts SHALL target the postgresql dialect. Existing SQLite migrations SHALL be removed.

Scenario: Generate migrations

• WHEN drizzle-kit generate is executed
• THEN a new SQL migration file is created in the drizzle/ directory with PostgreSQL-dialect DDL

Scenario: Apply migrations at startup

• WHEN the application starts (not during build phase)
• THEN Drizzle runs pending migrations against the PostgreSQL database

Scenario: Skip migrations during build

• WHEN NEXT_PHASE is phase-production-build or phase-development-build
• THEN migration execution is skipped

Requirement: npm dependency changes

The project SHALL remove better-sqlite3 and @types/better-sqlite3 from dependencies and add pg and @types/pg.

Scenario: Dependencies updated

• WHEN package.json is inspected
• THEN better-sqlite3 and @types/better-sqlite3 are absent, and pg and @types/pg are present in dependencies

Requirement: Data migration from SQLite to PostgreSQL

The project SHALL include a one-time migration script at scripts/migrate-sqlite-to-postgres.ts that reads all data from the SQLite database and inserts it into PostgreSQL with appropriate type conversions.

Scenario: Migrate all tables

• WHEN the migration script is executed with both databases accessible
• THEN all rows from charts, candles, annotation_types, annotations, span_label_types, and span_annotations are transferred to PostgreSQL

Scenario: Type conversions applied

• WHEN data is migrated
• THEN SQLite integer timestamps are converted to PostgreSQL timestamps, integer booleans (0/1) are converted to PostgreSQL booleans, and text JSON fields are inserted as jsonb

Scenario: Idempotent execution

• WHEN the migration script is run a second time on an already-migrated database
• THEN the script either skips existing data or clears and re-inserts (with a flag), without creating duplicates

Requirement: Environment variable configuration (credentials)

The project SHALL use environment variables for runtime configuration. Credentials SHALL NOT be hardcoded in any committed file.

Scenario: .env file gitignored

• WHEN .gitignore is inspected
• THEN it includes .env (bare, not just .env*.local)

Scenario: .env removed from git history

• WHEN git ls-files .env is run
• THEN .env is NOT tracked by git

Scenario: .env.example has placeholder credentials

• WHEN .env.example is inspected
• THEN it contains POSTGRES_PASSWORD=change_me_to_a_strong_password (not a real password)

Scenario: No credentials in Python source

• WHEN services/ml/app/db.py is inspected
• THEN there are no SQL comments containing usernames or passwords, and the code fails fast if DATABASE_URL env var is not set

Requirement: models directory gitignored

The .gitignore file SHALL include models/ and *.pkl patterns to prevent model files from being committed.

Scenario: Model files excluded

• WHEN a model file is saved to models/best.pkl
• THEN git status does not show it as untracked

Requirement: devDependencies correctly categorized

The package.json SHALL list @types/*, typescript, eslint, eslint-config-next, autoprefixer, and postcss under devDependencies (not dependencies).

Scenario: Type packages in devDependencies

• WHEN package.json is inspected
• THEN @types/node, @types/react, @types/react-dom, @types/papaparse, @types/pg are in devDependencies

Scenario: Build tools in devDependencies

• WHEN package.json is inspected
• THEN typescript, eslint, eslint-config-next, autoprefixer, postcss are in devDependencies

ADDED Requirements (user-accounts)

Requirement: Users table schema

The Drizzle schema SHALL define a users table with columns: id (uuid, primary key, default gen_random_uuid()), name (text, nullable), email (text, unique, not null), email_verified (timestamp, nullable), password_hash (text, nullable), image (text, nullable), provider (text, not null, default 'credentials'), provider_account_id (text, nullable), created_at (timestamp, not null, default now), updated_at (timestamp, not null, default now).

Scenario: Users table created

• WHEN the schema is loaded and migrations are applied
• THEN the users table exists with all specified columns and the email unique constraint

Scenario: UUID primary key generation

• WHEN a new user is inserted without specifying an ID
• THEN a UUID is automatically generated via gen_random_uuid()

Requirement: User data migration script

The project SHALL include a migration script that adds user_id to existing tables, creates a default admin user, assigns all existing data to that user, and makes user_id NOT NULL.

Scenario: Migration adds user_id columns

• WHEN the migration runs on a database without user_id columns
• THEN user_id (uuid, nullable) columns are added to charts, annotation_types, annotations, span_label_types, span_annotations

Scenario: Default admin user created

• WHEN the migration runs and no users exist
• THEN a default admin user is created with email from DEFAULT_ADMIN_EMAIL env var (default: admin@candleannotator.local) and password from DEFAULT_ADMIN_PASSWORD env var (default: changeme123)

Scenario: Existing data assigned to admin

• WHEN the migration runs and existing rows have NULL user_id
• THEN all rows are updated to set user_id to the admin user's ID

Scenario: Columns made NOT NULL

• WHEN all existing rows have been assigned a user_id
• THEN the user_id columns are altered to NOT NULL with foreign key constraints

Scenario: Unique constraints updated

• WHEN the migration completes
• THEN the unique constraint on charts.name is replaced with (user_id, name)
• AND the unique constraint on annotation_types.name is replaced with (user_id, name)
• AND the unique constraint on span_label_types.name is replaced with (user_id, name)