Database Architecture¶

Document type: Technical Architecture — Database Specification
Product: WhiteLotus AI
Organization: Whitelotus Corporation
Product stage: Beta
Status: Initial source-inspected baseline
Audience: Backend engineers, QA engineers, DevOps, security reviewers, and technical stakeholders

This document describes the database-related infrastructure identified in the WhiteLotusAI repository, distinguishes verified implementation details from unresolved questions, and defines the evidence required to establish a complete database specification.

1. Purpose and Scope¶

WhiteLotusAI uses several infrastructure components that have different responsibilities. PostgreSQL provides a relational database platform, pgvector adds vector-search capabilities to PostgreSQL, Redis supports API rate limiting, and RabbitMQ provides message-driven communication.

The current source-inspected baseline establishes that:

• Docker Compose configures PostgreSQL using the pgvector/pgvector:pg16 image.
• Docker Compose includes Redis 7 and RabbitMQ 3.13.
• The REST API gateway initializes a Redis-backed rate limiter.
• The RAG worker initializes an embedding model and vector-store pool before starting its RabbitMQ listener.
• RAG handler registration includes modules for ingestion, search, document listing, deletion, context, and publishing.

The following details remain subject to further verification:

• The complete relational schema and ORM model inventory.
• The migration framework and migration history.
• The actual persistence destinations of individual services.
• The vector-store implementation and its relationship to PostgreSQL.
• Database constraints, indexes, access permissions, and operational recovery procedures.

2. Database Infrastructure¶

2.1 PostgreSQL and pgvector¶

The configured PostgreSQL container uses the following image:

image: pgvector/pgvector:pg16

This image provides PostgreSQL 16 with pgvector support. Its presence establishes the configured database capability, but does not prove that the pgvector extension has been enabled in the running database or that the application's vector data is stored there.

Responsibilities and verification requirements

2.2 Redis¶

The local Docker Compose configuration includes Redis 7. The REST API gateway initializes a Redis-backed rate limiter during startup.

This confirms Redis's role in API rate limiting. Additional uses, such as caching, session storage, or application-state persistence, should only be documented when supported by the relevant implementation.

Redis should not automatically be classified as the primary database for users, sessions, conversations, or documents.

2.3 RabbitMQ¶

Docker Compose includes RabbitMQ 3.13 with the management image. RabbitMQ is a message broker, not a relational database.

It supports the inspected message-driven communication patterns involving the REST API, Chat, Integrations, and worker components.

Queue definitions, exchanges, routing keys, acknowledgements, and temporary reply queues belong in the messaging architecture documentation rather than the relational schema reference.

3. Logical Infrastructure View¶

The following diagram separates the infrastructure components from the application behaviors that have been observed. The dashed relationship represents an unresolved implementation detail, not a confirmed database connection.

%%{init: {
  "flowchart": {
    "htmlLabels": true,
    "nodeSpacing": 50,
    "rankSpacing": 65,
    "useMaxWidth": true
  },
  "themeVariables": {
    "fontSize": "18px",
    "lineColor": "#9CA3AF"
  }
}}%%

flowchart TB
    subgraph Application["Application Components"]
        direction TB
        Gateway["REST API Gateway"]
        Chat["Chat Service"]
        Integrations["Integrations Service"]
        Workers["Message-Driven Workers"]
        RAG["RAG Worker"]
    end

    subgraph Infrastructure["Configured Infrastructure"]
        direction TB
        Redis[("Redis 7")]
        RabbitMQ[["RabbitMQ 3.13"]]
        PostgreSQL[("PostgreSQL 16<br/>pgvector-enabled image")]
        VectorStore["Vector-Store Pool"]
    end

    Gateway -->|"Rate limiting"| Redis
    Gateway -.->|"Message-driven communication"| RabbitMQ
    Chat -.->|"Message-driven communication"| RabbitMQ
    Integrations -.->|"Message-driven communication"| RabbitMQ
    Workers -.->|"Consumes and processes messages"| RabbitMQ
    RAG -->|"Initializes vector-store pool"| VectorStore
    VectorStore -.->|"Backend and persistence path to verify"| PostgreSQL

    %% Pastel fills with category-specific colored outlines.

    classDef gateway fill:#E8F0FE,stroke:#2563EB,stroke-width:1.5px,color:#23345B
    classDef application fill:#EDE9FE,stroke:#7C3AED,stroke-width:1.5px,color:#3B267A
    classDef processing fill:#E0F7FA,stroke:#0891B2,stroke-width:1.5px,color:#164E63
    classDef messaging fill:#FFF2CC,stroke:#D97706,stroke-width:1.5px,color:#7C3E00
    classDef database fill:#DDF5E5,stroke:#059669,stroke-width:1.5px,color:#16452B
    classDef pending fill:#F1F5F9,stroke:#64748B,stroke-width:1.5px,stroke-dasharray:5 3,color:#334155

    class Gateway gateway
    class Chat,Integrations application
    class Workers,RAG processing
    class RabbitMQ messaging
    class Redis database
    class PostgreSQL,VectorStore pending

    %% Transparent containers preserve the active theme background.

    style Application fill:transparent,stroke:#7C3AED,stroke-width:1.5px,color:inherit
    style Infrastructure fill:transparent,stroke:#64748B,stroke-width:1.5px,color:inherit

    %% Neutral connectors remain legible across themes.

    linkStyle default stroke:#9CA3AF,stroke-width:1.3px

For a complete view of service communication, consult System Design and Data Flow.

4. Component Inventory¶

5. Logical Data Ownership¶

