Database Map

Questi contenuti non sono ancora disponibili nella tua lingua.

🗄️ DATABASE MAP - BLACKTRAILS

Database Name: blacktrails Provider: Neon.tech (PostgreSQL 17) Architecture: Multi-schema (separated by domain)

📋 QUICK REFERENCE

Schema	Brand	Tables Count	Purpose
`auth`	All	4	Authentication & authorization
`in1`	IN-1	2	Haiku generation app
`library`	All	2	Knowledge base (RAG)
`elements`	Elements	3	Booking system
`finance`	All	3	Financial transactions
`system`	All	3	Logs & audits

Total Tables: 17 (6 active, 11 planned)

🔐 SCHEMA: auth

Brand: All (shared authentication) Purpose: User authentication, sessions, roles, tokens

Table: `auth.users`

What it does: Stores user accounts (email, password, profile)

Columns:

id              UUID PRIMARY KEY        -- Unique user ID (gen_random_uuid)
email           VARCHAR(255) UNIQUE     -- User email (login identifier)
email_verified  BOOLEAN DEFAULT FALSE   -- Email verification status
hashed_password VARCHAR(255)            -- Bcrypt hashed password (NOT plain text!)
name            VARCHAR(255)            -- Display name
avatar_url      TEXT                    -- Profile picture URL
created_at      TIMESTAMP               -- Account creation date
updated_at      TIMESTAMP               -- Last profile update

Used by:

IN-1 (future: save haikus to account)
Elements (booking user accounts)
All apps (centralized authentication)

Example row:

id: 550e8400-e29b-41d4-a716-446655440000
email: [email protected]
hashed_password: $2b$10$...
name: Francesco Pelosio
created_at: 2025-12-01 10:30:00

Table: `auth.sessions`

What it does: Tracks active user sessions (login state)

Columns:

id          VARCHAR(255) PRIMARY KEY    -- Session ID (random string)
user_id     UUID REFERENCES users       -- Which user owns this session
expires_at  TIMESTAMP                   -- When session expires
created_at  TIMESTAMP                   -- When session was created

Used by:

All apps (check if user is logged in)
Automatic cleanup (delete expired sessions)

Example row:

id: sess_abc123xyz789
user_id: 550e8400-e29b-41d4-a716-446655440000
expires_at: 2025-12-25 10:30:00
created_at: 2025-12-18 10:30:00

Table: `auth.roles`

What it does: Assigns roles to users per product (admin, user, etc.)

Columns:

id            SERIAL PRIMARY KEY        -- Auto-increment ID
user_id       UUID REFERENCES users     -- Which user
product_code  VARCHAR(50)               -- Which product (in1, elements, gcore)
role          VARCHAR(50)               -- Role name (owner, admin, member)
granted_at    TIMESTAMP                 -- When role was assigned

Used by:

Permission checks (can this user do X?)
Multi-tenant access control

Example row:

id: 1
user_id: 550e8400-e29b-41d4-a716-446655440000
product_code: in1
role: owner
granted_at: 2025-12-01 10:30:00

Table: `auth.tokens`

What it does: Stores temporary tokens (password reset, email verification)

Columns:

id          SERIAL PRIMARY KEY          -- Auto-increment ID
user_id     UUID REFERENCES users       -- Which user owns this token
token       VARCHAR(255) UNIQUE         -- Token string (random, secure)
type        VARCHAR(50)                 -- Token type (refresh, reset_password, verify_email)
expires_at  TIMESTAMP                   -- When token expires
created_at  TIMESTAMP                   -- When token was created

Used by:

Password reset flow
Email verification flow
JWT refresh tokens

Example row:

id: 1
user_id: 550e8400-e29b-41d4-a716-446655440000
token: abc123xyz789...
type: reset_password
expires_at: 2025-12-18 11:30:00
created_at: 2025-12-18 10:30:00

🌲 SCHEMA: in1

Brand: IN-1 (Haiku generation app) Purpose: Store haikus generated by users

Table: `in1.haikus`

What it does: Stores every haiku generated by IN-1 app

Columns:

