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

Schema

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

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

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:

1. Parses the connection string and repo name
2. Connects to Postgres
3. Creates a libgit2 repo with the Postgres ODB and refdb backends
4. 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.

Usage: gitgres-import <repo-path> <conninfo> <repo-name>

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

Build Order

1. Schema + object hash + read/write functions -- the foundation. Verify that git_object_hash(3, 'hello') matches git hash-object --stdin.

2. Tree + commit parsing functions -- test against real objects from a git repo using git cat-file.

3. Import script -- import a small test repo (~50 commits). Verify object count matches git rev-list --objects --all | wc -l.

4. Materialized views -- REFRESH after import. Write example queries against commits and trees.

5. 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.

6. libgit2 refdb backend -- implement CRUD + iterator. Test ref creation and lookup.

7. git-remote-gitgres helper -- wire up libgit2 transport so git push/clone work.

8. Roundtrip test -- push a repo, clone it elsewhere, diff the working trees.

9. C extension -- git_oid type, C hash, C tree parse. Benchmark against PL/pgSQL.

10. 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