Summary

Implement the following plan:

Gitgres: PostgreSQL as a Git Backend

Context

Store git objects and refs in Postgres tables. Two implementations: pure SQL (PL/pgSQL functions, works on managed Postgres) and a C extension (native types, faster parsing). A libgit2-based bridge program registers Postgres as the storage backend so standard git push/fetch/clone work against the database.

Project Structure

gitgres/  Makefile                        # top-level, delegates to sub-makes  sql/    schema.sql                    # tables, indexes    functions/      object_hash.sql      object_read_write.sql      tree_parse.sql      commit_parse.sql      ref_manage.sql    views/      queryable.sql               # materialized views  ext/                            # Postgres C extension    Makefile                      # PGXS    gitgres.c                     # PG_MODULE_MAGIC, init    git_oid_type.c                # 20-byte OID type with operators    sha1_hash.c                   # C SHA1 via OpenSSL    tree_parse.c                  # C tree entry parser    gitgres.control    sql/gitgres--0.1.sql          # CREATE TYPE, CREATE FUNCTION  backend/                        # libgit2 pluggable backend    Makefile    main.c                        # CLI entry point (serves repos)    odb_postgres.c                # git_odb_backend implementation    odb_postgres.h    refdb_postgres.c              # git_refdb_backend implementation    refdb_postgres.h    writepack_postgres.c          # packfile receive handling  import/    gitgres-import.sh             # shell script: git plumbing + psql  test/    test_helper.rb    schema_test.rb    object_hash_test.rb    object_store_test.rb    tree_parse_test.rb    commit_parse_test.rb    ref_test.rb    roundtrip_test.rb             # push then clone, diff working trees

gitgres/  Makefile                        # top-level, delegates to sub-makes  sql/    schema.sql                    # tables, indexes    functions/      object_hash.sql      object_read_write.sql      tree_parse.sql      commit_parse.sql      ref_manage.sql    views/      queryable.sql               # materialized views  ext/                            # Postgres C extension    Makefile                      # PGXS    gitgres.c                     # PG_MODULE_MAGIC, init    git_oid_type.c                # 20-byte OID type with operators    sha1_hash.c                   # C SHA1 via OpenSSL    tree_parse.c                  # C tree entry parser    gitgres.control    sql/gitgres--0.1.sql          # CREATE TYPE, CREATE FUNCTION  backend/                        # libgit2 pluggable backend    Makefile    main.c                        # CLI entry point (serves repos)    odb_postgres.c                # git_odb_backend implementation    odb_postgres.h    refdb_postgres.c              # git_refdb_backend implementation    refdb_postgres.h    writepack_postgres.c          # packfile receive handling  import/    gitgres-import.sh             # shell script: git plumbing + psql  test/    test_helper.rb    schema_test.rb    object_hash_test.rb    object_store_test.rb    tree_parse_test.rb    commit_parse_test.rb    ref_test.rb    roundtrip_test.rb             # push then clone, diff working trees

Schema

Depends on pgcrypto (available on all managed Postgres). OIDs stored as bytea (20 bytes raw).

sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE repositories (    id          serial PRIMARY KEY,    name        text NOT NULL UNIQUE,    created_at  timestamptz NOT NULL DEFAULT now());
-- Git objects: raw content without the git header.-- Type uses git's internal numbering: 1=commit, 2=tree, 3=blob, 4=tag.-- OID = SHA1("<type> <size>\0<content>"), computed on write.CREATE TABLE objects (    repo_id     integer NOT NULL REFERENCES repositories(id),    oid         bytea NOT NULL,    type        smallint NOT NULL,    size        integer NOT NULL,    content     bytea NOT NULL,    PRIMARY KEY (repo_id, oid));CREATE INDEX idx_objects_oid ON objects (oid);
CREATE TABLE refs (    repo_id     integer NOT NULL REFERENCES repositories(id),    name        text NOT NULL,    oid         bytea,                 -- NULL for symbolic refs    symbolic    text,                  -- NULL for direct refs    PRIMARY KEY (repo_id, name),    CHECK ((oid IS NOT NULL) != (symbolic IS NOT NULL)));
CREATE TABLE reflog (    id          bigserial PRIMARY KEY,    repo_id     integer NOT NULL REFERENCES repositories(id),    ref_name    text NOT NULL,    old_oid     bytea,    new_oid     bytea,    committer   text NOT NULL,    timestamp_s bigint NOT NULL,    tz_offset   text NOT NULL,    message     text,    created_at  timestamptz NOT NULL DEFAULT now());CREATE INDEX idx_reflog_ref ON reflog (repo_id, ref_name, id);

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE repositories (    id          serial PRIMARY KEY,    name        text NOT NULL UNIQUE,    created_at  timestamptz NOT NULL DEFAULT now());
-- Git objects: raw content without the git header.-- Type uses git's internal numbering: 1=commit, 2=tree, 3=blob, 4=tag.-- OID = SHA1("<type> <size>\0<content>"), computed on write.CREATE TABLE objects (    repo_id     integer NOT NULL REFERENCES repositories(id),    oid         bytea NOT NULL,    type        smallint NOT NULL,    size        integer NOT NULL,    content     bytea NOT NULL,    PRIMARY KEY (repo_id, oid));CREATE INDEX idx_objects_oid ON objects (oid);
CREATE TABLE refs (    repo_id     integer NOT NULL REFERENCES repositories(id),    name        text NOT NULL,    oid         bytea,                 -- NULL for symbolic refs    symbolic    text,                  -- NULL for direct refs    PRIMARY KEY (repo_id, name),    CHECK ((oid IS NOT NULL) != (symbolic IS NOT NULL)));
CREATE TABLE reflog (    id          bigserial PRIMARY KEY,    repo_id     integer NOT NULL REFERENCES repositories(id),    ref_name    text NOT NULL,    old_oid     bytea,    new_oid     bytea,    committer   text NOT NULL,    timestamp_s bigint NOT NULL,    tz_offset   text NOT NULL,    message     text,    created_at  timestamptz NOT NULL DEFAULT now());CREATE INDEX idx_reflog_ref ON reflog (repo_id, ref_name, id);