id            SERIAL PRIMARY KEY        -- Auto-increment haiku ID
user_id       UUID REFERENCES users     -- Which user (nullable = anonymous)
session_id    VARCHAR(255)              -- Session identifier (crypto.randomBytes)
prompt_text   TEXT                      -- Full user conversation (all 5 turns concatenated)
haiku_text    TEXT                      -- Generated haiku (3 lines, 5-7-5 syllables)
emotion       VARCHAR(50)               -- Detected emotion (gioia, tristezza, paura, etc.)
language      VARCHAR(10) DEFAULT 'it'  -- Language (it, en, es, fr, de)
tree_planted  BOOLEAN DEFAULT FALSE     -- If tree was planted for this haiku
tree_id       VARCHAR(255)              -- External tree planting service ID
created_at    TIMESTAMP                 -- When haiku was generated

Used by:

IN-1 app (save haiku after generation)
Forest gallery (read haikus for display)
Analytics (count haikus by language, emotion)

Indexes:

idx_haikus_user         -- Fast lookup by user
idx_haikus_session      -- Fast lookup by session
idx_haikus_language     -- Filter by language
idx_haikus_emotion      -- Filter by emotion
idx_haikus_created_at   -- Sort by date (DESC)

Example row:

id: 1
user_id: NULL (anonymous)
session_id: a1b2c3d4e5f6
prompt_text: "paura del futuro... incertezza... buio..."
haiku_text: "Notte senza stelle\nIl sentiero si nasconde\nMa l'alba arriverà"
emotion: paura
language: it
tree_planted: false
created_at: 2025-12-18 10:30:00

Table: `in1.generations` (Future)

What it does: Track generation metadata (analytics, costs)

Columns:

id                  SERIAL PRIMARY KEY      -- Auto-increment ID
haiku_id            INT REFERENCES haikus   -- Which haiku
model_conversation  VARCHAR(100)            -- Model used for chat (claude-haiku-4.5)
model_analyzer      VARCHAR(100)            -- Model used for analysis (claude-sonnet-3.5)
model_poet          VARCHAR(100)            -- Model used for haiku (claude-sonnet-3.5)
turns               INTEGER                 -- Number of conversation turns (1-5)
tokens_used         INTEGER                 -- Total tokens consumed
cost_usd            DECIMAL(10, 6)          -- API cost in USD
latency_ms          INTEGER                 -- Total generation time (milliseconds)
created_at          TIMESTAMP               -- When generated

Used by:

Analytics dashboard (cost tracking)
Performance monitoring
A/B testing different models

📚 SCHEMA: library

Brand: All (shared knowledge base) Purpose: Knowledge base, RAG system, document management

Table: `library.documents`

What it does: Stores markdown documents (philosophy, projects, legal, etc.)

Columns:

id              SERIAL PRIMARY KEY          -- Auto-increment doc ID
slug            VARCHAR(255) UNIQUE         -- URL-friendly identifier (rhama, in1-algorithm)
title           VARCHAR(500)                -- Document title
category        VARCHAR(100)                -- Category (filosofia, progetti, algoritmi, legal, strategie, finanza)
content         TEXT                        -- Full markdown content
content_hash    VARCHAR(64)                 -- MD5 hash (detect changes)
public          BOOLEAN DEFAULT FALSE       -- Public (true) or private (false)
author          VARCHAR(255)                -- Author name
tags            TEXT[]                      -- Array of tags (["philosophy", "rhama"])
metadata        JSONB                       -- Frontmatter fields (custom data)
language        VARCHAR(5) DEFAULT 'it'     -- Language code (it, en, es, fr, de)
translation_id  VARCHAR(100)                -- Links translated versions (rhama = same for all langs)
last_updated    TIMESTAMP                   -- Last content update
created_at      TIMESTAMP                   -- When document was created

Used by:

Website (display public documents)
RAG system (semantic search)
Sync script (npm run sync-library)

Indexes:

idx_documents_slug            -- Fast lookup by slug
idx_documents_category        -- Filter by category
idx_documents_public          -- Filter public/private
idx_documents_hash            -- Detect content changes
idx_documents_language        -- Filter by language
idx_documents_translation_id  -- Find translations

