Data Schema & Neon

🗄️ Data Schema & Neon

Living Document - Questo schema riflette la struttura del database PostgreSQL su Neon.tech.

🎯 Overview

Database Provider: Neon.tech (Serverless PostgreSQL)
PostgreSQL Version: 17
Cluster Name: blacktrails
Project ID: patient-queen-89181819
Region: AWS EU-Central-1

Architecture Philosophy

BlackTrails uses a single-cluster, multi-schema architecture:

• ✅ One Cluster: blacktrails (cost-effective, unified management)
• ✅ Two Branches: main (production), dev (development)
• ✅ Eight Schemas: Logical domain separation (auth, in1, elements, finance, library, ai, system, organization)
• ✅ pgvector Enabled: For semantic search and RAG (Retrieval-Augmented Generation)

---

🌿 Branch Strategy

Main Branch (Production)

Branch: main
Endpoint: ep-fancy-dust-ag0l0r9n
Purpose: Production environment
Database: blacktrails

Connection String:

DATABASE_URL=postgresql://[user]:[password]@ep-fancy-dust-ag0l0r9n-pooler.c-2.eu-central-1.aws.neon.tech/blacktrails?sslmode=require

Dev Branch (Development)

Branch: dev
Endpoint: ep-weathered-scene-ag4iuf4p
Purpose: Testing & development
Database: blacktrails

Connection String:

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

---

🏗️ Schema Architecture

blacktrails (database)
├── 🔐 auth          # Users, Sessions, Roles, Tokens
├── 🌲 in1           # Rhama Generation (IN-1) + Emotional Alchemy Detection
├── 🏠 elements      # Booking System (Property Management)
├── 💰 finance       # Transactions, Invoices, Stripe
├── 📚 library       # Knowledge Base (Docs + Embeddings for RAG)
├── 🤖 ai            # AI Chats, Messages, Errors (Future)
├── 🔧 system        # Audit Logs, Migrations, Health
└── 🏢 organization  # Multi-tenant (Future)

---

📊 Entity-Relationship Diagram (Textual)

┌─────────────┐
│ auth.users  │──┐
└─────────────┘  │
       ↑         │
       │         │
       ├─────────┴──────────────────┐
       │                            │
┌──────┴──────┐          ┌──────────┴───────┐
│ auth.roles  │          │ auth.sessions    │
└─────────────┘          └──────────────────┘

       │
       ├────────────────────────────┐
       ↓                            ↓
┌─────────────┐          ┌──────────────────┐
│ in1.rhamas  │          │ elements.bookings│
│ (alchemy)   │          │                  │
└─────────────┘          └──────────────────┘
                                  ↓
                         ┌──────────────────┐
                         │ elements.rooms   │
                         └──────────────────┘
                                  ↓
                         ┌──────────────────┐
                         │ finance.trans... │
                         └──────────────────┘

┌─────────────────┐      ┌──────────────────────┐
│ library.docs    │──────│ library.embeddings   │
└─────────────────┘      └──────────────────────┘
                                  ↑
                            pgvector (RAG)

---

🔐 SCHEMA: auth

Purpose: Authentication, authorization, user management

Tables

auth.users

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    fantasy_name VARCHAR(100),      -- IN-1 display name
    full_name VARCHAR(255),         -- Real name (optional)
    avatar_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

Indexes:

• idx_users_email on email (login lookup)

auth.roles

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    product_code VARCHAR(20) NOT NULL,  -- 'in1', 'elements', 'gcore'
    role_name VARCHAR(20) NOT NULL,     -- 'admin', 'manager', 'user'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, product_code)
);

Indexes:

• idx_roles_user on user_id
• idx_roles_product on product_code

auth.auth_tokens

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

Indexes:

• idx_tokens_user on user_id

---

🌲 SCHEMA: in1

Purpose: IN-1 Rhama generation (conversational AI poetry with emotional alchemy)

Tables

in1.rhamas ⭐ CURRENT (Production Active)

CREATE TABLE rhamas (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    session_id VARCHAR(255),
    prompt_text TEXT NOT NULL,          -- User conversation (full context)
    rhama_text TEXT NOT NULL,           -- Generated Rhama (3-4 lines poetry)
    emotion VARCHAR(50),                -- Alchemy detection: "[ALCHEMY: Emotion | State]"
                                        -- Examples: "Tristezza | Piombo", "Gioia | Oro"
    tree_planted BOOLEAN DEFAULT FALSE,
    tree_id VARCHAR(100),               -- Tree planting service ID (future)
    language VARCHAR(5) DEFAULT 'it',   -- 'it' or 'en'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

• idx_rhamas_user on user_id (user history)
• idx_rhamas_session on session_id (anonymous tracking)
• idx_rhamas_emotion on emotion (alchemy analytics)
• idx_rhamas_language on language (i18n queries)
• idx_rhamas_created on created_at (time-series analysis)

Special Features:

• Alchemy Detection: The emotion field stores the full alchemy tag [ALCHEMY: Emozione | Stato] where:
  • Piombo (Lead): Pain, blockage, darkness, blind emotions
  • Transition: Movement, crossing, active change
  • Oro (Gold): Peace, gratitude, acceptance, awareness
• Soul Card Integration: Used by /in1/soul-card endpoint to calculate collective emotional state color mixing
• Baseline State Preservation: Captures emotional state from FIRST turn, not final turn of conversation

in1.haikus (Legacy - Read Only)

-- DEPRECATED: Old table name, kept for data migration
-- No longer used by application (2025-12-21+)
-- Scheduled for archival: Q1 2026

---

🏠 SCHEMA: elements

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

Tables

elements.bookings

CREATE TABLE bookings (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    guest_name VARCHAR(255),
    guest_email VARCHAR(255),
    room_id VARCHAR(50) NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    guests_count INTEGER DEFAULT 1,
    total_price DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'confirmed',  -- pending, confirmed, cancelled
    source VARCHAR(20) DEFAULT 'direct',     -- direct, booking, airbnb
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Indexes:

• idx_bookings_user on user_id
• idx_bookings_dates on (check_in, check_out)
• idx_bookings_status on status

elements.rooms

CREATE TABLE rooms (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    max_guests INTEGER DEFAULT 2,
    price_per_night DECIMAL(10, 2),
    active BOOLEAN DEFAULT TRUE
);

---

💰 SCHEMA: finance

Purpose: Financial transactions, invoices, Stripe webhooks

Tables

finance.transactions

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    booking_id INTEGER REFERENCES bookings(id),
    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 CURRENT_TIMESTAMP
);

Indexes:

• idx_transactions_user on user_id
• idx_transactions_booking on booking_id
• idx_transactions_status on status

---

📚 SCHEMA: library (RAG System)

Purpose: Knowledge base, document embeddings, semantic search

Extensions Required

CREATE EXTENSION IF NOT EXISTS vector;  -- pgvector

Tables

library.documents

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    slug VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(500) NOT NULL,
    category VARCHAR(100),              -- filosofia, progetti, algoritmi, legal
    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
    last_updated TIMESTAMP DEFAULT NOW(),
    created_at TIMESTAMP DEFAULT NOW()
);

