vss vector search tables is not supported in CR-SQL

[Volland]

Dear Team,
We working on privacy firest ai agent and for our data vector search is a critical feature.
i tried to use https://github.com/asg017/sqlite-vss extension that is quite popular vector extension

Steps to reproduce
1 . install deps

npm install sqlite-vss @vlcn.io/crsqlite better-sqlite3

my deps :
"@vlcn.io/crsqlite": "^0.16.3",
"better-sqlite3": "^9.4.3",
"fastembed": "^1.14.1",
"sqlite-vss": "^0.1.2",
"uuid": "^9.0.1"

2. try to turn vss table to a CR-sql table

import Database from "better-sqlite3";
import { extensionPath } from "@vlcn.io/crsqlite";
import * as sqlite_vss from "sqlite-vss";

const setupDb = (path) => {
    const db = new Database(path);
    db.loadExtension(extensionPath);
    sqlite_vss.load(db);

    console.log('0')
    db.exec(`
     CREATE TABLE IF NOT EXISTS nodes (
        id PRIMARY KEY NOT NULL,
        label VARCHAR
     )
    `)

     db.exec(`select crsql_as_crr('nodes');`)
     console.log('1')
     db.exec(`
    create virtual table node_vector using vss0(
        vectorLabel(768)
      );
    `)
     db.exec(`select crsql_as_crr('node_vector');`)

}
setupDb('mydb.sqlite')

error result :
SqliteError: Table node_vector has no primary key or primary key is nullable. CRRs must have a non nullable primary key
at Database.exec (/Users/volodymyrpavlyshyn/Documents/work/hyphen/cr-sql-playground/vector-test/node_modules/better-sqlite3/lib/methods/wrappers.js:9:14)
at setupDb (file:///Users/volodymyrpavlyshyn/Documents/work/hyphen/cr-sql-playground/vector-test/vec.js:27:9)
at main (file:///Users/volodymyrpavlyshyn/Documents/work/hyphen/cr-sql-playground/vector-test/vec.js:35:18)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
code: 'SQLITE_ERROR'

I also noticed that
CREATE TABLE IF NOT EXISTS nodes (
id VARCHAR(36 ) PRIMARY KEY NOT NULL,
label VARCHAR
)
will throw error

[tantaman]

Sorry for the late reply. Was on a family trip.

---

I see the issue. node_vector is a virtual table so can't itself be made a crr.

sqlite-vss should create base tables underneath the virtual table. Converting those base tables (rather then the top level virtual interface) to crrs would be what we need to do.

@asg017 -- is that a correct assumption, that vss creates a set of base or shadow tables used by the virtual table?