Example row:

id: 1
slug: rhama
title: La Nascita di Rhama
category: filosofia
content: "# La Nascita di Rhama\n\nRhama nasce dall'amore..."
content_hash: a1b2c3d4e5f6...
public: true
author: Francesco Pelosio
tags: ["filosofia", "rhama", "origine"]
metadata: {"lang": "it", "date": "2025-12-01"}
language: it
translation_id: rhama
last_updated: 2025-12-18 10:30:00
created_at: 2025-12-01 10:00:00

Table: `library.embeddings`

What it does: Stores vector embeddings for semantic search (RAG)

Columns:

id            SERIAL PRIMARY KEY              -- Auto-increment ID
document_id   INT REFERENCES documents        -- Which document this chunk belongs to
chunk_index   INTEGER                         -- Position in document (0, 1, 2...)
chunk_text    TEXT                            -- Text chunk (~500 words)
embedding     vector(1536)                    -- OpenAI embedding (1536 dimensions)
created_at    TIMESTAMP                       -- When embedding was generated

Used by:

RAG queries (find similar documents)
Semantic search (natural language queries)

Indexes:

idx_embeddings_document   -- Fast lookup by document
idx_embeddings_vector     -- Vector similarity search (IVFFlat, cosine)

Example row:

id: 1
document_id: 1
chunk_index: 0
chunk_text: "Rhama nasce dall'amore di una madre..."
embedding: [0.123, -0.456, 0.789, ...] (1536 values)
created_at: 2025-12-18 10:30:00

RAG Query Example:

-- Find 5 most similar documents to a query embedding
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
  AND d.language = 'it'
ORDER BY e.embedding <=> $1::vector
LIMIT 5;

🏠 SCHEMA: elements

Brand: Elements (Booking system) Purpose: Room booking, guest management

Table: `elements.rooms`

What it does: Stores available rooms for booking

Columns:

id              SERIAL PRIMARY KEY      -- Auto-increment room ID
name            VARCHAR(255)            -- Room name (Camera Deluxe, Suite Presidenziale)
description     TEXT                    -- Room description
max_guests      INTEGER DEFAULT 2       -- Maximum guests allowed
price_per_night DECIMAL(10, 2)          -- Price per night (€)
amenities       JSONB                   -- Amenities (["wifi", "tv", "balcony"])
images          TEXT[]                  -- Array of image URLs
available       BOOLEAN DEFAULT TRUE    -- Room available for booking
created_at      TIMESTAMP               -- When room was added
updated_at      TIMESTAMP               -- Last room update

Used by:

Elements booking app (list rooms)
Admin panel (manage rooms)

Example row:

id: 1
name: Camera Deluxe
description: Camera spaziosa con vista montagna
max_guests: 2
price_per_night: 120.00
amenities: {"wifi": true, "tv": true, "balcony": true}
images: ["https://cdn.../room1.jpg", "https://cdn.../room2.jpg"]
available: true
created_at: 2025-12-01 10:00:00

Table: `elements.bookings`

What it does: Stores room bookings

Columns:

id          SERIAL PRIMARY KEY          -- Auto-increment booking ID
user_id     UUID REFERENCES users       -- Which user made booking (nullable)
room_id     INT REFERENCES rooms        -- Which room was booked
guest_name  VARCHAR(255)                -- Guest name
guest_email VARCHAR(255)                -- Guest email
check_in    DATE                        -- Check-in date
check_out   DATE                        -- Check-out date
guests      INTEGER DEFAULT 1           -- Number of guests
total_price DECIMAL(10, 2)              -- Total price (calculated)
status      VARCHAR(50) DEFAULT 'pending' -- Status (pending, confirmed, cancelled)
notes       TEXT                        -- Special requests
created_at  TIMESTAMP                   -- When booking was made
updated_at  TIMESTAMP                   -- Last booking update

Used by:

Elements booking app (create booking)
Admin panel (manage bookings)
Email notifications (send confirmation)

Indexes:

idx_bookings_user     -- Fast lookup by user
idx_bookings_room     -- Fast lookup by room
idx_bookings_dates    -- Fast lookup by date range
idx_bookings_status   -- Filter by status

Example row:

id: 1
user_id: 550e8400-e29b-41d4-a716-446655440000
room_id: 1
guest_name: Francesco Pelosio
guest_email: [email protected]
check_in: 2025-12-20
check_out: 2025-12-22
guests: 2
total_price: 240.00
status: confirmed
notes: Late check-in requested
created_at: 2025-12-18 10:00:00

Table: `elements.guests`

What it does: Stores guest details for bookings (legal requirement)

Columns:

id              SERIAL PRIMARY KEY      -- Auto-increment guest ID
booking_id      INT REFERENCES bookings -- Which booking
name            VARCHAR(255)            -- Guest full name
email           VARCHAR(255)            -- Guest email
phone           VARCHAR(50)             -- Guest phone
document_type   VARCHAR(50)             -- Document type (passport, id_card)
document_number VARCHAR(100)            -- Document number
nationality     VARCHAR(100)            -- Nationality
created_at      TIMESTAMP               -- When guest was added

Used by:

Legal compliance (registry of guests)
Check-in process

💰 SCHEMA: finance

Brand: All (shared financial transactions) Purpose: Track payments, invoices, Stripe logs

Table: `finance.transactions`

What it does: Stores all financial transactions

Columns:

id                      SERIAL PRIMARY KEY      -- Auto-increment transaction ID
user_id                 UUID REFERENCES users   -- Which user
booking_id              INT REFERENCES bookings -- Which booking (nullable)
amount                  DECIMAL(10, 2)          -- Transaction amount
currency                VARCHAR(3) DEFAULT 'EUR' -- Currency code
type                    VARCHAR(50)             -- Type (payment, refund, payout)
status                  VARCHAR(50) DEFAULT 'pending' -- Status (pending, completed, failed)
payment_method          VARCHAR(50)             -- Payment method (card, bank_transfer, cash)
stripe_payment_intent_id VARCHAR(255)           -- Stripe payment ID
metadata                JSONB                   -- Additional data
created_at              TIMESTAMP               -- When transaction was created

Used by:

Payment processing (Stripe integration)
Accounting (track revenue)
Refunds (process refunds)

Indexes:

idx_transactions_user       -- Fast lookup by user
idx_transactions_booking    -- Fast lookup by booking
idx_transactions_status     -- Filter by status
idx_transactions_created    -- Sort by date

Table: `finance.invoices`

What it does: Stores invoices for bookings

Columns:

id              SERIAL PRIMARY KEY          -- Auto-increment invoice ID
booking_id      INT REFERENCES bookings     -- Which booking
invoice_number  VARCHAR(100) UNIQUE         -- Invoice number (INV-2025-001)
amount          DECIMAL(10, 2)              -- Invoice amount
currency        VARCHAR(3) DEFAULT 'EUR'    -- Currency code
status          VARCHAR(50) DEFAULT 'draft' -- Status (draft, sent, paid, cancelled)
pdf_url         TEXT                        -- PDF invoice URL
issued_at       DATE                        -- Issue date
due_at          DATE                        -- Due date
paid_at         DATE                        -- Payment date
created_at      TIMESTAMP                   -- When invoice was created

Used by:

Accounting (generate invoices)
Email (send invoice PDF)

Table: `finance.stripe_logs`

What it does: Logs all Stripe webhook events

Columns:

id              SERIAL PRIMARY KEY      -- Auto-increment log ID
event_type      VARCHAR(100)            -- Event type (payment_intent.succeeded)
stripe_event_id VARCHAR(255) UNIQUE     -- Stripe event ID
payload         JSONB                   -- Full webhook payload
processed       BOOLEAN DEFAULT FALSE   -- Event processed
created_at      TIMESTAMP               -- When event was received

Used by:

Webhook processing (handle Stripe events)
Debugging (track payment issues)

Indexes:

idx_stripe_logs_event_type  -- Filter by event type
idx_stripe_logs_processed   -- Find unprocessed events

🔧 SCHEMA: system

