Database Architecture

DATABASE ARCHITECTURE - BLACKTRAILS PLATFORM

Version: 2.1 Date: 18 Dicembre 2025 Author: BlackTrails Team Status: Production Ready

---

📋 TABLE OF CONTENTS

1. Overview
2. Cluster Information
3. Branch Strategy
4. Schema Architecture
5. Connection Strings
6. Library System (NEW)
7. Naming Conventions
8. Common Commands

---

🎯 OVERVIEW

BlackTrails Platform uses a single PostgreSQL cluster with multi-schema architecture hosted on Neon.tech.

Key Principles:

• ✅ One Cluster: blacktrails (patient-queen-89181819)
• ✅ Two Branches: main (production), dev (development)
• ✅ Eight Schemas: Logical separation by domain
• ✅ pgvector: Enabled for RAG/embeddings (library schema)

Benefits:

• Cost Efficiency: Single cluster instead of 5 separate projects
• Logical Separation: Clear domain boundaries via schemas
• Scalability: Branch-based dev/prod isolation
• Performance: Optimized indexes per schema

---

🗄️ CLUSTER INFORMATION

Neon Project Details

Property	Value
Project Name	blacktrails
Project ID	patient-queen-89181819
Region	AWS EU-Central-1
Platform	Neon.tech (Serverless PostgreSQL)
PostgreSQL Version	17
Storage	~50MB (scalable)
Compute	Autoscaling (0.25-2 CU)

Resource Limits (Free Tier)

Resource	Limit
Storage	512 MB per branch
Compute	Shared, autoscaling
Active Time	Unlimited (no sleep on free tier with activity)
Branches	Unlimited
History Retention	6 hours

---

🌿 BRANCH STRATEGY

Main Branch (Production)

Branch: main
Endpoint: ep-fancy-dust-ag0l0r9n
Purpose: Production environment
Access: Read/Write (protected)
Backup: Daily automatic snapshots

Usage:

• Production application (BLACKTRAILS-PLATFORM)
• Public-facing IN-1 app
• Live data (users, haikus, bookings)

Dev Branch (Development)

Branch: dev
Endpoint: ep-weathered-scene-ag4iuf4p
Purpose: Development/testing environment
Access: Read/Write (developers)
Reset: Can be reset from main snapshot

Usage:

• Local development
• Feature testing
• Migration dry-runs
• Schema changes validation

Workflow

Development → Merge → Main (Production)
    ↓            ↓            ↓
  Tests      Approval     Deploy

---

🏗️ SCHEMA ARCHITECTURE

Overview

blacktrails (database)
├── auth          # Authentication & Authorization
├── in1           # IN-1 Haiku App
├── elements      # Booking System
├── finance       # Financial Transactions
├── library       # Knowledge Base (RAG) ✨ NEW
├── ai            # AI Services (future)
├── system        # System Logs & Audits
└── organization  # Multi-tenant (future)

---

🔐 SCHEMA: auth

Purpose: User authentication, sessions, roles, tokens

Tables

auth.users

CREATE TABLE auth.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    email_verified BOOLEAN DEFAULT FALSE,
    hashed_password VARCHAR(255),
    name VARCHAR(255),
    avatar_url TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_email ON auth.users(email);

auth.sessions

CREATE TABLE auth.sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_sessions_user_id ON auth.sessions(user_id);
CREATE INDEX idx_sessions_expires ON auth.sessions(expires_at);

auth.roles

CREATE TABLE auth.roles (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    product_code VARCHAR(50) NOT NULL,
    role VARCHAR(50) NOT NULL,
    granted_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(user_id, product_code)
);

CREATE INDEX idx_roles_user ON auth.roles(user_id);
CREATE INDEX idx_roles_product ON auth.roles(product_code);

---

🌲 SCHEMA: in1

Purpose: IN-1 Haiku generation app (conversational AI)

Tables

in1.haikus

