npm package: @outs-tand-ing/postgres
Description: PostgreSQL database with Drizzle ORM for Outs project
Latest version: 0.7.0

# Outs PostgreSQL Database

PostgreSQL 18 database setup with Drizzle ORM, matching all Mongoose schemas from the original MongoDB implementation.

## Features

- 🐘 PostgreSQL 18
- 🔥 Drizzle ORM
- 🐳 Docker & Docker Compose (dev + prod)
- 🌱 Seed scripts with demo users
- 🔐 Bcrypt password hashing
- 📊 Pre-built query functions with CLI commands
- 🎯 Type-safe schemas matching Mongoose models

## Quick Start

### 1. Install Dependencies

```bash
bun install
```

### 2. Set Up Environment

```bash
cp .env.example .env
```

Edit `.env` if needed (default is fine for local development).

### 3. Start PostgreSQL with Docker

```bash
bun run docker:up
```

This starts PostgreSQL 18 on `localhost:5432`.

### 4. Generate and Push Database Schema

```bash
# Generate migration files
bun run db:generate

# Push schema to database
bun run db:push
```

### 5. Seed the Database

```bash
bun run db:seed
```

This creates demo users with bcrypted passwords:

- `john.doe@example.com` / `password123`
- `jane.smith@example.com` / `securepass456`
- `admin@example.com` / `admin2024!`
- `bob.wilson@example.com` / `mypassword789`
- `alice.jones@example.com` / `alicepass2024`

### 6. Test Connection

```bash
bun run db:test
```

## Schema Overview

All schemas match the Mongoose models exactly:

### Account Module

- **Users** (`account_users`) - User accounts with bcrypt passwords, push devices, settings
- **Subscriptions** (`account_subscriptions`) - User subscriptions to seasons
- **Leagues** (`account_leagues`) - Leagues with previous versions support

### Blueprint Module

- **Blueprint Seasons** (`blueprint_seasons`) - Season blueprints
- **Blueprint Domains** (`blueprint_domains`) - Domain configurations
- **Blueprint Challenges** (`blueprint_challenges`) - Challenge templates
- **Blueprint Tournaments** (`blueprint_tournaments`) - Tournament configurations

### Calendar Module

- **Seasons** (`calendar_seasons`) - Active seasons
- **Stages** (`calendar_stages`) - Season stages
- **Rounds** (`calendar_rounds`) - Competition rounds with relevant users

### Entry Module

- **Challenges** (`entry_challenges`) - User challenges with fixture slots
- **Bets** (`entry_bets`) - User bets with previous versions support

### Group Module

- **Standings** (`group_standings`) - Group standings with history
- **Fixtures** (`group_fixtures`) - Group fixtures
- **Cursors** (`group_cursors`) - Cursor tracking for groups

### Real Module

- **Real Fixtures** (`real_fixtures`) - Real-world fixture data
- **Real Teams** (`real_teams`) - Team information
- **Real Players** (`real_players`) - Player data
- **Real Events** (`real_events`) - Match events
- **Real Channels** (`real_channels`) - Data channels

### Delivery Module

- **Notifications** (`delivery_notifications`) - Push notifications
- **Delivery Sessions** (`delivery_sessions`) - Delivery tracking
- **Live Activities** (`delivery_liveactivities`) - iOS Live Activities

### Timeline Module

- **Snapshots** (`timeline_snapshots`) - Timeline snapshots

## Key Differences from MongoDB

### Relationships

- MongoDB uses string references (`_user`, `_season`, etc.)
- PostgreSQL maintains the same string reference pattern for compatibility
- Use joins in queries when needed (Drizzle makes this easy)

### Embedded Documents

- MongoDB embedded documents → PostgreSQL JSONB columns
- Examples: `pushDevices`, `settings`, `fixtureSlots`, `rows`

### Arrays

- MongoDB arrays → PostgreSQL JSONB arrays for complex types
- Simple string arrays remain as TEXT[]

### Previous Versions

- MongoDB `prevs` array → PostgreSQL JSONB with `refPrevKey`
- Tables: `challenges`, `bets`, `leagues`

## CLI Query Commands

Run queries directly from the command line:

```bash
# List all users
bun run query:users

# Get user by ID or email
bun run src/query.ts user john.doe@example.com
bun run src/query.ts user user-john-doe

# Get user with their subscriptions
bun run src/query.ts user-subscriptions user-john-doe

# List all subscriptions
bun run query:subscriptions

# List all seasons
bun run query:seasons

# Search users
bun run src/query.ts search john

# Show statistics
bun run src/query.ts stats
```

## Query Functions

Import and use query functions in your code:

```typescript
import {
  getAllUsers,
  getUserBy,
  getUserWithSubscriptions,
  getAllSubscriptions,
  getAllSeasons,
  getUserBetsForRound,
  getChallengesForRound,
  getRealFixturesByDateRange,
  getStandingsForSeason,
  searchUsers,
  getUserStats,
} from './src/query'

// Get all active users
const users = await getAllUsers({ status: 'active', limit: 10 })

// Get user by email
const user = await getUserBy('email', 'john.doe@example.com')

// Search users
const results = await searchUsers('john')
```

## Drizzle Studio

Visual database browser:

```bash
bun run db:studio
```

Opens at `https://local.drizzle.studio`

## Docker Commands

```bash
# Start dev database
bun run docker:up

# Stop dev database
bun run docker:down

# View logs
bun run docker:logs

# Start production database
bun run docker:prod:up

# Stop production database
bun run docker:prod:down
```

## Database Management

```bash
# Generate migration files
bun run db:generate

# Run migrations
bun run db:migrate

# Push schema directly (no migrations)
bun run db:push

# Clear all data
bun run src/migrations/clear.ts

# Re-seed database
bun run db:seed
```

## Production Deployment

1. Copy `docker-compose.prod.yml` to your server
2. Create `.env` file with secure credentials:

```env
POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_secure_password
POSTGRES_DB=outs_prod
POSTGRES_PORT=5432
```

3. Start production container:

```bash
docker-compose -f docker-compose.prod.yml up -d
```

4. Run migrations:

```bash
DATABASE_URL=postgresql://user:pass@host:5432/db bun run db:push
```

## Publishing to npm

This package is designed to be published to npm as a shared schema library.

```bash
bun run publish:npm
```

This will:

1. Clean the dist directory
2. Build TypeScript declarations and JS files
3. Publish to npm with public access

### After publishing, update consuming packages:

```json
{
  "dependencies": {
    "@outs/postgres": "^1.0.0"
  }
}
```

## Using in Your Application

### Import all schemas (not recommended for production):

```typescript
import { users, subscriptions, leagues } from '@outs/postgres'
```

### Import specific schemas (recommended for tree-shaking):

```typescript
// Only import account schemas (much smaller bundle)
import { users, type User, type PushDevice } from '@outs/postgres/schemas/account'

// Only import calendar schemas
import { seasons, stages, rounds } from '@outs/postgres/schemas/calendar'

// Only import real data schemas
import { fixtures, teams, players } from '@outs/postgres/schemas/real'
```

### Available schema modules for tree-shaking:

- `@outs/postgres/schemas/account` - users, subscriptions, leagues
- `@outs/postgres/schemas/blueprint` - seasons, domains, challenges, tournaments
- `@outs/postgres/schemas/calendar` - seasons, stages, rounds
- `@outs/postgres/schemas/entry` - challenges, bets
- `@outs/postgres/schemas/group` - standings, fixtures, cursors
- `@outs/postgres/schemas/real` - fixtures, teams, players, events, channels
- `@outs/postgres/schemas/delivery` - notifications, sessions, live activities
- `@outs/postgres/schemas/timeline` - snapshots

### Example: Using in Cloudflare Workers

```typescript
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import { users } from '@outs/postgres/schemas/account'
import { eq } from 'drizzle-orm'

export default {
  async fetch(request: Request, env: Env) {
    const client = postgres(env.DATABASE_URL)
    const db = drizzle(client)

    const user = await db.select().from(users).where(eq(users.email, 'test@example.com')).limit(1)

    await client.end()
    return Response.json({ user })
  },
}
```

### Example: Local database queries

```typescript
import { db } from '@outs/postgres'
import { users, eq } from '@outs/postgres'

// Query users
const allUsers = await db.select().from(users)

// Query with conditions
const user = await db.select().from(users).where(eq(users.email, 'john@example.com'))

// Insert
await db.insert(users).values({
  id: 'user-new',
  url: '/api/users/user-new',
  email: 'new@example.com',
  password: hashedPassword,
})

// Update
await db.update(users).set({ name: 'New Name' }).where(eq(users.id, 'user-123'))

// Delete
await db.delete(users).where(eq(users.id, 'user-123'))
```

## Type Safety

All tables have type-safe insert and select types:

```typescript
import type { User, NewUser } from '@outs/postgres'

// Select type (includes all fields including defaults)
const user: User = await db.select().from(users).where(eq(users.id, 'user-1'))

// Insert type (only required fields)
const newUser: NewUser = {
  id: 'user-new',
  url: '/api/users/user-new',
  email: 'new@example.com',
}
```

## Resolving type issues when importing from @outs-tand-ing/postgres for the PG/Drizzle schemas (best practices going forward)

1. Import drizzle utilities directly: Always use import { eq, and, or } from 'drizzle-orm' in your ops code
2. Import schemas from postgres package: import { type Round, type Season } from '@outs-tand-ing/postgres'
3. Keep versions in sync: Both packages use exact version 0.45.1 - maintain this consistency
4. After updating postgres package: Run bun install in ops to pick up changes

## License

MIT