PL/pgSQL Functions

git_object_hash(type, content) -- SHA1 of <type> <size>\0<content> using pgcrypto digest()
git_object_write(repo_id, type, content) -- compute hash, INSERT ON CONFLICT DO NOTHING, return oid
git_object_read(repo_id, oid) -- return (type, size, content)
git_object_read_prefix(repo_id, prefix, prefix_len) -- abbreviated OID lookup
git_tree_entries(content) -- parse binary tree into (mode, name, entry_oid) rows
git_commit_parse(content) -- parse commit into tree_oid, parent_oids[], author fields, committer fields, message
git_ref_update(repo_id, name, new_oid, old_oid, force) -- compare-and-swap with SELECT FOR UPDATE
git_ls_tree_r(repo_id, tree_oid, prefix) -- recursive tree walk returning (mode, path, oid, type)

Materialized Views

sql

CREATE MATERIALIZED VIEW commits_view ASSELECT o.repo_id, o.oid AS commit_oid, encode(o.oid, 'hex') AS sha,       c.tree_oid, c.parent_oids, c.author_name, c.author_email,       to_timestamp(c.author_timestamp) AS authored_at,       c.committer_name, c.committer_email,       to_timestamp(c.committer_timestamp) AS committed_at,       c.messageFROM objects o, LATERAL git_commit_parse(o.content) cWHERE o.type = 1;
CREATE MATERIALIZED VIEW tree_entries_view ASSELECT o.repo_id, o.oid AS tree_oid, e.mode, e.name, e.entry_oidFROM objects o, LATERAL git_tree_entries(o.content) eWHERE o.type = 2;

CREATE MATERIALIZED VIEW commits_view ASSELECT o.repo_id, o.oid AS commit_oid, encode(o.oid, 'hex') AS sha,       c.tree_oid, c.parent_oids, c.author_name, c.author_email,       to_timestamp(c.author_timestamp) AS authored_at,       c.committer_name, c.committer_email,       to_timestamp(c.committer_timestamp) AS committed_at,       c.messageFROM objects o, LATERAL git_commit_parse(o.content) cWHERE o.type = 1;
CREATE MATERIALIZED VIEW tree_entries_view ASSELECT o.repo_id, o.oid AS tree_oid, e.mode, e.name, e.entry_oidFROM objects o, LATERAL git_tree_entries(o.content) eWHERE o.type = 2;

libgit2 Backend (C)

This is the bridge. Implement git_odb_backend and git_refdb_backend structs that store/retrieve from Postgres via libpq. libgit2 handles all protocol work (pack negotiation, delta resolution, ref advertisement).

ODB Backend (`odb_postgres.c`)

Implements these callbacks from git_odb_backend:

