Database Schema, Turso Integration, and Vector Storage

TenderCheck AI persists all data in Turso — a serverless edge SQLite database built on the LibSQL protocol. Turso was chosen because it is always-on (no cold-start penalty unlike a typical serverless database), scales to zero automatically, and works well with Render’s persistent Node.js runtime via its HTTP transport (https://). All database access goes through the Repository Pattern: domain-layer interfaces define the contract; infrastructure-layer classes provide the implementation using the @libsql/client SDK.

Database Schema

-- Database Schema for TenderCheck AI

-- 1. Users table
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    name TEXT NOT NULL,
    company TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tenders (Analyses) table
CREATE TABLE IF NOT EXISTS tenders (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    title TEXT NOT NULL,
    status TEXT NOT NULL, -- 'PENDING', 'COMPLETED', 'FAILED'
    document_url TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 3. Requirements table (linked to a Tender)
CREATE TABLE IF NOT EXISTS requirements (
    id TEXT PRIMARY KEY,
    tender_id TEXT NOT NULL,
    text TEXT NOT NULL,
    type TEXT NOT NULL, -- 'MANDATORY', 'OPTIONAL'
    confidence REAL DEFAULT 1.0,
    keywords TEXT, -- Store as JSON string or comma-separated
    page_number INTEGER,
    snippet TEXT,
    embedding BLOB, -- Vector embedding for semantic search (768 dimensions)
    FOREIGN KEY (tender_id) REFERENCES tenders(id) ON DELETE CASCADE
);

-- 4. Validation Results (linked to a Tender)
CREATE TABLE IF NOT EXISTS validation_results (
    id TEXT PRIMARY KEY,
    tender_id TEXT NOT NULL,
    status TEXT NOT NULL, -- 'MET', 'NOT_MET'
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tender_id) REFERENCES tenders(id) ON DELETE CASCADE
);

-- 5. Decision Log (Audit Trail)
CREATE TABLE IF NOT EXISTS decision_logs (
    id TEXT PRIMARY KEY,
    tender_id TEXT NOT NULL,
    requirement_id TEXT NOT NULL,
    prompt_used TEXT,
    ai_response TEXT,
    model_version TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tender_id) REFERENCES tenders(id) ON DELETE CASCADE
);

