[Feature] Add CHECK constraints and unique constraints to database schema

[d-oit]

Description

The SQLite schema lacks important constraints that would ensure data integrity at the database level:

1. No CHECK constraint on claims.confidence to enforce 0 <= confidence <= 1
2. No CHECK constraint on claims.verification_status to enforce valid enum values
3. No UNIQUE constraint on entities.name — duplicate entity names are possible
4. No indexes on frequently queried columns (claims.verification_status, notes.entity_id, web_cache.resolved_at)

Current Schema Gaps

-- Missing: CHECK (confidence BETWEEN 0 AND 1)
confidence REAL NOT NULL,

-- Missing: CHECK (verification_status IN ('unverified', 'verified', 'disputed'))
verification_status TEXT NOT NULL DEFAULT 'unverified',

-- Missing: UNIQUE constraint
name TEXT NOT NULL,

Proposed Changes

-- Add CHECK constraints
confidence REAL NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
verification_status TEXT NOT NULL DEFAULT 'unverified' 
  CHECK (verification_status IN ('unverified', 'verified', 'disputed')),

-- Add unique constraint
name TEXT NOT NULL UNIQUE,

-- Add missing indexes
CREATE INDEX idx_claims_verification_status ON claims(verification_status);
CREATE INDEX idx_notes_entity_id ON notes(entity_id);
CREATE INDEX idx_web_cache_resolved_at ON web_cache(resolved_at);

Acceptance Criteria

• confidence has CHECK constraint
• verification_status has CHECK constraint
• entities.name has UNIQUE constraint
• Missing indexes created
• Existing tests still pass
• Zod schemas match database constraints

[github-actions]

👋 This looks like a duplicate of #185.

The canonical issue has broader scope and/or more tasks — please subscribe there for updates.

⏳ This issue will be auto-closed in 3 days unless you react with 👎 to dispute.

Detected via title similarity analysis