Indexes:

• idx_documents_slug on slug
• idx_documents_category on category
• idx_documents_public on public
• idx_documents_hash on content_hash

library.embeddings

CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES 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
    created_at TIMESTAMP DEFAULT NOW()
);

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

Indexes:

• idx_embeddings_document on document_id
• idx_embeddings_vector on embedding (vector similarity)

---

🤖 RAG Query Pattern

How It Works

1. Document Ingestion:

   • Markdown files in library/public/ are synced to library.documents via scripts/sync-library.js
   • Content is chunked (500-1000 tokens per chunk)
   • Each chunk is embedded using OpenAI’s text-embedding-ada-002 model
   • Embeddings are stored in library.embeddings

2. Semantic Search:

   • User query is embedded using the same model
   • Vector similarity search finds the top-K most relevant chunks
   • Chunks are ranked by cosine similarity

3. Context Injection:

   • Top chunks are injected into AI prompt context
   • AI generates response grounded in documentation

Example RAG Query

-- Find top 5 similar document chunks
SELECT
    d.title,
    d.slug,
    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
ORDER BY e.embedding <=> $1::vector
LIMIT 5;

Parameters:

• $1::vector = Query embedding (1536-dimensional vector)

Returns:

• title: Document title
• slug: Document URL slug
• chunk_text: Relevant text chunk
• similarity: Cosine similarity score (0-1, higher = more similar)

---

🔧 SCHEMA: system

Purpose: Audit logs, migrations, health checks

Tables

system.audit_logs

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    action VARCHAR(100) NOT NULL,       -- login, logout, create, update, delete
    resource_type VARCHAR(100),         -- user, booking, haiku
    resource_id VARCHAR(255),
    ip_address INET,
    user_agent TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

Indexes:

• idx_audit_user on user_id
• idx_audit_action on action
• idx_audit_resource on (resource_type, resource_id)
• idx_audit_created on created_at DESC

---

📊 Key Metrics Queries

Total Rhamas Generated

SELECT COUNT(*) FROM in1.haikus;

Bookings by Status

SELECT status, COUNT(*)
FROM elements.bookings
GROUP BY status;

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;

RAG Document Count

SELECT
    public,
    COUNT(*) as count
FROM library.documents
GROUP BY public;

IN-1 Alchemy Distribution (Soul Card Data)

-- Today's Emotional States
SELECT
    CASE
        WHEN emotion LIKE '%(Piombo)%' THEN '🌑 Piombo'
        WHEN emotion LIKE '%(Transition)%' THEN '🌗 Transition'
        WHEN emotion LIKE '%(Oro)%' THEN '🌕 Oro'
        ELSE '❓ Other'
    END as alchemy_state,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) as percentage
FROM in1.rhamas
WHERE created_at >= CURRENT_DATE
  AND emotion IS NOT NULL
GROUP BY alchemy_state
ORDER BY count DESC;

IN-1 Language Distribution

SELECT
    language,
    COUNT(*) as total_rhamas,
    COUNT(DISTINCT session_id) as unique_sessions
FROM in1.rhamas
GROUP BY language
ORDER BY total_rhamas DESC;

---

🤖 Agent Update Instructions

AI Assistant: Quando lo schema del database cambia (nuove tabelle, colonne, indici), esegui:

# 1. Analizza il nuovo schema
read_file E:\BLACKTRAILS-PLATFORM\db\schema.sql
read_file E:\BLACKTRAILS-PLATFORM\docs\DATABASE.md

# 2. Aggiorna questo file mantenendo:
#    - Diagrammi ER testuali aggiornati
#    - Nuove tabelle con definizioni SQL
#    - Query RAG pattern se cambiano
#    - Key metrics queries aggiornate

# 3. NON includere password reali (usa ${DATABASE_URL})

# 4. Commit con messaggio:
git commit -m "docs(truth): update data schema"

---

Last Auto-Update: 2025-12-21
Next Review: On schema migration
Maintainer: BlackTrails AI Agent
Neon Dashboard: console.neon.tech/projects/patient-queen-89181819