investor-flow/.scratch/micro-1a-prompt.md

You are a code implementer. Build ONE focused module. Write the code now.

HARD RULES

• Do NOT call any automaton_* tool. Do NOT read PARENT_SPEC.md or DECOMPOSITION.md.
• Start writing files within your first 2 tool calls. Read only what's listed, then build.
• You are building micro-slice 1a: the SQLite database foundation for the Investor Flow backend.

CONTEXT (read these, then build)

The project is "Investor Flow" — a Bun + SQLite backend serving a Next.js SPA. You are creating the database layer. The schema follows DESIGN.md Section 1 (Tier A shared market cache + Tier D system).

Read this ONE file for the exact schema: grep -n "Tier D\|Tier A\|CREATE TABLE\|users\|sessions\|symbol_meta\|price_quotes\|price_candles\|symbol_demand" /Users/laptran/Documents/investor-flow/.automaton/tasks/investor-flow-platform-design/DESIGN.md | head -40

If the grep doesn't give enough, read lines 17-175 of that DESIGN.md.

BUILD EXACTLY THESE 4 FILES

1. app/server/package.json

{
  "name": "investor-flow-server",
  "version": "0.1.0",
  "type": "module",
  "scripts": {
    "dev": "bun run src/index.ts",
    "test": "bun test"
  },
  "dependencies": {
    "better-sqlite3": "^11.0.0",
    "@trpc/server": "^11.0.0",
    "argon2": "^0.40.0",
    "yahoo-finance2": "^2.0.0",
    "zod": "^3.23.0"
  },
  "devDependencies": {
    "@types/better-sqlite3": "^7.6.0",
    "bun-types": "^1.1.0",
    "typescript": "^5.0.0"
  }
}

2. app/server/tsconfig.json

Standard Bun TypeScript config (target ESNext, module ESNext, moduleResolution bundler, strict true, types ["bun-types"], outDir ./dist).

3. app/server/src/db/schema.sql

SQLite DDL for these tables (match DESIGN.md Section 1 Tier A + Tier D):

• users (id TEXT PK, email TEXT UNIQUE NOT NULL, pw_hash TEXT NOT NULL, complexity TEXT DEFAULT 'beginner', risk_tolerance TEXT, created_at TEXT NOT NULL)
• sessions (id TEXT PK, user_id TEXT NOT NULL REFERENCES users(id), expires_at TEXT NOT NULL, created_at TEXT NOT NULL)
• symbol_meta (symbol TEXT PK, name TEXT, sector TEXT, industry TEXT, exchange TEXT, ticker_kind TEXT NOT NULL DEFAULT 'equity', updated_at TEXT)
• price_quotes (symbol TEXT NOT NULL, price REAL, bid REAL, ask REAL, change REAL, change_pct REAL, ts_observed TEXT NOT NULL, fetched_at TEXT NOT NULL, PRIMARY KEY(symbol, ts_observed))
• price_candles (symbol TEXT NOT NULL, tf TEXT NOT NULL, ts TEXT NOT NULL, o REAL, h REAL, l REAL, c REAL, v REAL, adj_close REAL, PRIMARY KEY(symbol, tf, ts))
• symbol_demand (symbol TEXT PK, refcount INTEGER NOT NULL DEFAULT 0, protected INTEGER NOT NULL DEFAULT 0) Add indexes: on price_quotes(symbol), price_candles(symbol, ts), sessions(user_id).

4. app/server/src/db/client.ts

A DbClient class (or factory function) that:

• Opens a SQLite database (better-sqlite3). Accepts a path arg; default ./investor-flow.db. For tests, accept :memory:.
• On init, reads and executes schema.sql (use import.meta.dir to resolve the path relative to the file).
• Exposes the raw Database instance via a db property for other modules to use prepared statements.
• Exports a createDb(path?) factory. TypeScript throughout. Use better-sqlite3 synchronous API.

THEN

Run cd /Users/laptran/Documents/investor-flow/app/server && bun install to install deps. Then run a quick smoke test: bun -e "import {createDb} from './src/db/client'; const db = createDb(':memory:'); console.log(db.db.prepare('SELECT name FROM sqlite_master WHERE type=\"table\"').all());" to confirm tables exist. Print the result. Then STOP — do not build anything else.

PRIMARY RULE

No user-facing strings in this micro-slice (it's the DB layer). Just clean typed code.