Project Overview - jra3/mulm GitHub Wiki

Project Overview

This document provides a high-level overview of the Mulm Breeder Awards Program platform architecture, technology stack, and design decisions.

What is Mulm?

Mulm is a web application for managing aquarium society Breeder Awards Programs (BAP), Horticultural Awards Programs (HAP), and Coral Awards Programs (CAP).

Production: https://bap.basny.org Repository: https://github.com/jra3/mulm

Core Features

Member Management - User registration, authentication, profiles
Submission System - Track breeding achievements with photos
Approval Workflow - Multi-stage admin review with witness verification
Awards & Levels - Automatic level advancement and specialty awards
Species Catalog - Searchable database of fish, plants, and corals
Activity Feed - Real-time member achievements
Admin Tools - Queues, member management, reporting

Architecture Overview

High-Level Architecture

┌─────────────────────────────────────────────────────────────┐
│                         User Browser                         │
│                    (HTMX + Tailwind CSS)                    │
└────────────────────┬────────────────────────────────────────┘
                     │ HTTPS (nginx)
┌────────────────────▼────────────────────────────────────────┐
│                      Nginx (Reverse Proxy)                   │
│           - SSL Termination (Let's Encrypt)                 │
│           - Rate Limiting                                    │
│           - Static File Serving                              │
└────────────────────┬────────────────────────────────────────┘
                     │ HTTP (port 4200)
┌────────────────────▼────────────────────────────────────────┐
│                    Node.js / Express.js                      │
│                                                              │
│  ┌──────────────┐  ┌──────────────┐  ┌─────────────────┐  │
│  │   Routes     │  │  Business    │  │   Utilities     │  │
│  │  (Express)   │──│    Logic     │──│  (Email, R2)    │  │
│  └──────────────┘  └──────────────┘  └─────────────────┘  │
│                           │                                  │
│  ┌──────────────┐  ┌──────▼──────┐  ┌─────────────────┐  │
│  │  Session     │  │  Database   │  │   Validation    │  │
│  │  Manager     │  │   Layer     │  │   (Zod)         │  │
│  └──────────────┘  └──────┬──────┘  └─────────────────┘  │
└────────────────────────────┼────────────────────────────────┘
                             │
              ┌──────────────┴──────────────┐
              │                             │
      ┌───────▼────────┐          ┌────────▼────────┐
      │  SQLite (DB)   │          │  Cloudflare R2  │
      │  - Members     │          │  - Images       │
      │  - Submissions │          │  - Thumbnails   │
      │  - Species     │          └─────────────────┘
      └────────────────┘

Technology Stack

Layer	Technology	Purpose
Frontend	Pug templates	Server-side rendered HTML
	HTMX	Dynamic interactions without JavaScript framework
	Tailwind CSS	Utility-first styling
	PostCSS	CSS processing and optimization
Backend	Node.js 20.x	Runtime environment
	Express.js 5.x	Web application framework
	TypeScript	Type-safe JavaScript
Database	SQLite 3.x	Embedded relational database
	Node sqlite library	Async database interface
Storage	Cloudflare R2	S3-compatible object storage for images
Image Processing	Sharp (libvips)	High-performance image processing
Validation	Zod	Schema validation for forms
Authentication	Cookie sessions	Session-based authentication
	OAuth 2.0	Google sign-in
	scrypt	Password hashing
Email	Nodemailer	SMTP email sending
Testing	Node.js test runner	Native test framework
	tsx	TypeScript test execution
Infrastructure	AWS EC2	Application hosting
	AWS EBS	Persistent storage
	Docker Compose	Container orchestration
	nginx	Reverse proxy and SSL termination
	Let's Encrypt	SSL certificates
	AWS CDK	Infrastructure as Code

Design Decisions

Why SQLite?

Chosen over: PostgreSQL, MySQL, MongoDB

Reasons:

✅ Simplicity - Single file, no separate server process
✅ Performance - Excellent for read-heavy workloads (< 100K requests/day)
✅ Reliability - Atomic commits, proven stability
✅ Low overhead - Minimal resource usage
✅ Easy backup - Single file to back up
✅ Cost - No separate database hosting required

Limitations:

❌ Single writer at a time (not an issue for our traffic)
❌ No built-in replication (EBS snapshots provide backups)
❌ Limited concurrency (acceptable for our use case)

When to migrate: If traffic exceeds 100K+ daily requests or need multi-region.

Why Pug + HTMX?

Chosen over: React, Vue, SPA frameworks

Reasons:

✅ Server-side rendering - Better SEO, faster initial load
✅ Simplicity - No build complexity for frontend
✅ Progressive enhancement - Works without JavaScript
✅ HTMX - Dynamic behavior without heavy JavaScript
✅ Less complexity - No API layer needed for most features
✅ Smaller bundle - Minimal JavaScript sent to client

Trade-offs:

❌ Less rich interactions than SPA
❌ Full page navigation (mitigated by HTMX)
❌ Smaller ecosystem than React

Fits use case: Form-heavy CRUD application, not real-time collaborative editing.

Why TypeScript?

Chosen over: Plain JavaScript

Reasons:

✅ Type safety - Catch errors at compile time
✅ Better IDE support - Autocomplete, refactoring
✅ Self-documenting - Types serve as inline documentation
✅ Safer refactoring - Compiler catches breaking changes
✅ Team scalability - Easier for new developers

Configuration: Strict mode enabled for maximum type safety.

Why Cloudflare R2?

Chosen over: AWS S3, local storage

Reasons:

✅ No egress fees - S3-compatible but cheaper
✅ S3 API - Easy to use AWS SDK
✅ CDN integration - Fast global delivery
✅ Reliability - Enterprise-grade storage

Cost comparison:

AWS S3: $0.023/GB storage + egress fees
Cloudflare R2: $0.015/GB storage + $0 egress

Why Docker Compose?

Chosen over: Kubernetes, bare metal, serverless

Reasons:

✅ Right-sized - Not overengineered for single-server app
✅ Simple - Easy to understand and maintain
✅ Portable - Same config works locally and in production
✅ Multi-container - App, nginx, certbot isolated
✅ Easy rollback - Rebuild previous version quickly

When to migrate: If traffic requires multiple servers or auto-scaling.

Application Structure

Request Flow

1. Browser → HTTPS Request
2. nginx → Rate limiting, SSL termination
3. nginx → Proxy to Express (port 4200)
4. Express → Route matching
5. Route Handler → Database query
6. Database → Return data
7. Route Handler → Render Pug template
8. Express → Return HTML
9. nginx → Return to browser
10. Browser → Render page

Database Layer Pattern

Dual connection architecture:

// Read-only connection (for SELECT queries)
const readOnlyConn = await open({
  filename: config.databaseFile,
  mode: sqlite3.OPEN_READONLY,
});

// Write connection (for INSERT/UPDATE/DELETE)
const writeConn = await open({
  filename: config.databaseFile,
  mode: sqlite3.OPEN_READWRITE,
});

// Automatically select correct connection
function db(write = false) {
  return write ? writeConn : readOnlyConn;
}

Benefits:

Prevents accidental writes from read operations
Better error messages (write fails on read-only conn)
Clear intent in code

Transaction pattern:

await withTransaction(async (db) => {
  // Multiple operations executed atomically
  await db.run('INSERT INTO members ...');
  await db.run('UPDATE submissions ...');
  // Auto-commit on success, auto-rollback on error
});

Session Management

Cookie-based sessions:

// Stored in database (sessions table)
{
  session_id: "random-uuid",
  member_id: 123,
  expires_on: "2025-11-01T00:00:00Z"
}

// Cookie sent to browser
Set-Cookie: session_id=random-uuid; HttpOnly; Secure; SameSite=Strict

Security features:

HttpOnly - Prevents JavaScript access (XSS protection)
Secure - HTTPS only (production)
SameSite=Strict - CSRF protection
Database-backed - Server-side session storage

Form Validation

Zod schemas define all forms:

// Define schema once
const submissionSchema = z.object({
  species_common_name: z.string().min(2),
  species_latin_name: z.string().min(3),
  reproduction_date: z.string().datetime(),
  // ... other fields
});

// Use for validation
const result = submissionSchema.safeParse(req.body);
if (!result.success) {
  // Show errors to user
  res.render('submit', { errors: result.error });
}

Benefits:

Single source of truth for validation rules
Type inference (TypeScript types from schemas)
Runtime validation
Clear error messages

Key Workflows

Submission Workflow

Member Creates Draft
       ↓
Fills in Species Info
       ↓
Fills in Tank Parameters
       ↓
Uploads Photos (optional)
       ↓
Adds Witness (if required)
       ↓
Submits for Approval
       ↓
┌──────────────────────┐
│  Witness Required?   │
└──────┬──────────┬────┘
       │Yes       │No
       ↓          ↓
Witness Queue    Approval Queue
       ↓          ↓
Witness Confirms │
       ↓          │
    ┌──▼──────────▼──┐
    │ Admin Reviews   │
    └──┬──────────┬──┘
       │Approve   │Deny/Changes
       ↓          ↓
  Award Points   Return to Member
       ↓
  Check Levels
       ↓
  Check Awards
       ↓
  Send Email
       ↓
  Activity Feed

Authentication Flow

User Visits Site
       ↓
Click "Sign In"
       ↓
┌──────────────────────┐
│  Email/Password OR   │
│  Google OAuth        │
└──────┬──────────┬────┘
       │          │
       ↓          ↓
Password Login   OAuth Flow
       ↓          ↓
Verify scrypt    Get Google Profile
       ↓          ↓
    ┌──▼──────────▼──┐
    │ Create Session │
    └────────┬────────┘
             ↓
      Set Cookie
             ↓
      Redirect Home
             ↓
    Authenticated!

Database Schema Overview

Core Tables

Table	Purpose	Key Relationships
members	User accounts	Referenced by most tables
submissions	Breeding records	FK to members, species_name
species_name_group	Canonical species	Grouping synonyms
species_name	Species variants	FK to species_name_group
awards	Member awards	FK to members
tank_presets	Saved tank configs	FK to members
sessions	Active sessions	FK to members
auth_codes	Password reset codes	FK to members
activity_feed	Public activity log	FK to members
submission_notes	Admin notes	FK to submissions, members

See: Database Schema for full ER diagrams.

Security Architecture

Defense in Depth

Layer 1: Network

Nginx rate limiting (30 req/sec general, 5 req/sec uploads)
Firewall (AWS Security Group)
DDoS protection (Cloudflare if enabled)

Layer 2: Transport

HTTPS only (Let's Encrypt TLS 1.2+)
HSTS headers
Secure cookies

Layer 3: Application

Input validation (Zod schemas)
SQL injection prevention (prepared statements)
XSS prevention (Pug auto-escaping)
CSRF protection (SameSite cookies)

Layer 4: Data

Password hashing (scrypt)
Config file permissions (600, owner-only)
Database backups encrypted
EXIF stripping from uploads

See: Security Overview for complete security posture.

Performance Characteristics

Benchmarks

Hardware: AWS t3.micro (2 vCPU, 1GB RAM)

Operation	Response Time	Notes
Homepage	< 100ms	Static + activity feed
Member profile	< 150ms	DB query + rendering
Submission form	< 100ms	Cached tank presets
Search API	< 50ms	Indexed species search
Image upload	2-5s	Processing + R2 upload
Admin queue	< 200ms	Filtered submissions

Bottlenecks

Current:

Single SQLite writer (acceptable for current traffic)
Image processing CPU-bound (Sharp is fast enough)
R2 upload network latency (5-10s for 5MB image)

Future optimizations if needed:

Redis for session storage
CDN for static assets
Image upload to S3 Multipart
Database connection pooling

Deployment Architecture

Production Environment

AWS EC2 Instance (t3.micro)
├── Docker Compose
│   ├── basny-app (Node.js)
│   ├── basny-nginx (Reverse proxy)
│   └── basny-certbot (SSL renewal)
│
├── /opt/basny (Application code)
│   └── Git repository
│
└── /mnt/basny-data (EBS volume)
    ├── app/
    │   ├── database/database.db
    │   └── config/config.production.json
    └── nginx/
        ├── certs/ (SSL certificates)
        └── logs/

CI/CD Pipeline

Currently: Manual deployment via SSH

Process:

Push code to GitHub
SSH to production
git pull latest code
docker-compose up -d --build
Monitor logs

Future: GitHub Actions for automated testing and deployment.

Testing Strategy

Test Coverage

Unit Tests:

Utilities (waiting period, status, image processing)
Form validation
Level calculation

Integration Tests:

Database queries
Species search
Typeahead API

End-to-End Tests:

Witness workflow
Submission approval
Upload transactions

Template Tests:

Pug rendering
Syntax validation

Current Status: 100% passing (all implemented tests)

See: Testing Guide

Development Workflow

Local Development

# Start dev server
npm run dev

# Server runs on http://localhost:4200
# Hot reload enabled for TypeScript and CSS
# Database auto-migrates on startup

Code Organization

src/
├── routes/          # HTTP route handlers
├── db/              # Database layer (queries)
├── forms/           # Zod validation schemas
├── utils/           # Helper functions
├── views/           # Pug templates
├── types/           # TypeScript type definitions
└── __tests__/       # Test files

Principle: Separation of concerns

Routes handle HTTP (thin layer)
Database layer handles queries (reusable)
Forms handle validation (single source of truth)
Views handle presentation (no logic)

Roadmap & Future Enhancements

Potential Improvements

Short-term (3-6 months):

Automated daily database backups to S3
GitHub Actions CI/CD pipeline
Email digest for admins (weekly summary)
Improved mobile responsive design

Medium-term (6-12 months):

Member-to-member messaging
Advanced search filters
Data export (CSV, PDF reports)
Multi-language support

Long-term (1+ years):

Mobile app (if needed)
Multi-club support
Live chat for members
AI-assisted species identification

When to migrate tech:

SQLite → PostgreSQL: If traffic > 100K req/day or need replication
Single server → Multi-server: If need redundancy or auto-scaling
Docker Compose → Kubernetes: If managing multiple applications

Project Overview - jra3/mulm GitHub Wiki

Project Overview

What is Mulm?

Core Features

Architecture Overview

High-Level Architecture

Technology Stack

Design Decisions

Why SQLite?

Why Pug + HTMX?

Why TypeScript?

Why Cloudflare R2?

Why Docker Compose?

Application Structure

Request Flow

Database Layer Pattern

Session Management

Form Validation

Key Workflows

Submission Workflow

Authentication Flow

Database Schema Overview

Core Tables

Security Architecture

Defense in Depth

Performance Characteristics

Benchmarks

Bottlenecks

Deployment Architecture

Production Environment

CI/CD Pipeline

Testing Strategy

Test Coverage

Development Workflow

Local Development

Code Organization

Roadmap & Future Enhancements

Potential Improvements

Related Documentation