Brand: All (shared system logs) Purpose: Audit logs, migrations, health checks

Table: `system.audit_logs`

What it does: Logs all important user actions (security audit)

Columns:

id            SERIAL PRIMARY KEY      -- Auto-increment log ID
user_id       UUID REFERENCES users   -- Which user performed action
action        VARCHAR(100)            -- Action (login, logout, create, update, delete)
resource_type VARCHAR(100)            -- Resource type (user, booking, haiku)
resource_id   VARCHAR(255)            -- Resource ID
ip_address    INET                    -- IP address
user_agent    TEXT                    -- Browser user agent
metadata      JSONB                   -- Additional data
created_at    TIMESTAMP               -- When action was performed

Used by:

Security audits (track suspicious activity)
Compliance (GDPR data access logs)

Indexes:

idx_audit_user      -- Fast lookup by user
idx_audit_action    -- Filter by action
idx_audit_resource  -- Fast lookup by resource
idx_audit_created   -- Sort by date

Table: `system.migrations`

What it does: Tracks database migrations (schema changes)

Columns:

id          SERIAL PRIMARY KEY      -- Auto-increment migration ID
name        VARCHAR(255) UNIQUE     -- Migration name (002_add_refresh_tokens)
executed_at TIMESTAMP               -- When migration was executed

Used by:

Migration scripts (prevent duplicate runs)

Table: `system.health_checks`

What it does: Logs health check results (uptime monitoring)

Columns:

id               SERIAL PRIMARY KEY      -- Auto-increment check ID
service          VARCHAR(100)            -- Service name (database, redis, api, stripe)
status           VARCHAR(50)             -- Status (healthy, degraded, down)
response_time_ms INTEGER                 -- Response time (milliseconds)
error_message    TEXT                    -- Error message (if failed)
checked_at       TIMESTAMP               -- When check was performed

Used by:

Monitoring dashboard (uptime status)
Alerts (send notification if service down)

Indexes:

idx_health_service  -- Filter by service
idx_health_checked  -- Sort by date

📊 SUMMARY BY BRAND

IN-1 (Haiku App)

in1.haikus - Generated haikus
in1.generations - Generation metadata (future)
auth.* - User authentication (shared)
library.* - Knowledge base (shared)

Elements (Booking System)

elements.rooms - Available rooms
elements.bookings - Room bookings
elements.guests - Guest details
finance.* - Payments & invoices (shared)
auth.* - User authentication (shared)

Library (Knowledge Base)

library.documents - Markdown documents
library.embeddings - Vector embeddings (RAG)
Used by all brands

Shared Services

auth.* - Authentication (all brands)
finance.* - Financial transactions (all brands)
system.* - Logs & audits (all brands)

End of Document 🌲✨

Database Map

🗄️ DATABASE MAP - BLACKTRAILS

📋 QUICK REFERENCE

🔐 SCHEMA: auth

Table: auth.users

Table: auth.sessions

Table: auth.roles

Table: auth.tokens

🌲 SCHEMA: in1

Table: in1.haikus

Table: in1.generations (Future)

📚 SCHEMA: library

Table: library.documents

Table: library.embeddings

🏠 SCHEMA: elements

Table: elements.rooms

Table: elements.bookings

Table: elements.guests

💰 SCHEMA: finance

Table: finance.transactions

Table: finance.invoices

Table: finance.stripe_logs

🔧 SCHEMA: system

Table: system.audit_logs

Table: system.migrations

Table: system.health_checks

📊 SUMMARY BY BRAND

IN-1 (Haiku App)

Elements (Booking System)

Library (Knowledge Base)

Shared Services

Table: `auth.users`

Table: `auth.sessions`

Table: `auth.roles`

Table: `auth.tokens`

Table: `in1.haikus`

Table: `in1.generations` (Future)

Table: `library.documents`

Table: `library.embeddings`

Table: `elements.rooms`

Table: `elements.bookings`

Table: `elements.guests`

Table: `finance.transactions`

Table: `finance.invoices`

Table: `finance.stripe_logs`

Table: `system.audit_logs`

Table: `system.migrations`

Table: `system.health_checks`