read(data, len, type, backend, oid) -- SELECT type, size, content FROM objects WHERE repo_id=$1 AND oid=$2
read_header(len, type, backend, oid) -- SELECT type, size FROM objects WHERE ...
read_prefix(full_oid, data, len, type, backend, short_oid, prefix_len) -- prefix match on oid column
write(backend, oid, data, len, type) -- INSERT INTO objects ... ON CONFLICT DO NOTHING
exists(backend, oid) -- SELECT 1 FROM objects WHERE ...
exists_prefix(full_oid, backend, short_oid, prefix_len) -- prefix existence check
foreach(backend, cb, payload) -- SELECT oid FROM objects WHERE repo_id=$1, call cb for each
writepack(writepack_out, backend, odb, progress_cb, payload) -- return a git_odb_writepack that accumulates pack bytes, then on commit delegates to libgit2's indexer to extract objects and calls write for each
free(backend) -- close PG connection

The backend struct holds a PGconn* and the repo_id.

Refdb Backend (`refdb_postgres.c`)

Implements these callbacks from git_refdb_backend:

exists(exists_out, backend, ref_name) -- SELECT 1 FROM refs WHERE ...
lookup(ref_out, backend, ref_name) -- SELECT oid, symbolic FROM refs WHERE ..., construct git_reference
iterator(iter_out, backend, glob) -- SELECT name, oid, symbolic FROM refs WHERE name LIKE ..., return custom iterator
write(backend, ref, force, who, message, old_id, old_target) -- CAS update using a transaction with SELECT FOR UPDATE
rename(ref_out, backend, old_name, new_name, force, who, message) -- UPDATE refs SET name=$1
del(backend, ref_name, old_id, old_target) -- DELETE with CAS check
has_log / ensure_log / reflog_read / reflog_write / reflog_rename / reflog_delete -- operate on the reflog table
lock / unlock -- use Postgres advisory locks (pg_advisory_xact_lock)
free(backend) -- close connection

CLI Entry Point (`main.c`)

A program that serves one or more repos. For testing, it can run as a one-shot helper that git invokes. Longer term it could serve HTTP smart protocol or the git:// protocol.

Initial approach: implement a git-remote-gitgres helper. When you do git clone gitgres::postgres://localhost/mydb/myrepo, git invokes git-remote-gitgres postgres://localhost/mydb/myrepo. The helper:

Parses the connection string and repo name
Connects to Postgres
Creates a libgit2 repo with the Postgres ODB and refdb backends
Speaks the transport protocol over stdin/stdout

libgit2 has git_transport_register for custom transports, or we can use the simpler git_remote_callbacks approach.

C Extension (`ext/`)

The Postgres extension, separate from the libgit2 backend. Adds performance for SQL queries.

git_oid type: 20-byte fixed binary, hex I/O, btree + hash operator classes. Replaces bytea columns with a proper type so you can write WHERE oid = 'abc123...'.
git_object_hash_c(type, content) RETURNS git_oid: C SHA1 via OpenSSL, ~10x faster than pgcrypto
git_tree_entries_c(content) RETURNS SETOF (mode, name, oid): C pointer arithmetic instead of PL/pgSQL byte walking

Built with PGXS, links against OpenSSL.

Import Script (`import/gitgres-import.sh`)

Shell script that imports an existing git repo into the database using git plumbing commands and psql.

git -C <repo> rev-list --objects --all to get all object OIDs
Pipe through git -C <repo> cat-file --batch to get type + content
Compute hashes (or trust git), INSERT into objects table via psql COPY
git -C <repo> show-ref to get all refs, INSERT into refs table

Build Order

Schema + object hash + read/write functions -- the foundation. Verify that git_object_hash(3, 'hello') matches git hash-object --stdin.
Tree + commit parsing functions -- test against real objects from a git repo using git cat-file.
Import script -- import a small test repo (~50 commits). Verify object count matches git rev-list --objects --all | wc -l.
Materialized views -- REFRESH after import. Write example queries against commits and trees.
libgit2 ODB backend -- implement read/write/exists. Test by creating a libgit2 repo with the Postgres backend, writing objects through it, verifying they appear in the database.
libgit2 refdb backend -- implement CRUD + iterator. Test ref creation and lookup.
git-remote-gitgres helper -- wire up libgit2 transport so git push/clone work.
Roundtrip test -- push a repo, clone it elsewhere, diff the working trees.
C extension -- git_oid type, C hash, C tree parse. Benchmark against PL/pgSQL.
Scale test -- import homebrew-core.

Testing

Minitest. Each test connects to a gitgres_test database, runs schema setup in a BEGIN, rolls back in teardown. SQL functions tested by comparing output against git CLI on the same objects.

Roundtrip integration test: create temp repo with commits, push via the backend, clone into new directory, diff -r.

Key Decisions