Database ownership must be derived from actual persistence code, not inferred from the name or purpose of a service.

The following table identifies responsibilities and the evidence needed to establish their persistence behavior.

6. RAG and Vector Storage¶

6.1 Observed initialization lifecycle¶

The inspected RAG worker performs the following sequence before processing messages:

1. Initializes the embedding model.
2. Initializes the vector-store pool.
3. Logs that the worker is ready.
4. Starts consuming messages from the configured RAG exchange and queue.
5. Closes the search cache and vector-store pool during shutdown.

This establishes a dependency on initialized embedding and vector-store components before message processing begins.

It does not, by itself, establish the database schema, vector persistence backend, or behavior following an interrupted operation.

6.2 Registered document operations¶

The inspected RAG handler registration imports modules associated with the following operation categories:

Handler registration demonstrates that the corresponding modules are present in the inspected implementation. It does not prove that every operation succeeds end to end or satisfies a particular durability guarantee.

6.3 Vector-store verification requirements¶

Before finalizing the RAG storage specification, inspect the vector-store implementation, retriever, handlers, models, and migrations.

• Identify the database driver and connection-pool implementation.
• Establish the actual tables and columns used for documents and chunks.
• Verify whether embeddings are stored in PostgreSQL using pgvector or another backend.
• Confirm the embedding model and output dimensions.
• Document chunking rules, metadata fields, and ownership identifiers.
• Identify similarity-search operators, filters, and ranking logic.
• Verify index types and index configuration.
• Trace document updates and deletion across related records.
• Establish transaction boundaries and rollback behavior.
• Verify cache invalidation after document changes.
• Test persistence and recovery after worker restarts.

Do not publish a specific vector dimension, table name, index type, or schema relationship until the implementation confirms it.

7. Database Configuration and Connectivity¶

Database configuration should be documented from the actual environment files, connection constructors, deployment manifests, and secret references.

The following is a format illustration only, not a verified WhiteLotusAI configuration value:

postgresql://<username>:<password>@<host>:<port>/<database>

Use placeholders in documentation and redact credentials from logs, examples, and diagnostic output.

7.1 Configuration reference requirements¶

Environment variable names must be copied from the source configuration. Do not introduce new names solely to make the documentation table appear complete.

7.2 Local and deployed environments¶

Local Docker Compose configuration does not necessarily match the deployed environment. Verify each environment independently.

These tabs are an investigation guide, not a claim that all listed controls are currently configured.

8. Schema and Migration Management¶

The complete schema and migration framework have not yet been established in this baseline.

8.1 Source inspection order¶

1. ORM model definitions and database mappings.
2. Database connection and initialization code.
3. Migration configuration and migration files.
4. SQL scripts and schema initialization files.
5. Vector-store table and extension initialization.
6. Foreign keys, unique constraints, indexes, and check constraints.
7. Tests that create, migrate, or validate database structures.

8.2 Required schema reference¶

Once the implementation has been verified, document each table using the following structure.

An entity-relationship diagram should be added only after the tables and relationships have been verified. It should reflect actual constraints rather than assumed relationships between similarly named entities.

9. Security Requirements¶

The following controls are requirements for implementation and verification. They are not assertions that every control is already in place.

9.1 Credentials and secrets¶

• Never commit database passwords, access tokens, or private keys.
• Keep real credentials out of documentation and examples.
• Use placeholders for local configuration examples.
• Use an appropriate secret-management mechanism in deployed environments.
• Rotate credentials that may have been exposed.
• Avoid including credentials in database connection error messages or logs.

9.2 Access control¶

• Use appropriately scoped database accounts.
• Avoid unnecessary administrative privileges for application services.
• Restrict database network access to authorized components.
• Verify access controls for user, session, document, and vector data.
• Separate administrative and application access where appropriate.
• Confirm that service identities have only the permissions required for their responsibilities.

9.3 Sensitive data¶

• Identify tables containing personal information, authentication data, or OAuth tokens.
• Verify encryption requirements for sensitive records and backups.
• Avoid logging passwords, access tokens, refresh tokens, or sensitive query payloads.
• Define retention and deletion requirements based on actual data handling and product requirements.
• Verify that document deletion and account deletion meet the intended data lifecycle requirements.

10. Reliability, Backup, and Recovery¶

Production readiness requires more than a healthy database process. The operational specification must establish how persisted data is protected, monitored, and restored.

11. Database Verification Checklist¶

Use this checklist during the source audit and implementation review. Mark an item complete only when supported by inspected source code, configuration, or reproducible test evidence.

Infrastructure and configuration¶

• Identify every database connection and its owning component.
• Verify database names, endpoints, connection settings, and secret references.
• Confirm whether pgvector is enabled in the actual database.
• Verify database permissions and network restrictions.
• Establish local-versus-deployed configuration differences.

Schema and persistence¶

• Locate all ORM models and SQL statements.
• Identify the migration framework and migration files.
• Verify tables, columns, keys, constraints, and indexes.
• Trace user and session persistence behavior.
• Trace OAuth token persistence and confirm the runtime endpoint or message path.
• Identify the document and vector schema.
• Trace document ingestion, search, deletion, and persistence.
• Verify transaction boundaries and failure handling.

Operations and reliability¶

• Verify cache consistency and invalidation behavior.
• Review connection-pool limits, timeouts, and recovery.
• Verify backup schedules, retention, and restoration procedures.
• Run database integration tests and record their results.
• Test recovery from database and RAG worker interruptions.
• Document unresolved risks and assign owners for follow-up.

Related documentation¶