CREATE TABLE in1.haikus (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    session_id VARCHAR(255),
    prompt_text TEXT NOT NULL,
    haiku_text TEXT NOT NULL,
    emotion VARCHAR(50),
    language VARCHAR(10) DEFAULT 'it',
    tree_planted BOOLEAN DEFAULT FALSE,
    tree_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_haikus_user ON in1.haikus(user_id);
CREATE INDEX idx_haikus_session ON in1.haikus(session_id);
CREATE INDEX idx_haikus_language ON in1.haikus(language);
CREATE INDEX idx_haikus_emotion ON in1.haikus(emotion);
CREATE INDEX idx_haikus_created_at ON in1.haikus(created_at DESC);

Columns:

• id: Primary key (auto-increment)
• user_id: Nullable (anonymous users allowed)
• session_id: Session identifier (crypto.randomBytes)
• prompt_text: Full user conversation (concatenated)
• haiku_text: Generated haiku (3 lines)
• emotion: Detected emotion (paura, gioia, tristezza, etc.)
• language: it (Italian) or en (English)
• tree_planted: If tree was planted for this haiku
• tree_id: External tree planting service ID

---

📚 SCHEMA: library ✨ NEW

Purpose: Knowledge base, RAG system, document management

Extensions Required

CREATE EXTENSION IF NOT EXISTS vector; -- pgvector for embeddings

Tables

library.documents

CREATE TABLE library.documents (
    id SERIAL PRIMARY KEY,
    slug VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(500) NOT NULL,
    category VARCHAR(100), -- filosofia, progetti, algoritmi, legal, strategie, finanza
    content TEXT NOT NULL,
    content_hash VARCHAR(64) NOT NULL, -- MD5 hash for change detection
    public BOOLEAN DEFAULT FALSE,
    author VARCHAR(255),
    tags TEXT[],
    metadata JSONB, -- frontmatter fields
    language VARCHAR(5) DEFAULT 'it', -- ✨ NEW: it, en, es, fr, de
    translation_id VARCHAR(100), -- ✨ NEW: links translated documents
    last_updated TIMESTAMP DEFAULT NOW(),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_documents_slug ON library.documents(slug);
CREATE INDEX idx_documents_category ON library.documents(category);
CREATE INDEX idx_documents_public ON library.documents(public);
CREATE INDEX idx_documents_hash ON library.documents(content_hash);
CREATE INDEX idx_documents_language ON library.documents(language); -- ✨ NEW
CREATE INDEX idx_documents_translation_id ON library.documents(translation_id); -- ✨ NEW

Categories Implemented:

• filosofia - Philosophy documents (Rhama, paradigm)
• progetti - Project documentation
• algoritmi - Technical algorithms
• legal - Legal documents (terms, privacy)
• strategie - Business strategies
• finanza - Financial documents

Recent Updates (18 Dec 2025):

• ✅ Added language column for i18n support
• ✅ Added translation_id for linking translated versions
• ✅ Implemented sync script: npm run sync-library
• ✅ Auto-detection from frontmatter: lang: en

library.embeddings

CREATE TABLE library.embeddings (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES library.documents(id) ON DELETE CASCADE,
    chunk_index INTEGER NOT NULL, -- Position in document
    chunk_text TEXT NOT NULL,
    embedding vector(1536), -- OpenAI text-embedding-ada-002 (1536 dimensions)
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_embeddings_document ON library.embeddings(document_id);
-- Vector similarity index (IVFFlat for cosine similarity)
CREATE INDEX idx_embeddings_vector ON library.embeddings
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

RAG Query Example:

-- Find similar documents by embedding
SELECT
    d.title,
    d.slug,
    d.language,
    e.chunk_text,
    1 - (e.embedding <=> $1::vector) AS similarity
FROM library.embeddings e
JOIN library.documents d ON d.id = e.document_id
WHERE d.public = true
  AND d.language = 'it'  -- Filter by language
ORDER BY e.embedding <=> $1::vector
LIMIT 5;

---

🏠 SCHEMA: elements

Purpose: Room booking system (hotels, B&Bs)

Tables

elements.rooms

CREATE TABLE elements.rooms (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    max_guests INTEGER DEFAULT 2,
    price_per_night DECIMAL(10, 2) NOT NULL,
    amenities JSONB,
    images TEXT[], -- Array of image URLs
    available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_rooms_available ON elements.rooms(available);

elements.bookings

CREATE TABLE elements.bookings (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    room_id INTEGER REFERENCES elements.rooms(id) ON DELETE CASCADE,
    guest_name VARCHAR(255) NOT NULL,
    guest_email VARCHAR(255) NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    guests INTEGER DEFAULT 1,
    total_price DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending', -- pending, confirmed, cancelled
    notes TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_bookings_user ON elements.bookings(user_id);
CREATE INDEX idx_bookings_room ON elements.bookings(room_id);
CREATE INDEX idx_bookings_dates ON elements.bookings(check_in, check_out);
CREATE INDEX idx_bookings_status ON elements.bookings(status);

---

💰 SCHEMA: finance

Purpose: Financial transactions, invoices, Stripe logs

Tables

finance.transactions

CREATE TABLE finance.transactions (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    booking_id INTEGER REFERENCES elements.bookings(id) ON DELETE SET NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    type VARCHAR(50) NOT NULL, -- payment, refund, payout
    status VARCHAR(50) DEFAULT 'pending', -- pending, completed, failed
    payment_method VARCHAR(50), -- card, bank_transfer, cash
    stripe_payment_intent_id VARCHAR(255),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_transactions_user ON finance.transactions(user_id);
CREATE INDEX idx_transactions_booking ON finance.transactions(booking_id);
CREATE INDEX idx_transactions_status ON finance.transactions(status);
CREATE INDEX idx_transactions_created ON finance.transactions(created_at DESC);

---

🔌 CONNECTION STRINGS

Template (Replace Placeholders)

# Main Branch (Production)
DATABASE_URL=postgresql://[user]:[password]@ep-fancy-dust-ag0l0r9n-pooler.eu-central-1.aws.neon.tech/blacktrails?sslmode=require

# Dev Branch
DATABASE_URL_DEV=postgresql://[user]:[password]@ep-weathered-scene-ag4iuf4p-pooler.eu-central-1.aws.neon.tech/blacktrails?sslmode=require

Environment Variables

.env

DATABASE_URL=postgresql://...          # Main branch (production)
DATABASE_URL_DEV=postgresql://...      # Dev branch (development)

# Optional: Schema search path
DB_SEARCH_PATH=auth,in1,elements,library,public

Node.js Connection (with search_path)

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  options: '-c search_path=auth,in1,elements,library,public'
});

// Query without schema prefix
const { rows } = await pool.query('SELECT * FROM users WHERE email = $1', ['[email protected]']);

---

📝 NAMING CONVENTIONS

Tables

• Lowercase: users, bookings, haikus
• Plural: Tables represent collections
• Underscores: Multi-word names (auth_tokens, audit_logs)

Columns

• Lowercase: id, user_id, created_at
• Underscores: Multi-word names (hashed_password, check_in)
• Timestamps: Always created_at, updated_at
• Foreign Keys: {table}_id (e.g., user_id, booking_id)

Indexes

• Format: idx_{table}_{column(s)}
• Examples: idx_users_email, idx_bookings_dates

Schemas

• Lowercase: auth, in1, elements
• Singular: Represent domains, not collections

---

🛠️ COMMON COMMANDS

Schema Operations

-- List all schemas
SELECT schema_name FROM information_schema.schemata;

-- Set search path for session
SET search_path TO auth, in1, elements, library, public;

-- Create new schema
CREATE SCHEMA IF NOT EXISTS my_schema;

-- Move table to schema
ALTER TABLE public.users SET SCHEMA auth;

Index Management

-- List all indexes
SELECT indexname, tablename FROM pg_indexes WHERE schemaname = 'library';

-- Create index
CREATE INDEX idx_documents_language ON library.documents(language);

-- Drop index
DROP INDEX IF EXISTS idx_documents_language;

-- Reindex table
REINDEX TABLE library.documents;

Maintenance

-- Vacuum and analyze
VACUUM ANALYZE library.documents;

-- Check table size
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

---

📊 METRICS & MONITORING

Key Metrics to Track

-- Total haikus generated (IN-1)
SELECT COUNT(*) FROM in1.haikus;

-- Haikus by language
SELECT language, COUNT(*)
FROM in1.haikus
GROUP BY language;

-- Documents in library (public vs private)
SELECT public, COUNT(*)
FROM library.documents
GROUP BY public;

-- Documents by language
SELECT language, COUNT(*)
FROM library.documents
GROUP BY language;

-- Active users (last 30 days)
SELECT COUNT(DISTINCT user_id)
FROM auth.sessions
WHERE created_at > NOW() - INTERVAL '30 days';

-- Storage per schema
SELECT
    schemaname,
    SUM(pg_total_relation_size(schemaname||'.'||tablename)) / 1024 / 1024 AS size_mb
FROM pg_tables
WHERE schemaname IN ('auth', 'in1', 'elements', 'finance', 'library', 'system')
GROUP BY schemaname
ORDER BY size_mb DESC;

---

🚀 RECENT UPDATES

Library System (18 Dec 2025)

1. i18n Support:

   • Added language column (VARCHAR(5))
   • Added translation_id for linking versions
   • Auto-detection from markdown frontmatter

2. Sync Script:

   npm run sync-library

   • Scans library/public/ and library/private/
   • Generates slugs from filenames
   • Extracts frontmatter metadata
   • Calculates MD5 hash for change detection
   • Updates existing documents or inserts new ones

3. Categories Implemented:

   • filosofia - 2 docs (Rhama, Rhama Paradigm)
   • progetti - Project docs
   • algoritmi - IN-1 algorithm doc
   • strategie - Business strategies
   • finanza - Financial documents

---

📞 SUPPORT

BlackTrails Platform Team Database Admin: Francesco Pelosio Email: [email protected] Neon Dashboard: https://console.neon.tech/projects/patient-queen-89181819

---

End of Document 🌲✨