bytea(20) for OIDs: half the storage of hex text, faster comparisons. The C extension upgrades this to a proper git_oid type.
Content stored without git header: type and size in columns, OID recomputed from type+content on write for verification.
libgit2 for the protocol bridge: it handles pack negotiation, delta resolution, ref advertisement, capabilities. We only implement storage.
writepack delegates to libgit2 indexer: when receiving a push, we let libgit2 parse the packfile and extract objects, then store them individually. We don't need our own pack parser.
pgcrypto for pure SQL SHA1: available on all managed Postgres services.
No delta compression in pack generation initially: libgit2 handles this when building packs for fetch.

If you need specific details from before exiting plan mode (like exact code snippets, error messages, or content you generated), read the full transcript at: /Users/andrew/.claude/projects/-Users-andrew-code-gitgres/0d86fef0-4a2d-473c-bb7e-29e502603055.jsonl

Let me check a few more key headers and then start writing the C code and tests in parallel.

Let me check a few more headers I need for the writepack and reflog implementation.

Good, I have a clear picture of all the APIs now. Let me also check the git_odb_new non-experimental API and the repository wrapping API.

Now let me check a couple more things and then write all the C files.

Now I have everything I need. Let me write all the C backend files, the import script, and the tests. I'll use parallel agents for the larger files.

Good, the C backend files are written. Now let me write the import script, C extension, and tests in parallel.

Now let me write all the test files and the C extension in parallel.

Good, all the files are written. Now let me verify everything compiles and the tests pass. Let me first check the pg gem is available, then run the tests.

Let me first drop and recreate the test database cleanly, then run the tests.

All 30 tests pass. Now let me try building the C backend.

Backend compiles. Let me now try building the C extension.

Missing includes in sha1_hash.c. Let me fix it and also check tree_parse.c.

The sha1_hash.c is missing #include "utils/builtins.h" which provides VARSIZE_ANY_EXHDR, VARDATA_ANY, etc. Let me also use the EVP API since the legacy SHA1 functions are deprecated in OpenSSL 3.

Tons of OpenSSL warnings (from the -Wcast-function-type flag in PGXS) but I need to check if it actually linked. Let me look at the end more carefully.

The issue is that PG 16 moved VARDATA macros to varatt.h. Let me fix the includes.

