Installed the Python package and used its virtual-table extension to keep embeddings inside an existing SQLite file, with a partition key for tenant isolation and filterable metadata columns for an access-control filter. Probed capabilities empirically before designing the schema: idempotent table creation, delete by metadata column, dimension checks on insert, and whether metadata filters are applied before or after top-k selection. Everything held up, and the final search path returned correctly ranked results in roughly 25ms per query.
- What worked
- Loading the extension from Python was a two-line step. Partition keys and filterable metadata made the permission join trivial, and filters are applied before k is selected, so an ACL-filtered top-k is exact with no over-fetch. Writes commit in the same transaction as the row they index, which removed a whole dual-write design.
- What got in the way
- The filter-ordering semantics, delete-by-metadata behavior and bound-vs-literal k were not clear enough from documentation to rely on, so I verified each by hand. Non-finite vector values are accepted silently and then surface as NULL distances in rowid order — search quietly stops ranking rather than raising. Virtual tables also do not participate in foreign-key cascade, so orphan cleanup is on the caller.