-- 6. Industry Presets for Scope Validation
CREATE TABLE IF NOT EXISTS industry_presets (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    positive_keywords TEXT NOT NULL, -- JSON array
    negative_keywords TEXT NOT NULL, -- JSON array
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Table Descriptions

Table	Purpose	Notable Columns
`users`	Stores authenticated user accounts	`password_hash` — bcrypt-hashed password; `company` — optional organisation name
`tenders`	One row per analysis run	`status` — `PENDING`, `COMPLETED`, or `FAILED`; `user_id` — cascades on user delete
`requirements`	Extracted requirements from a tender PDF	`embedding BLOB` — serialised `Float32Array` for vector search; `page_number` for clickable citations; `keywords` as a JSON string
`validation_results`	Proposal compliance results per tender	`status` — `MET` or `NOT_MET`; `message` — AI reasoning returned to the frontend
`decision_logs`	Immutable audit trail of every AI decision	`prompt_used`, `ai_response`, `model_version` — full prompt/response stored for debugging and compliance
`industry_presets`	Keyword presets that drive scope filtering	`positive_keywords` and `negative_keywords` — JSON arrays; seeded automatically on first startup (e.g., “Digital Services”, “Construction”)

Repository Pattern

The repository pattern cleanly separates the persistence contract from its implementation. Domain contracts (src/domain/):

// ITenderRepository.ts
export interface ITenderRepository {
  save(tender: TenderAnalysis): Promise<void>;
  findById(id: string): Promise<TenderAnalysis | null>;
  findByUserId(userId: string): Promise<TenderAnalysis[]>;
  delete(id: string): Promise<void>;
}

// UserRepository.ts
export interface UserRepository {
  findByEmail(email: string): Promise<User | null>;
  save(user: User): Promise<void>;
  findById(id: string): Promise<User | null>;
}

Infrastructure implementations (src/infrastructure/repositories/):

Interface	Production	Testing
`ITenderRepository`	`TursoTenderRepository`	`InMemoryTenderRepository`
`UserRepository`	`TursoUserRepository`	`InMemoryUserRepository`

Use cases in the Application layer receive these implementations through constructor injection. Because they only depend on the domain interface, the in-memory variants let the full test suite run without a live database connection.

Vector Embeddings in SQLite

Turso (SQLite) has no native vector similarity extension in the version used by this project, so embeddings are stored as BLOBs and similarity is computed in JavaScript by VectorSearchService. Serialisation round-trip:

// Serialize Float32Array → Buffer for SQLite BLOB
serializeEmbedding(embedding: Float32Array): Buffer {
  return Buffer.from(embedding.buffer);
}

// Deserialize Buffer from SQLite → Float32Array
deserializeEmbedding(buffer: Buffer): Float32Array {
  if (!buffer || !buffer.buffer || buffer.length === 0) {
    return new Float32Array(this.dimensions);
  }
  return new Float32Array(
    buffer.buffer,
    buffer.byteOffset || 0,
    buffer.length / Float32Array.BYTES_PER_ELEMENT,
  );
}

When TursoTenderRepository loads a tender’s requirements, it reads the embedding column as a Buffer and passes it to deserializeEmbedding() to reconstruct the Float32Array. VectorSearchService.findSimilar() then iterates over all requirement vectors, computes pairwise cosine similarity, and returns up to TOP_K_SIMILAR = 5 results above SIMILARITY_THRESHOLD = 0.3. The embedding column is added via a runtime ALTER TABLE migration inside TursoDatabase.initializeSchema(), so existing databases without the column are upgraded automatically on the next server start.

Connection Management

TursoDatabase is a singleton that creates the LibSQL client once and reuses it for the lifetime of the server process instance.

// TursoDatabase.ts
export class TursoDatabase {
  private static instance: Client | null = null;

  static getInstance(): Client {
    if (!TursoDatabase.instance) {
      const url = process.env.TURSO_DB_URL;
      const authToken = process.env.TURSO_AUTH_TOKEN;

      if (!url) {
        throw new Error(
          "🚨 TURSO_DB_URL is missing. Please check your .env file.",
        );
      }

      if (url.startsWith("libsql://") && !authToken) {
        throw new Error("🚨 TURSO_AUTH_TOKEN is required for remote Turso DB.");
      }

      // Force HTTP for serverless stability — WebSocket can be unstable
      const safeUrl = url.replace("libsql://", "https://");

      TursoDatabase.instance = createClient({
        url: safeUrl,
        ...(authToken ? { authToken } : {}),
      } as any);
    }
    return TursoDatabase.instance;
  }
}

URL format rules:

Environment	`TURSO_DB_URL` format	`TURSO_AUTH_TOKEN`
Render / production	`https://your-db-name.turso.io`	Required
Local development	`file:./local.db`	Not required
Remote + local tunnel	`libsql://your-db-name.turso.io`	Required (auto-converted to `https://`)

The libsql:// → https:// conversion is applied automatically at runtime to ensure HTTP transport is used in all production environments, avoiding WebSocket instability.

Schema Initialization

TursoDatabase.initializeSchema() runs automatically during server startup (called from server.ts before Express starts listening). It reads schema.sql, splits on ;, and executes each statement individually. All CREATE TABLE statements use IF NOT EXISTS, making the initialisation fully idempotent — safe to run on every cold start without risk of data loss or duplicate-table errors. After the core schema is applied, two additional bootstrap steps run:

Embedding column migration — ALTER TABLE requirements ADD COLUMN embedding BLOB (silently skipped if the column already exists).
Industry presets seeding — inserts default rows for “Digital Services” and “Construction” if industry_presets is empty.

To inspect your Turso database locally during development, use the Turso CLI shell:

turso db shell <your-db-name>

This opens an interactive SQLite prompt connected to your remote database. You can run SELECT * FROM tenders LIMIT 5; or SELECT id, text, length(embedding) FROM requirements; to verify that embeddings are being stored correctly.

Get Started

Core Features

Architecture

Configuration & Deployment

Database Schema, Turso Integration, and Vector Storage

Database Schema

Table Descriptions

Repository Pattern

Vector Embeddings in SQLite

Connection Management

Schema Initialization

​Database Schema

​Table Descriptions

​Repository Pattern

​Vector Embeddings in SQLite

​Connection Management

​Schema Initialization

Database Schema

Table Descriptions

Repository Pattern

Vector Embeddings in SQLite

Connection Management

Schema Initialization