Extension builds successfully (it's .dylib on macOS). Zero errors, just OpenSSL warnings from their headers. Let me now do a quick integration test - install the extension and verify it works.

Owned by andrew but "Operation not permitted" - likely SIP. Let me try with install.

Sandbox restriction on Postgres.app. The C extension builds but can't be installed without disabling SIP protections on the app bundle. That's fine - the extension is a build step 9 optimization item. The core functionality (PL/pgSQL functions + libgit2 backend) all works without it.

Let me now do a proper end-to-end test with the backend binary. Let me first test the import script with a small repo.

Let me do the roundtrip test - create a repo, push it, clone it, diff the working trees.

Now clone it back and diff.

Full roundtrip works. Push to Postgres, clone back, working trees are identical, git history is preserved.

Let me update the task status and also verify the materialized views work.

Everything works. Let me now run the full test suite one more time and then mark tests complete.

Let me also initialize a git repo for the project, now that you mentioned wanting a Makefile (the top-level one already exists).

The server is PG 16 (Postgres.app) but the pg_config client is PG 14 (Homebrew). For the C extension we need matching headers, but for everything else it doesn't matter much. Let me check what's available and get started.

All three Makefiles exist and work. The top-level one you asked about was already created.

Let me also update it to exclude the views from createdb since they depend on data existing, and fix the test runner path.

Now let me initialize the git repo and take a look at the final file list.

Here's the final state of the project:

SQL layer (all working, tested):

sql/schema.sql - tables for repositories, objects, refs, reflog with pgcrypto
sql/functions/ - PL/pgSQL functions: git_object_hash, git_object_write, git_object_read, git_tree_entries, git_ls_tree_r, git_commit_parse, git_ref_update, git_ref_set_symbolic
sql/views/queryable.sql - materialized views for commits and tree entries

libgit2 backend (compiles, roundtrip verified):

backend/odb_postgres.c - ODB backend storing objects in Postgres via libpq
backend/refdb_postgres.c - refdb backend storing refs in Postgres
backend/writepack_postgres.c - pack file receiver using libgit2's indexer
backend/main.c - CLI tool with init, push, clone, ls-refs commands

C extension (compiles, can't install to Postgres.app due to sandbox):

ext/git_oid_type.c - custom 20-byte git_oid type with btree/hash operator classes
ext/sha1_hash.c - C SHA1 via OpenSSL EVP API
ext/tree_parse.c - C tree entry parser as SRF

Import script: import/gitgres-import.sh - imports repos via git plumbing + psql

Tests: 30 tests, 78 assertions, all passing. Covers schema constraints, object hashing (verified against git hash-object), object store CRUD, tree parsing, commit parsing, ref CAS updates, and a roundtrip integration test.

Verified roundtrip: pushed a 2-commit repo to Postgres, cloned it back, diff -r shows identical working trees, git log preserved.

ok lets add a readme and git init a repo and commit with "Hello world"

Done. 33 files, one commit: 23ffdab Hello world.

is there a proper .gitignore

Pretty thin. Let me flesh it out.

Want me to amend the commit with this, or leave it for the next one?

new commit is fine, make sure we didn't acceientally commit any files that we ignored

Clean - nothing tracked that should be ignored.

Let me start by checking the current state of the project and then work through the build order.

All good. No accidentally committed build artifacts.

add a prior art section to the readme with links

Good, PG 18 is available via Postgres.app and libgit2 is installed. Let me set up for PG 16 (the running server) and create the project structure.

Done. Five projects with links and a short note on what each does differently from gitgres.

so what would it take to make this talk to a regular git client

Right now gitgres-backend push and clone work, but they're our own CLI, not something git push origin main knows about. The gap is a git remote helper -- a program called git-remote-gitgres that git invokes automatically when it sees a URL with the gitgres:: scheme.

You'd use it like:

git remote add pg gitgres::dbname=gitgres/myrepogit push pg maingit clone gitgres::dbname=gitgres/myrepo

git remote add pg gitgres::dbname=gitgres/myrepogit push pg maingit clone gitgres::dbname=gitgres/myrepo

Git launches git-remote-gitgres dbname=gitgres/myrepo and talks to it over stdin/stdout with a simple text protocol. The helper needs to handle four commands:

capabilities -- tell git what we support. We'd advertise fetch, push, and option.

list -- query the refs table, print each one as <hex-sha> <refname>\n. Git uses this for git ls-remote, fetch negotiation, and push target resolution.

fetch <sha> <ref> -- git sends one or more of these, ending with a blank line. We need to send back the requested objects and everything they reference. The practical way: generate a packfile covering the requested OIDs using libgit2's git_packbuilder, write it into the local repo's object store. Or simpler still, just walk from each requested OID and write loose objects into the local repo via a temporary ODB.

push <src>:<dst> -- git sends refspecs. We read objects from the local repo that the remote doesn't have, write them to postgres, update the ref. Then print ok <ref>\n for each one.

The hard part is fetch. When git asks for a set of commits, it also tells us (via the list output) what it already has, so we could compute the delta. But for a first pass we can skip negotiation and just transfer everything reachable from the requested OIDs that isn't already local -- which is what git_packbuilder does when you add "wants" and "haves".

The alternative that avoids writing pack protocol code entirely: use the import/export capabilities with git's fast-import format. For fetch, we output a fast-import stream (commits, blobs, trees as text). For push, we read a fast-export stream and parse it into objects. This is what git-remote-hg and git-remote-bzr did. It's simpler but slower for large repos.

In terms of actual code, we already have all the storage operations. The new work is:

A main() that reads stdin line by line and dispatches commands (~200 lines)
A list handler that queries refs (~30 lines, we already have ls-refs)
A push handler that walks local objects and writes to postgres (~100 lines, we already have this in cmd_push)
A fetch handler that walks postgres objects and writes to local (~150 lines, similar to cmd_clone but incremental)
Drop the binary as git-remote-gitgres on PATH

Want me to build it?

yes build it

Using PostgreSQL to Store Git Data

Using PostgreSQL to Store Git DataPublic

Summary

Gitgres: PostgreSQL as a Git Backend

Context

Project Structure

Schema

PL/pgSQL Functions

Materialized Views

libgit2 Backend (C)

ODB Backend (`odb_postgres.c`)

Refdb Backend (`refdb_postgres.c`)

CLI Entry Point (`main.c`)

C Extension (`ext/`)

Import Script (`import/gitgres-import.sh`)

Build Order

Testing

Key Decisions

Summary

Gitgres: PostgreSQL as a Git Backend

Context

Project Structure

Schema

PL/pgSQL Functions

Materialized Views

libgit2 Backend (C)

ODB Backend (odb_postgres.c)

Refdb Backend (refdb_postgres.c)

CLI Entry Point (main.c)

C Extension (ext/)

Import Script (import/gitgres-import.sh)

Build Order

Testing

Key Decisions

ODB Backend (`odb_postgres.c`)

Refdb Backend (`refdb_postgres.c`)

CLI Entry Point (`main.c`)

C Extension (`ext/`)

Import Script (`import/gitgres-import.sh`)