[Bug]: vec0 shadow table _rowids.id column is untyped — stores NULL for INTEGER PRIMARY KEY tables

[murillodutt]

Description

When creating a vec0 virtual table with INTEGER PRIMARY KEY, the shadow table {table}_rowids has an untyped id column that stores NULL for all rows instead of the actual integer ID.

Steps to Reproduce

-- Create a vec0 table with INTEGER PRIMARY KEY
CREATE VIRTUAL TABLE IF NOT EXISTS vec_test USING vec0(
  id INTEGER PRIMARY KEY,
  embedding FLOAT[384]
);

-- Insert some data
INSERT INTO vec_test (id, embedding) VALUES (1, <384-dim vector>);
INSERT INTO vec_test (id, embedding) VALUES (2, <384-dim vector>);

-- Check shadow table
SELECT * FROM vec_test_rowids;

Expected Behavior

vec_test_rowids.id should contain the integer IDs (1, 2, etc.).

Actual Behavior

vec_test_rowids.id is NULL for all rows. Example from a production database with 13,140 conversation embeddings:

SELECT COUNT(*) FROM vec_conversations_rowids WHERE id IS NULL;
-- Result: 13140 (100% of rows)

Root Cause

In the C source (sqlite-vec.c), the shadow table creation for the non-text-PK variant uses an untyped column:

"CREATE TABLE " VEC0_SHADOW_ROWIDS_NAME "("
"rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
"id,"          // <-- no type, no NOT NULL constraint
"chunk_id INTEGER,"
"chunk_offset INTEGER"
");"

For TEXT PRIMARY KEY tables, the id column is typed as TEXT, but for INTEGER PRIMARY KEY tables, it's left untyped.

Impact

• The NULL values don't break KNN queries at runtime because vec0 resolves IDs internally via rowid mapping
• However, direct queries on shadow tables (for debugging, integrity checks, or analytics) return no useful ID data
• SELECT COUNT(*) FROM vec_test_rowids WHERE id = 42 always returns 0

Environment

• sqlite-vec: 0.1.7-alpha.2 (also confirmed in 0.1.7-alpha.10 — source unchanged)
• Platform: macOS ARM64, also tested on Linux x64
• SQLite: 3.x via both Bun native and better-sqlite3

Reported by Murillo Dutt