Database Architecture

DATABASE ARCHITECTURE - BLACKTRAILS PLATFORM

Version: 2.2 Date: 22 Dicembre 2025 Author: BlackTrails Team Status: Production Ready (Synced with Real DB)

---

📋 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

Current Status (Dec 22, 2025):

• ✅ auth - Active (users, roles, tokens) - NO password storage
• ✅ in1 - Active (rhamas table with 98 records)
• ✅ library - Active (5 documents, pgvector enabled)
• ✅ elements - Active (3 rooms, booking system ready)
• 🚧 finance - Schema exists but empty (planned)
• 🚧 ai - Schema exists but empty (planned)
• 🚧 system - Schema exists but empty (planned)
• 🚧 organization - Schema exists but empty (planned)

---

🗄️ 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 (NO passwords)
├── in1           # IN-1 Rhama App (98 rhamas)
├── elements      # Booking System (3 rooms)
├── library       # Knowledge Base RAG (5 docs, pgvector enabled)
├── finance       # Financial Transactions (🚧 empty - future)
├── ai            # AI Services (🚧 empty - future)
├── system        # System Logs & Audits (🚧 empty - future)
└── organization  # Multi-tenant (🚧 empty - future)

---

🔐 SCHEMA: auth

Purpose: User authentication, roles, tokens (NO PASSWORD - OAuth/Magic Link only)

Tables

auth.users

CREATE TABLE auth.users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    fantasy_name VARCHAR(100), -- Nome di fantasia/nickname
    full_name VARCHAR(255), -- Nome completo
    avatar_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

CREATE INDEX idx_users_email ON auth.users(email);

Note:

• ⚠️ NO password storage - Authentication via OAuth or magic links only
• ⚠️ NO email_verified - Verification happens through token system

auth.tokens

CREATE TABLE auth.tokens (
    token VARCHAR(255) PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    expires_at TIMESTAMP NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_tokens_user ON auth.tokens(user_id);

Purpose: Magic link tokens, password reset tokens (if needed), email verification

auth.roles

CREATE TABLE auth.roles (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    product_code VARCHAR(20) NOT NULL,
    role_name VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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);

Product Codes:

• in1 - IN-1 Haiku App
• elements - Booking System
• gcore - G-CORE SaaS
• admin - Admin Dashboard

---

🌲 SCHEMA: in1

Purpose: IN-1 Rhama generation app (conversational AI, renamed from haikus)

Tables

in1.rhamas

CREATE TABLE in1.rhamas (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    session_id VARCHAR(255),
    prompt_text TEXT NOT NULL,
    rhama_text TEXT NOT NULL,
    emotion VARCHAR(50),
    language VARCHAR(2) DEFAULT 'it',
    original_language VARCHAR(5), -- Language of original generation
    translations JSONB DEFAULT '{}', -- Multi-language translations
    tree_planted BOOLEAN DEFAULT FALSE,
    tree_id VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_rhamas_user ON in1.rhamas(user_id);
CREATE INDEX idx_rhamas_session ON in1.rhamas(session_id);
CREATE INDEX idx_rhamas_language ON in1.rhamas(language);
CREATE INDEX idx_rhamas_original_language ON in1.rhamas(original_language);
CREATE INDEX idx_rhamas_emotion ON in1.rhamas(emotion);
CREATE INDEX idx_rhamas_created ON in1.rhamas(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)
• rhama_text: Generated rhama/haiku (3 lines)
• emotion: Detected emotion (paura, gioia, tristezza, etc.)
• language: Current display language (it/en/es/fr/de)
• original_language: Language used for generation
• translations: JSONB object with translations: {"en": "...", "it": "...", "es": "..."}
• tree_planted: If tree was planted for this rhama
• tree_id: External tree planting service ID

Note: Table renamed from haikus to rhamas to reflect brand evolution

---

📚 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 VARCHAR(50) PRIMARY KEY, -- String ID (e.g., 'room-001', 'suite-deluxe')
    name VARCHAR(100) NOT NULL,
    description TEXT,
    max_guests INTEGER DEFAULT 2,
    price_per_night NUMERIC,
    active BOOLEAN DEFAULT TRUE
);

Note:

• ⚠️ Simplified structure for MVP
• Images and amenities managed externally or in future JSONB column
• id is VARCHAR for human-readable room codes

elements.bookings

CREATE TABLE elements.bookings (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    room_id VARCHAR(50) NOT NULL, -- References elements.rooms(id)
    guest_name VARCHAR(255),
    guest_email VARCHAR(255),
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    guests_count INTEGER DEFAULT 1,
    total_price NUMERIC,
    status VARCHAR(20) DEFAULT 'confirmed', -- confirmed, cancelled, completed
    source VARCHAR(20) DEFAULT 'direct', -- direct, booking.com, airbnb, etc.
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

Booking Sources:

• direct - Direct booking from website
• booking - Booking.com
• airbnb - Airbnb
• phone - Phone reservation

---

💰 SCHEMA: finance

Purpose: Financial transactions, invoices, Stripe logs

Status: 🚧 NOT YET IMPLEMENTED

Planned Tables

finance.transactions (FUTURE)

-- Schema exists but no tables created yet
-- Planned structure:

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()
);

Note: Currently payments are managed externally or manually

---

🔌 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 rhamas generated (IN-1)
SELECT COUNT(*) FROM in1.rhamas;

-- Rhamas by language
SELECT language, COUNT(*)
FROM in1.rhamas
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

Schema Alignment (25 Dec 2025)

PLATFORM Documentation Update:

1. ✅ schema.sql - Complete rewrite to reflect multi-schema architecture

   • Aligned with production database state
   • Added all 8 schemas with proper table definitions
   • Backup created: schema.sql.backup

2. ✅ SCHEMA_REFERENCE.md - Created comprehensive reference (11KB)

   • Full table documentation with examples
   • Query patterns for RAG system
   • Schema diagram and relationships

3. ✅ MIGRATION_LOG.md - Created migration history tracker

   • Log of all database changes
   • Template for future migrations
   • Verification steps documented

4. ✅ verify-db-schema.js - Updated with schema-qualified queries

   • Fixed count queries to use auth.users, in1.rhamas, etc.
   • Removed obsolete haikus checks
   • Added library tables verification

5. ✅ AGENTS runbooks - Created standardized AI workflows

   • LEGGI-DATABASE.md (read-only reports)
   • AGGIORNA-DATABASE.md (schema updates with confirmations)

Documentation Sync with Real Database (22 Dec 2025)

Major Changes:

1. ✅ auth schema - Updated to reflect token-based auth (NO password storage)

   • Removed auth.sessions (not implemented)
   • Added auth.tokens (magic links)
   • Updated auth.users structure (fantasy_name, full_name, no password)

2. ✅ in1 schema - Renamed table from haikus to rhamas

   • Added translations JSONB field
   • Added original_language field
   • Updated indexes

3. ✅ elements schema - Updated to match production structure

   • Changed rooms.id to VARCHAR(50)
   • Simplified room structure (removed amenities, images)
   • Changed available to active
   • Added source field to bookings

4. ✅ finance schema - Marked as NOT IMPLEMENTED (schema exists but empty)

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 🌲✨

Last verified: 22 Dicembre 2025 - Database structure verified via Neon connection