Schema & Setup

BashGres stores versioned filesystem state across small relational tables: version roots, version labels, ancestor closure rows, entries, and deduplicated blobs. Paths use PostgreSQL ltree, and RLS isolates every table by workspace.

setup()

The setup() function creates everything idempotently. Safe to call on every startup. It works with both adapters.

import { setup } from "bash-gres/postgres" // or "bash-gres/drizzle"

await setup(sql, {
  enableRLS: true,              // Row-Level Security (default: true)
  enableFullTextSearch: true,   // BM25 index via pg_textsearch (default: true)
  enableVectorSearch: false,    // pgvector HNSW index (default: false)
  embeddingDimensions: 1536,    // required if enableVectorSearch is true
  skipExtensions: false,        // skip CREATE EXTENSION (default: false)
})

SetupOptions

Option	Type	Default	Description
enableRLS	boolean	true	Enable Row-Level Security for workspace isolation
enableFullTextSearch	boolean	true	Create BM25 index on content (requires pg_textsearch)
enableVectorSearch	boolean	false	Add embedding column and HNSW index (requires pgvector)
embeddingDimensions	number	-	Vector dimensions (required when enableVectorSearch is true)
skipExtensions	boolean	false	Skip CREATE EXTENSION if extensions already exist

Table Schema

The core tables separate the workspace isolation boundary from the versioning boundary. A version root is usually /, but a directory created with versioned: true gets its own row in fs_version_roots.

CREATE TABLE fs_version_roots (
    id            bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    workspace_id  text NOT NULL,
    path          ltree NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, path)
);

CREATE TABLE fs_versions (
    id                 bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    workspace_id       text NOT NULL,
    version_root_id    bigint REFERENCES fs_version_roots(id) ON DELETE RESTRICT,
    label              text NOT NULL,
    parent_version_id  bigint REFERENCES fs_versions(id) ON DELETE RESTRICT,
    created_at         timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE version_ancestors (
    workspace_id   text NOT NULL,
    descendant_id  bigint NOT NULL,
    ancestor_id    bigint NOT NULL,
    depth          int NOT NULL CHECK (depth >= 0),
    PRIMARY KEY (workspace_id, descendant_id, ancestor_id)
);

CREATE TABLE fs_blobs (
    workspace_id  text NOT NULL,
    hash          bytea NOT NULL,
    content       text,
    binary_data   bytea,
    size_bytes    bigint NOT NULL DEFAULT 0,
    created_at    timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (workspace_id, hash)
);

CREATE TABLE fs_entries (
    workspace_id    text NOT NULL,
    version_id      bigint NOT NULL,
    path            ltree NOT NULL,
    blob_hash       bytea,
    node_type       text NOT NULL CHECK (node_type IN ('file', 'directory', 'symlink', 'tombstone')),
    symlink_target  text,
    mode            int NOT NULL DEFAULT 420,
    size_bytes      bigint NOT NULL DEFAULT 0,
    mtime           timestamptz NOT NULL DEFAULT now(),
    created_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (workspace_id, version_id, path)
);

fs_versions.label is unique per version root, so two versioned directories in the same workspace can both have labels like main and draft. See Versioning for the API.

Indexes

Index	Type	Purpose
idx_fs_entries_path_gist	GiST	Subtree scans for directory listing, walk, glob, usage, and diff
unique_workspace_version_root_label	B-tree	Unique version labels inside each version root
idx_fs_entries_path_version	B-tree	Visibility lookups by workspace, path, and version
idx_version_ancestors_depth	B-tree	Nearest-ancestor scans for copy-on-write visibility
idx_fs_blobs_content_bm25	BM25	Full-text search on content (optional)
idx_fs_blobs_embedding	HNSW	Vector similarity search (optional)

PostgreSQL Extensions

Extension	Required	Purpose
ltree	Always	Hierarchical path storage and queries
pg_textsearch	If enableFullTextSearch	BM25-ranked full-text search
pgvector	If enableVectorSearch	Embedding storage and HNSW similarity

Drizzle Schema

If you use Drizzle ORM, use createSchema() to generate the table definition so drizzle-kit can manage migrations for you.

schema.ts

import { createSchema } from "bash-gres/drizzle"

export const schema = createSchema({
  enableFullTextSearch: true,   // BM25 index on content
  enableVectorSearch: false,    // pgvector HNSW index
  embeddingDimensions: 1536,    // required if enableVectorSearch
})

Drizzle Migrations

generateMigrationSQL() produces SQL for extensions and RLS policies that Drizzle can't express. Paste it into a custom migration.

import { generateMigrationSQL } from "bash-gres/drizzle"

const sql = generateMigrationSQL({
  enableRLS: true,
  enableFullTextSearch: true,
  enableVectorSearch: false,
})

console.log(sql)
// CREATE EXTENSION IF NOT EXISTS ltree;
// CREATE EXTENSION IF NOT EXISTS pg_textsearch;
// ALTER TABLE fs_entries ENABLE ROW LEVEL SECURITY;
// ...

# Generate the table migration, then add a custom one for extensions + RLS
npx drizzle-kit generate
npx drizzle-kit generate --custom
npx drizzle-kit migrate