Create robust database schema with vector search capabilities

[VolenNakov]

Design comprehensive database schema with pgvector support for AI-powered similarity search.

Acceptance Criteria:

• Enhanced listings table with vector fields
• Image embeddings table with HNSW indexes
• Categories table with hierarchical structure
• Listing attributes table for flexible metadata
• Proper foreign key constraints and indexes

Database Schema Components:

-- Main listings table with vector support
CREATE TABLE listings (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2),
    image_vectors vector(512)[],
    text_vector vector(1536),
    -- ... other fields
);

-- Vector similarity indexes
CREATE INDEX idx_listings_image_vectors ON listings 
    USING hnsw (image_vectors vector_cosine_ops);

Files to Create:

• internal/db/migrations/00006_enable_pgvector.sql
• internal/db/migrations/00007_create_listings_tables.sql
• internal/db/migrations/00008_create_image_embeddings.sql
• internal/db/migrations/00009_create_vector_indexes.sql

[onestardao]

Looks solid — very clean schema structure.

If you plan to push this toward AI-powered retrieval beyond basic similarity (e.g., meaning-level clustering or document-level logic recall), I’d suggest thinking in terms of semantic decomposability as early as now.

In a recent PDF I released, we use a structure called WFGY to annotate vector memory with traceable logic units — each vector isn't just “close” in cosine space, but also part of a semantic progression tracked via ΔS (semantic shift) and λ_observe (logical vector direction).

Might sound overkill, but the benefit is:
→ You can query things like “vectors that are semantically converging toward topic X” or
→ “Vectors that had a divergence from node Y but rebounded”

Anyway, here’s the PDF repo if it’s helpful — not a product, just an open framework for building meaning-aware vector stores:
https://github.com/onestardao/WFGY