D&D 5e Campaign Manager

A comprehensive web application for managing D&D 5e campaigns, characters, and gameplay sessions with integrated world mapping and PostgreSQL database support.

Features

Connected Dashboards

• Player dashboard loads the signed-in user's characters and campaign membership from /api/users/:id/* and /api/campaigns/public, failing visibly if the backend is unreachable.
• DM dashboard consumes the same live data sources for campaign administration.
• Admin dashboard calls /api/admin/metrics; a valid admin session is required and the UI now reflects errors when metrics are unavailable.

Character & Campaign Management

• Character sheets, spellbooks, and inventory panels read and write directly to the PostgreSQL backend via the shared database helpers.
• Campaign join/leave actions call the live Express endpoints and refresh UI state from server responses.
• Session manager uses /api/campaigns/:id/sessions for lifecycle operations and surfaces backend validation errors.

Messaging & Real-Time Hooks

• Chat components persist party messages and dice rolls through /api/campaigns/:id/messages and respect the configured WebSocket host.
• Standalone dice and exploration utilities are intentionally disabled with FeatureUnavailable notices until backed services ship, preventing dummy data from reappearing.

Live Narrative Console

• The in-game "Narratives" panel lets DMs and co-DMs call /api/campaigns/:campaignId/narratives/* endpoints for DM narration, scene descriptions, NPC dialogue, action outcomes, and quest outlines using the active campaign/session context.
• Responses display verbatim with provider metadata, cache indicators, and surfaced backend errors—there is no local fallback or synthetic prose when the LLM service is unavailable.

LLM Monitoring & Cache Governance

• /api/admin/llm/metrics streams live request counters, provider latency averages, token totals, and the latest 25 generation attempts; the admin dashboard now renders this data on the LLM Workloads tab.
• /api/admin/llm/cache exposes cache entries (provider/model, TTL, timestamps) with destructive controls for clearing all entries or a specific key—no dummy cache rows are ever returned.

Mapping & Spatial Data

• OpenLayers map viewer loads world metadata and spatial layers from /api/maps/world and related PostGIS-powered routes.
• Campaign location overlays rely on live responses; failures present actionable error states instead of silent fallbacks.

Operational Transparency

• /api/health exposes basic pool statistics for the database server and is covered by automated smoke tests (tests/live-api.integration.test.js).
• UI-level error boundaries and toasts communicate authentication issues, missing configuration, and backend outages without fabricating success states.

Technology Stack

• Frontend: React + TypeScript + Tailwind CSS v4
• Backend: Express.js + PostgreSQL 17 + PostGIS
• Database Architecture: UUID primary keys, JSONB fields, field mapping utilities
• Real-time Features: Polling-based updates, database health monitoring
• Error Handling: Centralized error boundaries and standardized patterns
• Mapping: OpenLayers with spatial data support
• UI Components: ShadCN/UI component library

Phase 2 Architecture

The application now features a complete database integration with:

API Endpoints

• Health Monitoring: /api/health - Database connection status and metrics
• Characters: Full CRUD operations with validation and ownership checks
• Campaigns: Campaign lifecycle management with player membership
• Chat Messages: Real-time messaging with character-based communication
• User Management: Profile management and authentication

Database Features

• Field Mapping: Automatic snake_case ↔ camelCase conversion
• JSONB Storage: Flexible storage for D&D-specific data structures
• Connection Pooling: Optimized database connections (max 20, timeout 2s)
• Health Monitoring: Real-time connection status with retry logic
• Transaction Safety: Proper error handling and rollback mechanisms

Frontend Architecture

• Live Data Sync: Character sheet updates automatically reflect inventory/spell changes
• Error Boundaries: Application-wide error catching with user-friendly recovery
• Loading States: Consistent loading indicators across all components
• Offline Mode: Graceful degradation when database is unavailable
• Health Indicators: Real-time database status in the UI

Quick Start

Prerequisites

• Node.js 18+
• PostgreSQL 17 with PostGIS extension
• Git

Fast Setup

1. Clone the repository

   git clone <repository-url>
   cd questables

2. Install dependencies

   npm install

3. Database Setup

   📖 For detailed setup instructions, see DATABASE_SETUP.md

   Quick setup:

   # Create database
   createdb dnd_app
   
   # Import schema
   psql -d dnd_app -f database/schema.sql
   
   # Create environment file
   cp .env.example .env.local
   # Edit .env.local with your database credentials

4. Start the application

   # Start database server (backend)
   npm run db:server
   
   # In another terminal, start frontend
   npm run dev

The application will be available at:

• Frontend: http://localhost:3000
• Backend API: http://localhost:5101
• Health Check: http://localhost:5101/api/health

⚠️ Important Notes

• Database Required: This application requires a properly configured PostgreSQL database
• No Demo Mode: There are no fallback modes - database setup is mandatory
• Environment Variables: Must be configured in .env.local (not tracked by git)
• Health Monitoring: Check the database status indicator in the UI for connection health

Database Setup

IMPORTANT: This application requires a properly configured database to function. There are no demo accounts or fallback modes - the application will fail if the database is not properly set up.

1. Create PostgreSQL Database

   createdb dnd_app

2. Install PostGIS Extension (for spatial features)

   psql dnd_app -c "CREATE EXTENSION IF NOT EXISTS postgis;"

3. Configure Environment Variables

   cp .env.example .env
   # Edit .env with your database configuration

The application will automatically create required tables on first startup.

Database Setup

The application automatically creates basic database tables on first run. For full spatial mapping features, ensure PostGIS is installed:

-- Connect to your database and run:
CREATE EXTENSION IF NOT EXISTS postgis;

Environment Configuration

Required Environment Variables:

Create a .env file in the project root (copy from .env.example):

# REQUIRED: Database Server URL (frontend to backend communication)
VITE_DATABASE_SERVER_URL=http://localhost:5101

# PostgreSQL Connection (backend)
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_NAME=dnd_app
DATABASE_USER=postgres
DATABASE_PASSWORD=your_password
DATABASE_SSL=false

# Server Configuration  
DATABASE_SERVER_PORT=5101
FRONTEND_URL=http://localhost:3000

# Optional HTTPS configuration
# DATABASE_SERVER_USE_TLS=true
# DATABASE_SERVER_TLS_CERT=
# DATABASE_SERVER_TLS_KEY=
# DATABASE_SERVER_PUBLIC_HOST=
# VITE_DATABASE_SERVER_URL=
# When enabling HTTPS for the frontend dev server, update FRONTEND_URL and set:
# FRONTEND_URL=
# DEV_SERVER_USE_TLS=
# DEV_SERVER_TLS_CERT=
# DEV_SERVER_TLS_KEY=

### LLM Provider Configuration

The Enhanced LLM Service runs via a provider abstraction layer. Production and development environments currently target the on-prem Ollama instance at `http://192.168.1.34` using the `qwen3:8b` model. Configure the following variables in `.env.local`:

```env
LLM_PROVIDER=ollama
LLM_OLLAMA_HOST=http://<your-ollama-host>:11434
LLM_OLLAMA_MODEL=qwen3:8b
# Optional overrides
# LLM_OLLAMA_API_KEY= # set only if the Ollama host requires bearer auth
# LLM_OLLAMA_TIMEOUT_MS=60000
# LLM_OLLAMA_TEMPERATURE=0.7
# LLM_OLLAMA_TOP_P=0.9

The backend refuses to serve narrative requests when the provider bootstrap fails. Use the health helper below to verify connectivity before running integration tests:

LLM_OLLAMA_MODEL=qwen3:8b node --input-type=module <<'NODE'
import { initializeLLMServiceFromEnv } from './server/llm/index.js';

const { registry } = initializeLLMServiceFromEnv(process.env);
const health = await registry.get('ollama').checkHealth();
console.log(health);
NODE

If the host is unreachable, the script surfaces the error instead of silently returning demo content—resolve connectivity before depending on narrative flows.

Provider Registry Table

Provider definitions can now be stored in public.llm_providers. Insert a row for each adapter/model you want available at runtime:

INSERT INTO public.llm_providers (name, adapter, host, model, default_provider)
VALUES ('ollama', 'ollama', 'http://localhost:11434', 'qwen3:8b', true)
ON CONFLICT (name) DO UPDATE
SET host = EXCLUDED.host,
    model = EXCLUDED.model,
    default_provider = EXCLUDED.default_provider,
    updated_at = NOW();

default_provider marks the provider used when callers omit overrides. Multiple providers can be registered; the admin-only endpoint GET /api/admin/llm/providers returns their health status so the UI can surface outages without guessing.

Narrative API Endpoints

With the provider layer online, the backend now exposes authenticated endpoints that stream narrative requests to the Enhanced LLM Service:

Method	Path	Description	Access
POST	/api/campaigns/:campaignId/narratives/dm	Generate DM narration summarising the latest events.	DM / co-DM / admin
POST	/api/campaigns/:campaignId/narratives/scene	Produce environmental descriptions for the active location.	DM / co-DM / admin
POST	/api/campaigns/:campaignId/narratives/npc	Return NPC dialogue and log the interaction in npc_memories.	DM / co-DM / admin
POST	/api/campaigns/:campaignId/narratives/action	Narrate the outcome of a specific action.	Any authenticated participant
POST	/api/campaigns/:campaignId/narratives/quest	Draft a quest outline using live campaign data.	DM / co-DM / admin

Each call persists a row in llm_narratives (prompt, response, metrics, cache state). NPC dialogue requests also append to npc_memories and adjust npc_relationships atomically. Provider failures surface as 502/503 responses—no fallback copy is returned.

NPC dialogue requests derive a memory summary from the generated prose when the caller does not supply one, estimate sentiment via keyword heuristics, and clamp trust adjustments between -10 and 10. Optional interaction payloads let DMs override the summary, sentiment, trust delta, tags, and relationship deltas explicitly.

The application will fail to start if VITE_DATABASE_SERVER_URL is not set. Player and DM dashboards now surface an explicit configuration error rather than falling back to dummy data when this variable is missing.

Development Scripts

# Start frontend only
npm run dev

# Start backend only  
npm run db:server

# Start both frontend and backend
npm run dev:local

# Set up database server dependencies
npm run db:setup

# Build for production
npm run build

Testing

• Run the live smoke suite once the backend is accessible:

  LIVE_API_BASE_URL= \
  LIVE_API_ADMIN_EMAIL="${DEFAULT_ADMIN_EMAIL:-admin@questables.example}" \
  LIVE_API_ADMIN_PASSWORD="${DEFAULT_ADMIN_PASSWORD:-changeme}" \
  npm test -- --runTestsByPath tests/live-api.integration.test.js

• If you seed different admin credentials, set LIVE_API_ADMIN_EMAIL and LIVE_API_ADMIN_PASSWORD to match (the suite also respects the DEFAULT_ADMIN_* values produced by npm run db:setup).

Project Structure

├── components/          # React components
│   ├── ui/             # ShadCN UI components
│   ├── openlayers-map.tsx
│   ├── player-dashboard.tsx
│   └── ...
├── utils/
│   └── database/       # PostgreSQL client and helpers
├── server/             # Express.js backend
│   ├── database-server.js
│   └── setup-database.js
├── styles/
│   └── globals.css     # Tailwind v4 configuration
└── database/
    └── schema.sql      # Full database schema

Mapping Features

The application supports:

• World Maps: Import from Azgaar's Fantasy Map Generator
• Spatial Queries: PostGIS-powered location searches
• Interactive Layers: Cities, roads, rivers, terrain, markers
• Campaign Integration: Link campaign locations to world positions

Authentication

• Database Auth: Accounts are created through the live API and stored in PostgreSQL.
• No Demo Accounts: The app does not provide fallback users; resolve backend issues instead of fabricating access.
• Role-based Access: Player, DM, and Admin permission levels

Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit changes (git commit -m 'Add amazing feature')
4. Push to branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Troubleshooting

Database Connection Issues:

• Ensure PostgreSQL is running
• Check database credentials in .env
• Verify database exists: createdb dnd_app

PostGIS Not Available:

• Spatial features will be limited but app will still work
• Install PostGIS: apt-get install postgresql-postgis (Ubuntu)

Environment Variables:

• Ensure .env file exists and contains required variables
• Restart server after changing environment variables

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• ShadCN/UI for the component library
• OpenLayers for mapping capabilities
• Azgaar's Fantasy Map Generator for world map support
• D&D 5e SRD for game mechanics reference