Data Storage & Organization

Last Updated: September 2026

Scrapalot provides flexible, powerful data storage designed for optimal performance and security with a dual-database architecture separating user data from AI data.

Storage Strategy

Scrapalot uses multiple specialized databases to deliver the best performance for different types of data:

Primary Database: PostgreSQL with pgvector

Two-Database Architecture:

• scrapalot_backend (Kotlin Backend): User data, auth, workspaces, collections, sessions, messages
• scrapalot (Python Chat): AI data, document content, chunks, embeddings, research plans

Purpose: Separation of concerns for scalability and security

Benefits:

• Reliable: ACID-compliant transactions ensure your data is never lost
• Fast Vector Search: Native support for semantic similarity search
• Scalable: Handles millions of documents efficiently
• Flexible: JSON support for custom metadata

Recommended for: All production deployments

Supporting Components

Redis (required):

• Purpose: Cross-service data sync (Redis Streams), background-job broker, gateway rate limiting and caching
• Isolation: each service uses its own Redis database (Python, Kotlin and Gateway are kept apart)

Neo4j:

• Purpose: Knowledge graph of entities and relationships in your documents, plus the opt-in Personal Brain memory
• Benefits: Answer questions about how concepts relate to each other
• Note: graph features can be switched off per deployment; everything else keeps working without them

Deployment Options

Production: Self-Hosted pgvector Docker (Recommended)

Production uses a self-hosted PostgreSQL with pgvector running in Docker. This provides complete data sovereignty and no external dependencies.

Why Self-Hosted pgvector Docker?

• Complete data sovereignty
• No external service dependencies
• Full control over configuration and backups
• pgvector extension pre-installed in Docker image
• No ongoing service costs

Setup Steps:

1. Deploy pgvector Docker container (included in docker-compose)
2. Two databases created automatically: scrapalot_backend and scrapalot
3. Migrations are applied on deploy (Liquibase for Kotlin runs on startup; Alembic for Python runs as a deploy step)

Cross-Service Data Sync

Redis Streams SAGA ensures reliable cross-service data synchronization:

• Kotlin and Python each own specific tables (see Data Ownership below)
• Changes propagate via Redis Streams with consumer groups (XADD/XREADGROUP)
• SAGA pattern: remote DB commits first, ACK via saga_ack stream, then local DB commits
• Pending message recovery at startup for guaranteed delivery
• Replaces legacy Redis Pub/Sub for data sync operations
• Streams in use: see Architecture Overview

Alternative: Self-Hosted PostgreSQL

You can run PostgreSQL yourself without Docker for complete control:

Requirements:

• PostgreSQL 18 or newer
• pgvector extension installed
• Minimum 2GB RAM allocated per database
• Regular backup strategy
• Two databases: scrapalot_backend and scrapalot

When to use:

• Complete data sovereignty required
• Custom performance tuning needed
• Integration with existing infrastructure

Data Organization

Workspaces

What: Organizational containers for your content Use for: Separating projects, teams, or clients Features:

• Share with team members
• Control access per workspace
• Workspace chat, huddles and projects live alongside the documents

Collections

What: Groups of related documents within a workspace Use for: Organizing documents by topic, project, or purpose Features:

• AI-generated description (kept unless you edit it yourself)
• Optional connectors that sync files from external sources
• Add custom metadata
• Control search scope

Chunking preferences are a per-user setting, and the embedding model is chosen once for the whole deployment, so every collection can be searched together.

Documents

What: Your uploaded files Support: PDF, EPUB, Word (DOCX), Markdown, plain text, RTF, and spreadsheets (CSV, TSV, XLSX, XLS) Features:

• Automatic processing and indexing
• Deduplication (same file uploaded twice detected)
• Processing status tracking
• Custom metadata support

Document Chunks

What: Smart segments of your documents optimized for retrieval Benefits:

• Precise answers from exact document sections
• Efficient semantic search
• Citation with page numbers
• Context-aware segmentation

Security & Access Control

Multi-Tenant Isolation

Automatic Protection:

• Your data is isolated from other users
• Application-level workspace isolation (enforced in the Kotlin backend)
• Workspace-based access control on every query

How it works:

1. You only see your workspaces
2. Your team members only see shared workspaces
3. Access cascades automatically (workspace → collections → documents)

Sharing & Collaboration

Workspace Sharing:

• Share entire workspace with team members
• Control access levels per member
• Shared users automatically see all collections in workspace

Access Levels:

• Owner: Full control, can delete and share
• Admin: Can edit content and manage members
• Write: Add/edit documents, cannot delete the workspace or manage members
• Read: Read-only access

Data Protection

Backup & Recovery

Backups:

• Configure your own backup schedule for self-hosted deployments
• Store backups securely off-site
• Test restoration regularly

Encryption

Data in Transit:

• All connections use TLS/SSL encryption
• API calls encrypted automatically
• Database connections secured

Data at Rest:

• Database encryption (configurable per deployment)
• API keys encrypted in storage
• Secure credential management

Compliance

Data Retention:

• Configure retention policies
• Automatic cleanup options
• Export capabilities for archival

Performance Features

Vector Search Optimization

Fast Similarity Search:

• Optimized indexes for semantic search
• Efficient nearest-neighbor algorithms
• Configurable precision/speed trade-off

Search Performance:

• Sub-second search across millions of chunks
• Parallel query execution
• Smart caching for common queries

Connection Management

Automatic Pooling:

• Efficient connection reuse
• Handles high concurrent users
• Automatic scaling within limits

Performance Monitoring:

• Track query performance
• Identify slow operations
• Optimize based on usage patterns

Migration & Updates

Automatic Migrations

Managed Updates:

• Database schema updates are applied as part of each deploy
• No manual intervention needed

What gets migrated:

• New features and improvements
• Security patches
• Performance optimizations
• Bug fixes

Cross-Platform Support

Works Everywhere:

• Development & Production: PostgreSQL 18 + pgvector (the pgvector container)
• SQLite supported only as a compatibility/test fallback (sqlite_compat.py), not the standard dev DB

Troubleshooting

Common Issues

Cannot Connect to Database:

• Verify connection parameters are correct
• Check if database is running/accessible
• Ensure firewall allows connections

Slow Queries:

• Check if indexes are built
• Review query complexity
• Consider enabling Redis cache
• Monitor database resource usage

Out of Storage:

• Review document retention policies
• Delete unused collections
• Optimize chunk sizes
• Check for duplicate uploads

Performance Degradation:

• Monitor connection pool usage
• Check for long-running queries
• Review index health
• Consider read replicas for scale

Best Practices

Document Management

Organize Effectively:

• Use workspaces to separate major projects
• Group related documents in collections
• Add meaningful metadata
• Use consistent naming conventions

Performance Optimization:

• Choose appropriate chunk sizes for your content
• Select embedding models based on accuracy vs. speed needs
• Archive old collections when no longer needed
• Monitor storage usage

Security

Access Control:

• Use workspace sharing for team collaboration
• Review access permissions regularly
• Remove inactive users
• Use viewer role when edit access not needed

Data Safety:

• Verify backups are working
• Test restoration procedures
• Keep connection credentials secure
• Use environment variables, never hardcode

Scaling

Growing Your Deployment:

• Start with self-hosted pgvector Docker
• Enable Redis as query volume increases
• Add Neo4j when graph features needed
• Consider read replicas for high traffic

Related Documentation

• RAG Strategy - How vector search powers retrieval
• Model Management - Choosing embedding models
• Security - Access control details
• Deployment Guide - Production setup

---

Scrapalot handles all database complexity automatically. Just upload your documents and start asking questions - the rest happens behind the scenes.