@livefolio/db

Database management for Livefolio — Supabase migrations, schemas, edge functions, and shared TypeScript types.

Setup

npm install
npx supabase start

Copy .env.example to .env and fill in the values from npx supabase status:

cp .env.example .env
npx supabase status   # copy SUPABASE_URL, ANON_KEY, SERVICE_ROLE_KEY into .env

The FRED_API_KEY is needed for treasury/rate series (DTB3, DFF, DGS*, CPIAUCNS). Get a free key at https://fred.stlouisfed.org/docs/api/api_key.html.

Usage

Install in a consuming Node.js project:

npm install @livefolio/db

import type { Tables, Enums } from "@livefolio/db";

type Strategy = Tables<"strategies">;
type Frequency = Enums<"trading_frequency">;

The Constants export provides enum values as readonly arrays for runtime use:

import { Constants } from "@livefolio/db";

Constants.public.Enums.trading_frequency;
// ["Daily", "Weekly", "Monthly", "Quarterly", "Semiannually", "Yearly"]

Development

# Run all migrations and seeds against local DB
npx supabase db reset

# Regenerate TypeScript types after schema changes
npx supabase gen types typescript --local > src/database.types.ts

# Build the npm package (compiles src/ → dist/)
npm run build

Migrations

Create a new migration:

npx supabase migration new <name>

This creates a timestamped SQL file in supabase/migrations/. Write your DDL there, then run npx supabase db reset to apply.

Edge Functions

Edge functions live in supabase/functions/. Each function has its own deno.json for Deno configuration.

| Function | Description | |----------|-------------| | series | Cache-through daily price series (yahoo chart / FRED) | | quote | Real-time quote (yahoo quote API, falls back to series cache) | | strategy | Strategy resolution with testfol.io auto-import fallback |

Serve locally (loads secrets from .env):

npx supabase functions serve --env-file .env

Test:

# Run from monorepo root
deno test --no-check --allow-env --allow-net --allow-read db/supabase/functions/

Releasing

This package is published to the public npm registry. CI handles it automatically:

1. On PR — migrations are dry-run against the remote DB, and CI verifies the version in package.json was bumped
2. On merge to main — migrations are pushed to the remote DB, and the package is built and published to npm

Before merging a PR, bump the version:

npm version patch   # 0.1.0 → 0.1.1
npm version minor   # 0.1.1 → 0.2.0
npm version major   # 0.2.0 → 1.0.0

Project Structure

src/                        # TypeScript library (published to npm)
  index.ts                  # Re-exports types + Constants
  database.types.ts         # Auto-generated from Supabase schema
supabase/
  migrations/               # Timestamped SQL migrations (source of truth)
  schemas/                  # Numbered SQL files (human-readable reference)
  seeds/                    # Seed data (trading days calendar)
  functions/                # Supabase Edge Functions (Deno)
  templates/                # Email templates (magic link, email change)
  config.toml               # Local Supabase configuration