Summary

Add a read-only SQLite function litestream_txid() that returns the current transaction ID from the VFS position. This complements the time travel functionality added in #849 by providing observability into the current replication state.

Context

Related to #849 (VFS time travel feature). This function follows the same implementation pattern established for custom SQLite functions in the Litestream VFS extension.

Per discussion with Ben:

"It'd nice to fetch the current TXID (litestream_txid())"

Implementation Details

Function Signature

SELECT litestream_txid(); -- Returns INTEGER (current TXID)

Initial Implementation Decision

• Return latest TXID always (regardless of time travel state)
• TODO: Add comment to confirm with Ben whether this should be time-travel aware (return TXID of current view when target time is set)

Files to Modify

1. src/litestream-vfs.c - Add C function implementation

   • Add litestream_txid_impl() static function
   • Register via sqlite3_create_function_v2() in litestream_auto_extension()
   • No SQLITE_DIRECTONLY flag needed (read-only)
   • Add extern declaration for GoLitestreamTXID()

2. cmd/litestream-vfs/main.go - Add CGO bridge

   //export GoLitestreamTXID
   func GoLitestreamTXID(dbPtr unsafe.Pointer, out *C.sqlite3_int64) *C.char

3. vfs_connections.go - Add Go implementation

   func VFSConnectionTXID(dbPtr uintptr) (uint64, error) {
       file, err := vfsFileForConnection(dbPtr)
       if err != nil {
           return 0, err
       }
       // TODO: Confirm with Ben - should this return current view TXID
       // (time travel aware) or always latest TXID? Currently returns latest.
       return uint64(file.Pos().TXID), nil
   }

4. cmd/litestream-vfs/time_travel_test.go - Add tests

5. docs/VFS.md - Update documentation with new function

Pattern Reference

Follow the exact pattern from time travel functions (commit 81c0c55):

• C layer auto-extension registration
• Connection validation (check litestream VFS)
• CGO bridge with proper memory management
• Connection-to-VFSFile mapping
• Thread-safe access to VFSFile state

Test Expectations

• ✅ Returns valid TXID (> 0) after database operations
• ✅ TXID increases after writes and sync operations
• ✅ Returns error when not using litestream VFS
• ✅ Works correctly alongside time travel functions
• ✅ Thread-safe with concurrent queries
• ✅ Handles connection cleanup properly

Acceptance Criteria

• Function returns correct TXID from VFS position
• Gracefully handles non-litestream VFS connections
• Tests cover success and error cases
• Documentation updated in docs/VFS.md
• TODO comment added for Ben to confirm time-travel behavior
• Follows established C/CGO/Go pattern from time travel implementation

Notes

• Requires CGO and explicit -tags vfs build flag
• Build with: CGO_ENABLED=1 go build -tags vfs -o bin/litestream-vfs ./cmd/litestream-vfs
• Test with: go test -tags vfs ./cmd/litestream-vfs -v
• Branch from issue-849-vfs-time-travel to build on existing infrastructure