Skip to main content

Databases

The Picteus back-end embeds two distinct, complementary database systems to manage and persist all application data: a relational SQL database for structured data and a vector database for high-dimensional vector embeddings.

Database architecture & roles​

The two databases serve distinct and complementary operational roles:

  • Relational SQL database: stores structured business entities, application settings, API keys, repository definitions, collections, image metadata, extracted textual/numeric features, tags, and binary image attachments ;
  • Vector database: stores mathematical vector embeddings generated by AI models and extensions, enabling similarity searches and nearest-neighbor queries across images and text.

While Picteus currently uses SQLite for relational persistence and Chroma DB for vector storage, the architecture is designed around abstract persistence providers. In future releases, these engines may be replaced or augmented with alternative database backends — such as PostgreSQL with pgvector, or dedicated vector engines like Qdrant or Weaviate.

Relational SQL database — SQLite​

Technology & version​

  • Database engine: SQLite (managed through Prisma ORM v6.19) ;
  • Storage mechanism: single-file transactional database on the local filesystem.

Prisma schema & entities overview​

The relational database structure and migrations are defined in the Prisma schema file:

The schema defines the following core entities:

Entity / ModelRole & contained information
SettingsGlobal application configuration parameters stored as typed key-value records.
ApiSecretProgrammatic API access secrets, storing token hashes, expiration dates, comments, and security scopes.
ExtensionSettingsPer-extension persistent user configuration JSON payloads and extension version tracking metadata.
RepositoryPhysical image storage repositories, storing filesystem path URLs, technical identifiers, state, and modification dates.
CollectionUser-defined image collections, storing collection names, descriptions, and dynamic JSON search query filters.
ImageCore image records, capturing file properties — width, height, byte size, format, URL, creation/modification timestamps —, repository relationships, and parent/child precedence links for transformed images.
ImageMetadataRaw and structured metadata extracted from image files, including EXIF, IPTC, XMP, ICC color profiles, and Photoshop TIFF tags.
ImageFeatureStructured features extracted by extensions, supporting string or floating-point numeric values, categorized by feature type and format.
ImageTagKeyword tags associated with an image, attributed to specific extension identifiers.
ImageAttachmentAuxiliary binary files — such as thumbnails, masks, or custom documents — attached directly to images by extensions.

Vector database — Chroma DB​

Technology & version​

  • Database engine: Chroma DB (v1.0.20, managed via the chromadb client) ;
  • Embedding storage: persistent collections indexed with Hierarchical Navigable Small World — HNSW — graphs using cosine distance metrics.

Functionality & network exposure​

Chroma DB runs as an internal child process managed by the Picteus back-end. Each extension generating vector embeddings maintains dedicated Chroma collections indexed by extension identifier and embedding feature name.

  • HTTP endpoint: the Chroma server is exposed over HTTP on local loopback (127.0.0.1) ;
  • Default port: 3002 ;
  • Port configuration: the port can be customized at startup via the --vectorDatabasePort CLI option — for example, --vectorDatabasePort 3005.

[!WARNING] No built-in authentication on the vector database port : Chroma DB is exposed on the local loopback interface without authentication. While it is not bound to external network interfaces by default, any local process on the machine can connect to port 3002. Users should avoid running untrusted local processes alongside Picteus.

Database file locations & backups​

All database files reside within the application home folder:

  • Windows: C:\Users\<user>\AppData\Roaming\Picteus ;
  • macOS: /Users/<user>/Library/Application Support/Picteus ;
  • Linux: /home/<user>/.config/Picteus,

where <user> represents the current user's login.

File structure​

Within the application home folder:

  • database.db: the SQLite database file containing all relational data (as well as temporary -wal write-ahead log and -shm shared-memory files when active) ;
  • chroma: the directory containing all Chroma vector database indexes and parquet storage files.

Performing backups​

To back up all Picteus data safely:

  1. Stop Picteus: shut down the Picteus application completely to ensure that write-ahead logs are checkpointed and that no active transactions are in flight ;
  2. Copy the database file: copy database.db (along with database.db-wal and database.db-shm if present) to your backup destination ;
  3. Copy the vector directory: copy the entire chroma directory to your backup destination.

To restore a backup, place the copied database.db file and chroma directory back into the application home folder before starting Picteus.

External database clients & direct access​

Because both database systems use standard formats and protocols, advanced users and developers can inspect the databases directly using external database management tools.

  • Prisma Studio: an interactive web-based visual data browser provided by Prisma. It can be launched independently using the npx prisma studio CLI by downloading the schema file from GitHub and pointing the DATABASE_URL environment variable to the database.db file:

    ```bash title="macOS / Linux"
    curl -sO https://raw.githubusercontent.com/picteus/picteus/main/back-end/prisma/schema.prisma
    DATABASE_URL="file:/Users/<user>/Library/Application Support/Picteus/database.db" npx prisma studio --schema schema.prisma
    ```
    <!-- slide -->
    ```powershell title="Windows PowerShell"
    Invoke-WebRequest -Uri "https://raw.githubusercontent.com/picteus/picteus/main/back-end/prisma/schema.prisma" -OutFile "schema.prisma"
    $env:DATABASE_URL="file:C:\Users\<user>\AppData\Roaming\Picteus\database.db"
    npx prisma studio --schema schema.prisma
    ```
    <!-- slide -->
    ```batch title="Windows Command Prompt"
    curl -sO https://raw.githubusercontent.com/picteus/picteus/main/back-end/prisma/schema.prisma
    set DATABASE_URL=file:C:\Users\%USERNAME%\AppData\Roaming\Picteus\database.db
    npx prisma studio --schema schema.prisma
    ```

    Once started, Prisma Studio opens in your default browser at http://localhost:5555 ;

  • DB Browser for SQLite: a dedicated open-source visual editor — https://sqlitebrowser.org/ ;

  • DBeaver: a universal multi-platform database tool — https://dbeaver.io/ ;

  • JetBrains DataGrip: a full-featured database IDE — https://www.jetbrains.com/datagrip/ ;

  • TablePlus: a modern native database client — https://tableplus.com/.

  • Official Chroma Python SDK: inspect collections programmatically using chromadb.HttpClient(host="localhost", port=3002) — https://docs.trychroma.com/ ;
  • Official Chroma JavaScript SDK: inspect collections with the chromadb npm package — https://www.npmjs.com/package/chromadb ;
  • HTTP REST API clients: query the Chroma REST API endpoints directly at http://localhost:3002/api/v1/collections using tools such as Postman, Insomnia, or curl.

Read-only access guideline​

[!CAUTION] Access databases in read-only mode only : direct modifications to database.db or Chroma DB collections bypassing the Picteus back-end can compromise referential integrity, invalidate internal caches, corrupt search indexes, and cause unexpected crashes in the front-end and extension runtimes. External database tools should strictly be used for inspection, diagnostics, and reporting in read-only mode.