Database Provider Abstraction - Repository Pattern Implementation

[jlacerte]

Summary

I've implemented a Repository Pattern for Archon that abstracts database operations, making it possible to swap between different database providers (Supabase, PostgreSQL, Neon, etc.) without changing business logic.

Motivation

Currently, Archon is tightly coupled to Supabase. This makes it difficult to:

• Use alternative providers (Neon, self-hosted Postgres, etc.) - see Feature Request: Neon db integration #880
• • Run unit tests without a live database
• • Deploy in environments where Supabase isn't available

What I've Built

Interface-based architecture:

Domain Layer
├── models/ (SitePage, SearchResult - Pydantic models)
└── interfaces/ (ISitePagesRepository - abstract contract)

Infrastructure Layer
├── supabase/ (existing RPC-based implementation)
├── postgres/ (direct pgvector queries)
└── memory/ (for testing)

Key features:

• ISitePagesRepository interface with search_similar(), get_by_id(), insert_batch(), delete_by_source()
• • Three implementations: Supabase, PostgreSQL (pgvector), In-Memory
• • Dependency Injection via REPOSITORY_TYPE environment variable
• • Backward compatible - Supabase remains the default

Current Status

The core implementation is done on an earlier version of Archon. I've forked the repo and created a branch to adapt this to the new microservices architecture (python/src/server/).

Fork: https://github.com/jlacerte/Archon
Branch: feature/database-abstraction

Next Steps

Before submitting a PR, I'd like feedback on:

1. Does this align with the project's direction?
2. 2. Any preferences on interface design or DI approach?
3. 3. Priority of which services to refactor first?
      Happy to adapt the implementation based on feedback. This could also serve as a foundation for requests like Feature Request: Neon db integration #880 (Neon support).

[jlacerte]

Quick note: I'm working on this with Claude Code as my coding partner, and I'm relatively new to open source contributions. Any feedback or guidance is welcome!

[jlacerte]

For those who want to see the actual code:

Here's the core implementation in my working branch:

• Domain Layer (interfaces + models)
• • Infrastructure Layer (implementations)
• • DI Container
    Architecture in a nutshell:

┌─────────────────────────┐
│    Business Logic       │  <- Never changes
├─────────────────────────┤
│   ISitePagesRepository  │  <- Stable contract
├─────────────────────────┤
│ Supabase|Postgres|Memory │  <- Swap without impact
└─────────────────────────┘

Switch providers with one env variable: REPOSITORY_TYPE=supabase|postgres|memory