Storage

The platform uses SQLite with FTS5 (full-text search) as the primary store, with JSON export for AI consumption. Both are shared across all verticals; schemas are per-vertical.

Why SQLite

The spike's decision, validated:

• Zero-config, portable, easy to back up
• FTS5 is sufficient for the search patterns we need (destination, service, specialty, free text)
• One file per environment — easy to inspect, easy to ship
• Postgres is the right answer at 10× the scale; we're not there

When (if) we outgrow SQLite, the migration path is well-defined: the Pydantic models and MCP tool contracts don't change, only the storage backend.

Schema Pattern (Per Vertical)

Each vertical gets its own set of tables. The DMC schema, validated in the spike:

-- Core entity table
CREATE TABLE dmc (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  source TEXT NOT NULL,
  source_url TEXT,
  crawled_at TIMESTAMP NOT NULL
);

-- Normalized relationship tables (many-to-many)
CREATE TABLE dmc_destinations (
  dmc_id TEXT REFERENCES dmc(id) ON DELETE CASCADE,
  destination TEXT NOT NULL,
  PRIMARY KEY (dmc_id, destination)
);

CREATE TABLE dmc_services (
  dmc_id TEXT REFERENCES dmc(id) ON DELETE CASCADE,
  service TEXT NOT NULL,
  PRIMARY KEY (dmc_id, service)
);

CREATE TABLE dmc_specialties (
  dmc_id TEXT REFERENCES dmc(id) ON DELETE CASCADE,
  specialty TEXT NOT NULL,
  PRIMARY KEY (dmc_id, specialty)
);

-- One-to-one related tables
CREATE TABLE dmc_contact (
  dmc_id TEXT PRIMARY KEY REFERENCES dmc(id) ON DELETE CASCADE,
  emails TEXT,      -- JSON array
  phones TEXT,      -- JSON array
  website TEXT,
  social TEXT       -- JSON object
);

CREATE TABLE dmc_team (
  dmc_id TEXT REFERENCES dmc(id) ON DELETE CASCADE,
  name TEXT,
  role TEXT,
  email TEXT,
  PRIMARY KEY (dmc_id, email)
);

-- Full-text search
CREATE VIRTUAL TABLE dmc_fts USING fts5(
  name, description, destinations, services, specialties,
  content='dmc', content_rowid='rowid'
);

Adding a New Vertical's Schema

To add the Dining vertical:

CREATE TABLE restaurant (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  cuisine TEXT,
  price_tier TEXT,
  source TEXT NOT NULL,
  source_url TEXT,
  crawled_at TIMESTAMP NOT NULL
);

CREATE TABLE restaurant_locations (
  restaurant_id TEXT REFERENCES restaurant(id) ON DELETE CASCADE,
  neighborhood TEXT,
  city TEXT,
  country TEXT,
  address TEXT,
  latitude REAL,
  longitude REAL,
  PRIMARY KEY (restaurant_id, city)
);

-- ... etc

CREATE VIRTUAL TABLE restaurant_fts USING fts5(
  name, description, cuisine, locations,
  content='restaurant', content_rowid='rowid'
);

The pattern is: core entity, normalized relationship tables, FTS5 mirror.

Upsert Logic

Every storage write is an upsert keyed on the entity's external ID. The spike's SQLiteStore implements this with cascade deletes:

def upsert(self, dmc: DMC) -> None:
    with self.conn:
        # Insert or replace the core row
        self.conn.execute(
            "INSERT OR REPLACE INTO dmc (...) VALUES (...)",
            (...)
        )
        # Delete existing relationship rows
        self.conn.execute("DELETE FROM dmc_destinations WHERE dmc_id = ?", (dmc.id,))
        # Re-insert
        for dest in dmc.destinations:
            self.conn.execute(
                "INSERT INTO dmc_destinations VALUES (?, ?)",
                (dmc.id, dest)
            )
        # ... same for services, specialties
        # FTS5 mirror is updated via triggers

Cascade deletes mean: replace the entity completely on re-crawl. No stale relationship rows.

Search

The storage layer exposes typed search methods:

store.search_dmc(
    query="luxury Greece",          # full-text query
    destination="Greece",           # filter
    service="Luxury Travel",        # filter
    specialty=None,                 # filter
    source=None,                    # filter
    limit=20,
)

Searches combine FTS5 with structured filters. The MCP server wraps these. See MCP Server.

JSON Export

storage/json_store.py exports the entire vertical to a single JSON file for AI consumption:

[
  {
    "id": "cf4f5c1676a7",
    "name": "62°N",
    "description": "...",
    "destinations": ["Faroe Islands"],
    "services": ["Accommodation", "..."],
    "specialties": ["Luxury Travel", "..."],
    "contact": { "emails": [...], "phones": [...], "website": "..." },
    "source": "inside_travel"
  },
  ...
]

The JSON export is the "give the LLM the whole world" path — useful for prompts that need broad context, even if the structured search is the primary interface.

Schema Migrations

Schema changes are versioned. A schema_version table tracks the current version. Migrations are forward-only, idempotent, and run on startup if needed.

Backups

• SQLite file is the entire database — cp dmc_data.db dmc_data.db.bak is a complete backup
• Backups run daily to object storage
• Retention: 30 days of daily backups