Now I have all the research I need. Let me synthesize this into a detailed implementation plan. --- # Gitgres Implementation Plan ## The Core Idea Store git objects and refs in PostgreSQL tables. Bridge the git protocol so that `git push` writes objects into the database and `git clone`/`fetch` reads them back out. The pure SQL version uses pgcrypto for SHA1 hashing and stores everything as `bytea`. The C extension version adds a native `git_oid` type, faster pack parsing, and zlib decompression inside Postgres. ## Project Structure ``` gitgres/ README.md LICENSE Makefile # top-level: delegates to sub-makes sql/ schema.sql # core tables and indexes functions/ object_hash.sql # SHA1 computation for git objects object_read_write.sql # insert/lookup objects tree_parse.sql # parse binary tree entries commit_parse.sql # parse commit text format ref_manage.sql # ref CRUD with CAS pack_parse.sql # packfile parsing (pure SQL, limited) pack_build.sql # packfile generation for fetch pkt_line.sql # pkt-line encoding/decoding helpers import_export.sql # fast-import/fast-export SQL helpers views/ queryable.sql # materialized views for querying seed.sql # test data bridge/ git-remote-gitgres # Ruby script: git remote helper Gemfile lib/ gitgres/ connection.rb # PG connection management pkt_line.rb # pkt-line read/write pack_reader.rb # packfile parser (receives push data) pack_writer.rb # packfile builder (serves fetch data) ref_advertisement.rb # ref discovery responses receive_pack.rb # handle git push upload_pack.rb # handle git clone/fetch fast_transport.rb # fast-import/fast-export bridge ext/ gitgres/ Makefile # PGXS makefile gitgres.c # PG_MODULE_MAGIC, init git_oid_type.c # custom git_oid type (20-byte binary) sha1_hash.c # C-level SHA1 (OpenSSL) zlib_inflate.c # raw zlib inflate for pack objects pack_parse.c # C-level packfile parser gitgres.control # extension metadata sql/ gitgres--0.1.sql # CREATE TYPE, CREATE FUNCTION test/ test_helper.rb schema_test.rb # table creation, constraints object_hash_test.rb # SHA1 computation matches git object_store_test.rb # write and read objects tree_parse_test.rb # parse tree binary format commit_parse_test.rb # parse commit text format ref_test.rb # ref operations, CAS pack_parse_test.rb # packfile parsing pack_build_test.rb # packfile generation pkt_line_test.rb # pkt-line encoding remote_helper_test.rb # end-to-end push/clone integration_test.rb # git push && git clone roundtrip ``` ## Phase 1: Schema and Object Store (Pure SQL) ### Tables ```sql CREATE EXTENSION IF NOT EXISTS pgcrypto; -- One row per hosted repository CREATE TABLE repositories ( id serial PRIMARY KEY, name text NOT NULL UNIQUE, created_at timestamptz NOT NULL DEFAULT now() ); -- Git objects: blob, tree, commit, tag -- Stores the raw, uncompressed content (not the header). -- The OID is computed as SHA1(" \0"). CREATE TABLE objects ( repo_id integer NOT NULL REFERENCES repositories(id), oid bytea NOT NULL, -- 20 bytes, SHA1 type smallint NOT NULL, -- 1=commit, 2=tree, 3=blob, 4=tag size integer NOT NULL, -- byte length of content content bytea NOT NULL, -- raw object content (uncompressed) PRIMARY KEY (repo_id, oid) ); -- B-tree index on oid alone for prefix lookups CREATE INDEX idx_objects_oid ON objects (oid); -- Git references CREATE TABLE refs ( repo_id integer NOT NULL REFERENCES repositories(id), name text NOT NULL, -- e.g. "refs/heads/main" oid bytea, -- 20 bytes, target SHA1 (NULL for symbolic) symbolic text, -- target ref name (NULL for direct) PRIMARY KEY (repo_id, name), CHECK ((oid IS NOT NULL) != (symbolic IS NOT NULL)) ); -- Reflog entries CREATE TABLE reflog ( id bigserial PRIMARY KEY, repo_id integer NOT NULL REFERENCES repositories(id), ref_name text NOT NULL, old_oid bytea, -- 20 bytes new_oid bytea, -- 20 bytes committer text NOT NULL, -- "Name " timestamp_s bigint NOT NULL, tz_offset text NOT NULL, -- e.g. "+0000" message text, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX idx_reflog_ref ON reflog (repo_id, ref_name, id); ``` The `objects` table is deliberately flat, matching the libgit2-backends/SQLite approach. One row per object per repo. The OID is stored as 20 raw bytes in `bytea`, not as 40-character hex text. This halves index size and makes comparisons faster. The type column uses git's internal numbering (1=commit, 2=tree, 3=blob, 4=tag) so pack format type codes map directly. ### Object Hash Function ```sql -- Compute the git object ID (SHA1 of " \0") CREATE OR REPLACE FUNCTION git_object_hash( obj_type smallint, content bytea ) RETURNS bytea AS $$ DECLARE type_name text; header bytea; BEGIN type_name := CASE obj_type WHEN 1 THEN 'commit' WHEN 2 THEN 'tree' WHEN 3 THEN 'blob' WHEN 4 THEN 'tag' END; -- Build header: " \0" header := convert_to(type_name || ' ' || octet_length(content)::text, 'UTF8') || '\x00'::bytea; RETURN digest(header || content, 'sha1'); END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; ``` ### Object Write with Hash Verification ```sql CREATE OR REPLACE FUNCTION git_object_write( p_repo_id integer, p_type smallint, p_content bytea ) RETURNS bytea AS $$ DECLARE computed_oid bytea; BEGIN computed_oid := git_object_hash(p_type, p_content); INSERT INTO objects (repo_id, oid, type, size, content) VALUES (p_repo_id, computed_oid, p_type, octet_length(p_content), p_content) ON CONFLICT (repo_id, oid) DO NOTHING; RETURN computed_oid; END; $$ LANGUAGE plpgsql; ``` ### Object Read ```sql CREATE OR REPLACE FUNCTION git_object_read( p_repo_id integer, p_oid bytea ) RETURNS TABLE(type smallint, size integer, content bytea) AS $$ SELECT type, size, content FROM objects WHERE repo_id = p_repo_id AND oid = p_oid; $$ LANGUAGE sql STABLE; -- Prefix lookup: find object by abbreviated OID CREATE OR REPLACE FUNCTION git_object_read_prefix( p_repo_id integer, p_oid_prefix bytea, p_prefix_len integer -- in hex chars (4-40) ) RETURNS TABLE(oid bytea, type smallint, size integer, content bytea) AS $$ DECLARE byte_len integer := p_prefix_len / 2; BEGIN -- For even-length prefixes, exact byte prefix match IF p_prefix_len % 2 = 0 THEN RETURN QUERY SELECT o.oid, o.type, o.size, o.content FROM objects o WHERE o.repo_id = p_repo_id AND substring(o.oid FROM 1 FOR byte_len) = p_oid_prefix; ELSE -- Odd-length: match full bytes plus upper nibble of next byte RETURN QUERY SELECT o.oid, o.type, o.size, o.content FROM objects o WHERE o.repo_id = p_repo_id AND substring(o.oid FROM 1 FOR byte_len) = substring(p_oid_prefix FROM 1 FOR byte_len) AND (get_byte(o.oid, byte_len) >> 4) = (get_byte(p_oid_prefix, byte_len) >> 4); END IF; END; $$ LANGUAGE plpgsql STABLE; ``` ### Ref Management ```sql -- Ref write with compare-and-swap CREATE OR REPLACE FUNCTION git_ref_update( p_repo_id integer, p_name text, p_new_oid bytea, p_old_oid bytea DEFAULT NULL, -- NULL means "create only" p_force boolean DEFAULT false ) RETURNS boolean AS $$ DECLARE current_oid bytea; BEGIN -- Lock the row (or gap) to prevent races SELECT oid INTO current_oid FROM refs WHERE repo_id = p_repo_id AND name = p_name FOR UPDATE; IF NOT FOUND THEN -- Creating new ref IF p_old_oid IS NOT NULL AND p_old_oid != '\x0000000000000000000000000000000000000000'::bytea THEN RETURN false; -- Expected existing ref, but none found END IF; INSERT INTO refs (repo_id, name, oid) VALUES (p_repo_id, p_name, p_new_oid); RETURN true; END IF; -- Updating existing ref IF NOT p_force AND p_old_oid IS NOT NULL AND current_oid != p_old_oid THEN RETURN false; -- CAS failure END IF; IF p_new_oid = '\x0000000000000000000000000000000000000000'::bytea THEN DELETE FROM refs WHERE repo_id = p_repo_id AND name = p_name; ELSE UPDATE refs SET oid = p_new_oid, symbolic = NULL WHERE repo_id = p_repo_id AND name = p_name; END IF; RETURN true; END; $$ LANGUAGE plpgsql; ``` ## Phase 2: Parsing Functions ### Tree Parsing (Pure SQL) Tree entries are binary: ` \0<20-byte-sha1>`. Parsing this in PL/pgSQL means walking byte-by-byte through the bytea. It is slow but works. ```sql -- Parse a tree object into its entries CREATE OR REPLACE FUNCTION git_tree_entries( p_tree_content bytea ) RETURNS TABLE(mode text, name text, entry_oid bytea) AS $$ DECLARE pos integer := 1; total integer := octet_length(p_tree_content); space_pos integer; null_pos integer; mode_bytes bytea; name_bytes bytea; BEGIN WHILE pos <= total LOOP -- Find the space separating mode from name space_pos := position('\x20'::bytea IN substring(p_tree_content FROM pos)); IF space_pos = 0 THEN EXIT; END IF; space_pos := pos + space_pos - 1; mode_bytes := substring(p_tree_content FROM pos FOR space_pos - pos); -- Find the null byte after name null_pos := position('\x00'::bytea IN substring(p_tree_content FROM space_pos + 1)); IF null_pos = 0 THEN EXIT; END IF; null_pos := space_pos + null_pos; -- absolute position of \0 name_bytes := substring(p_tree_content FROM space_pos + 1 FOR null_pos - space_pos - 1); mode := convert_from(mode_bytes, 'UTF8'); name := convert_from(name_bytes, 'UTF8'); entry_oid := substring(p_tree_content FROM null_pos + 1 FOR 20); pos := null_pos + 21; RETURN NEXT; END LOOP; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; ``` ### Commit Parsing Commits are text-based (headers separated by newlines, blank line before message body), which makes them easier to parse in SQL. ```sql CREATE OR REPLACE FUNCTION git_commit_parse( p_content bytea ) RETURNS TABLE( tree_oid bytea, parent_oids bytea[], author_name text, author_email text, author_timestamp bigint, author_tz text, committer_name text, committer_email text, committer_timestamp bigint, committer_tz text, message text ) AS $$ DECLARE txt text; lines text[]; line text; header_end integer; i integer; parents bytea[] := '{}'; parts text[]; BEGIN txt := convert_from(p_content, 'UTF8'); header_end := position(E'\n\n' IN txt); -- Parse headers lines := string_to_array(substring(txt FROM 1 FOR header_end - 1), E'\n'); FOR i IN 1..array_length(lines, 1) LOOP line := lines[i]; IF line LIKE 'tree %' THEN tree_oid := decode(substring(line FROM 6), 'hex'); ELSIF line LIKE 'parent %' THEN parents := array_append(parents, decode(substring(line FROM 8), 'hex')); ELSIF line LIKE 'author %' THEN -- Parse: "author Name timestamp tz" parts := regexp_match(substring(line FROM 8), '^(.+?) <(.+?)> (\d+) ([+-]\d{4})$'); IF parts IS NOT NULL THEN author_name := parts[1]; author_email := parts[2]; author_timestamp := parts[3]::bigint; author_tz := parts[4]; END IF; ELSIF line LIKE 'committer %' THEN parts := regexp_match(substring(line FROM 11), '^(.+?) <(.+?)> (\d+) ([+-]\d{4})$'); IF parts IS NOT NULL THEN committer_name := parts[1]; committer_email := parts[2]; committer_timestamp := parts[3]::bigint; committer_tz := parts[4]; END IF; END IF; END LOOP; parent_oids := parents; message := substring(txt FROM header_end + 2); RETURN NEXT; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; ``` ## Phase 3: Materialized Queryable Views Once objects are in Postgres, the whole point is querying them with SQL. ```sql -- Parsed commits view CREATE MATERIALIZED VIEW commits_view AS SELECT o.repo_id, o.oid AS commit_oid, encode(o.oid, 'hex') AS commit_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.message FROM objects o, LATERAL git_commit_parse(o.content) c WHERE o.type = 1; -- commit CREATE UNIQUE INDEX idx_commits_view ON commits_view (repo_id, commit_oid); -- Parsed trees view (flattened) CREATE MATERIALIZED VIEW tree_entries_view AS SELECT o.repo_id, o.oid AS tree_oid, e.mode, e.name, e.entry_oid FROM objects o, LATERAL git_tree_entries(o.content) e WHERE o.type = 2; -- tree CREATE INDEX idx_tree_entries ON tree_entries_view (repo_id, tree_oid); CREATE INDEX idx_tree_entries_name ON tree_entries_view (repo_id, name); -- Refs with resolved targets CREATE VIEW refs_view AS SELECT r.repo_id, rp.name AS repo_name, r.name AS ref_name, encode(COALESCE(r.oid, r2.oid), 'hex') AS target_sha, r.symbolic IS NOT NULL AS is_symbolic, r.symbolic FROM refs r JOIN repositories rp ON rp.id = r.repo_id LEFT JOIN refs r2 ON r2.repo_id = r.repo_id AND r2.name = r.symbolic; -- File listing at a given commit (recursive tree walk) CREATE OR REPLACE FUNCTION git_ls_tree_r( p_repo_id integer, p_tree_oid bytea, p_prefix text DEFAULT '' ) RETURNS TABLE(mode text, path text, entry_oid bytea, obj_type smallint) AS $$ DECLARE entry RECORD; sub_content bytea; BEGIN FOR entry IN SELECT e.mode, e.name, e.entry_oid FROM git_tree_entries( (SELECT content FROM objects WHERE repo_id = p_repo_id AND oid = p_tree_oid) ) e LOOP IF entry.mode = '40000' THEN -- Subtree: recurse RETURN QUERY SELECT * FROM git_ls_tree_r( p_repo_id, entry.entry_oid, p_prefix || entry.name || '/' ); ELSE mode := entry.mode; path := p_prefix || entry.name; entry_oid := entry.entry_oid; obj_type := 3; -- blob RETURN NEXT; END IF; END LOOP; END; $$ LANGUAGE plpgsql STABLE; ``` These let you write queries like: ```sql -- All commits by a given author in the last month SELECT commit_sha, message, authored_at FROM commits_view WHERE repo_id = 1 AND author_email = 'dev@example.com' AND authored_at > now() - interval '1 month' ORDER BY authored_at DESC; -- Files changed between two commits (via tree diff) -- List all files at HEAD SELECT path, encode(entry_oid, 'hex') FROM git_ls_tree_r(1, (SELECT tree_oid FROM commits_view WHERE commit_sha = 'abc...')) ORDER BY path; ``` ## Phase 4: The Git Protocol Bridge This is the novel and most involved part. The bridge is a Ruby script named `git-remote-gitgres` that implements the git remote helper protocol. It sits on the user's PATH. When a user runs `git push gitgres://mydb/myrepo`, git invokes `git-remote-gitgres gitgres://mydb/myrepo` and communicates over stdin/stdout. The bridge uses the **`connect` capability**, which means git handles the pack protocol negotiation itself and the bridge just needs to pipe bytes between git's native upload-pack/receive-pack and the database. But since there is no actual git process on the other end, the bridge must *be* upload-pack and receive-pack. That means the bridge needs to: **For `git push` (receive-pack side):** 1. Advertise refs from the database (pkt-line format) 2. Receive the list of ref updates (old-oid, new-oid, refname) 3. Receive the packfile 4. Parse the packfile into individual objects 5. Insert each object into the `objects` table 6. Update refs (with CAS using old-oid) 7. Send report-status back **For `git clone`/`fetch` (upload-pack side):** 1. Advertise refs from the database 2. Receive want/have lines 3. Determine which objects are needed 4. Build a packfile from those objects 5. Send the packfile back ### Approach: Use `fetch`/`push` capabilities instead of `connect` The `connect` capability requires implementing the full bidirectional pack protocol. A simpler approach: use the `fetch` and `push` capabilities of the remote helper protocol, paired with `git fast-import` and `git fast-export`. This way git handles all the pack/object format work, and the bridge only needs to shuttle text-format streams. But this has limitations -- `git fast-export` works from a local repo, and we want to serve from the database directly. The cleaner path is to use the remote helper `connect` capability but implement a minimal receive-pack and upload-pack inside the bridge. Actually, the simplest working approach is even more direct: **implement the HTTP smart protocol** behind a small HTTP server (WEBrick or similar thin Ruby server). Then users add a remote with `git remote add pg http://localhost:9418/myrepo` and push/fetch works with zero custom protocol work -- git's built-in HTTP transport handles everything. I recommend the HTTP approach for the initial implementation. Here's why: - The smart HTTP protocol is well-documented and widely tested - We only need two endpoints: `GET /info/refs` and `POST /git-upload-pack` or `POST /git-receive-pack` - The packfile parsing/generation code is needed either way - Debugging is easier (curl, browser, wireshark) ### HTTP Bridge Structure ```ruby # bridge/lib/gitgres/server.rb # # Endpoints: # GET /:repo/info/refs?service=git-upload-pack # GET /:repo/info/refs?service=git-receive-pack # POST /:repo/git-upload-pack # POST /:repo/git-receive-pack class Gitgres::Server def handle_info_refs(repo_name, service) # Query refs from database # Format as pkt-line advertisement # Return with correct content-type end def handle_receive_pack(repo_name, request_body) # Parse command lines (old_oid new_oid refname) # Parse packfile from request body # Insert objects into database # Update refs with CAS # Return report-status end def handle_upload_pack(repo_name, request_body) # Parse want/have lines # Walk object graph to find needed objects # Build packfile # Return packfile (possibly with side-band-64k framing) end end ``` ### Packfile Parsing (Ruby, for receive-pack) The bridge must parse incoming packfiles. This is the hardest part of the write path. ```ruby # bridge/lib/gitgres/pack_reader.rb class Gitgres::PackReader # Parse a packfile stream into individual objects # # Packfile format: # 4 bytes: "PACK" # 4 bytes: version (network order, must be 2) # 4 bytes: object count (network order) # N object entries (variable length) # 20 bytes: SHA1 checksum # # Each object entry: # variable-length type+size header # for OFS_DELTA: variable-length negative offset # for REF_DELTA: 20-byte base object SHA1 # zlib-compressed data def parse(io) # Returns array of {type:, content:, oid:} hashes end def read_object_header(io) # Read type (3 bits) and size (variable-length) end def apply_delta(base_content, delta_data) # Apply copy/insert delta instructions end end ``` ### Packfile Generation (Ruby, for upload-pack) ```ruby # bridge/lib/gitgres/pack_writer.rb class Gitgres::PackWriter # Generate a packfile from a set of object OIDs # # For the initial version, emit undeltified objects only. # Delta compression is an optimization for later. def build(objects) # Returns binary packfile data # Header: "PACK" + version(2) + count # For each object: type+size header + zlib-compressed content # Trailer: SHA1 of everything above end end ``` ### Object Graph Walking (for fetch/clone) When a client sends `want ` and `have ` lines, the server needs to determine which objects to send. For a full clone (no `have` lines), this means walking every reachable object from the wanted commits. For incremental fetch, it means walking from wanted commits back to the common ancestors, collecting objects along the way. ```ruby # bridge/lib/gitgres/object_walk.rb class Gitgres::ObjectWalk # Given a set of "want" OIDs and "have" OIDs, # determine the set of objects to include in the packfile. # # Algorithm: # 1. Start from each wanted commit # 2. Walk parents recursively until we reach a "have" commit (or root) # 3. For each commit in the walk, include: # - The commit object itself # - Its tree (and all subtrees/blobs, recursively) # - Skip trees/blobs already reachable from "have" commits def needed_objects(repo_id, wants, haves) # Uses recursive CTE in SQL for the commit walk, # then collects trees and blobs end end ``` The commit walk can be done efficiently with a recursive CTE: ```sql -- Find all commits reachable from :want_oids but not from :have_oids WITH RECURSIVE commit_walk AS ( -- Base case: the wanted commits SELECT oid, content FROM objects WHERE repo_id = :repo_id AND oid = ANY(:want_oids) AND type = 1 UNION -- Recursive case: parents of commits in the walk SELECT o.oid, o.content FROM commit_walk cw, LATERAL git_commit_parse(cw.content) cp, LATERAL unnest(cp.parent_oids) AS parent_oid, objects o WHERE o.repo_id = :repo_id AND o.oid = parent_oid AND o.type = 1 AND o.oid != ALL(:have_oids) ) SELECT oid FROM commit_walk; ``` ## Phase 5: The C Extension The C extension replaces the performance-sensitive parts. Here is what it provides over pure SQL. ### 1. Native `git_oid` Type A fixed 20-byte binary type with hex input/output, btree and hash operator classes. This is essentially what the `hashtypes` extension provides, but bundled so there's no external dependency. ```c // ext/gitgres/git_oid_type.c PG_FUNCTION_INFO_V1(git_oid_in); // Parse hex string -> 20 bytes PG_FUNCTION_INFO_V1(git_oid_out); // 20 bytes -> hex string PG_FUNCTION_INFO_V1(git_oid_recv); // Binary receive PG_FUNCTION_INFO_V1(git_oid_send); // Binary send PG_FUNCTION_INFO_V1(git_oid_eq); PG_FUNCTION_INFO_V1(git_oid_ne); PG_FUNCTION_INFO_V1(git_oid_lt); PG_FUNCTION_INFO_V1(git_oid_le); PG_FUNCTION_INFO_V1(git_oid_gt); PG_FUNCTION_INFO_V1(git_oid_ge); PG_FUNCTION_INFO_V1(git_oid_cmp); PG_FUNCTION_INFO_V1(git_oid_hash); ``` With this type, the schema changes from `oid bytea` to `oid git_oid`, and you can write `WHERE oid = 'a1b2c3...'` without explicit `decode()` calls. ### 2. C-level SHA1 ```c // ext/gitgres/sha1_hash.c // git_object_hash_c(type smallint, content bytea) RETURNS git_oid // // Computes SHA1(" \0") in C using OpenSSL. // ~10x faster than pgcrypto's digest() for small objects. PG_FUNCTION_INFO_V1(git_object_hash_c); ``` ### 3. C-level Tree Parsing The PL/pgSQL tree parser walks bytea byte-by-byte with `get_byte()` and `substring()`, which is extremely slow for large trees. The C version uses pointer arithmetic. ```c // ext/gitgres/pack_parse.c // git_tree_entries_c(content bytea) RETURNS SETOF (mode text, name text, oid git_oid) PG_FUNCTION_INFO_V1(git_tree_entries_c); ``` ### 4. C-level Pack Parsing For very large pushes, parsing the packfile inside Postgres (as a stored procedure that receives the raw bytes) avoids round-tripping every object through the bridge. This is an optimization for after the bridge works correctly. ```c // git_unpack_objects(repo_id int, packdata bytea) RETURNS integer // Parses a packfile and inserts all objects into the objects table. // Returns the number of objects inserted. // Handles OFS_DELTA and REF_DELTA by resolving bases from the same pack // or from existing objects in the database. PG_FUNCTION_INFO_V1(git_unpack_objects); ``` ### 5. C-level Zlib Inflate ```c // git_inflate(data bytea) RETURNS bytea // Raw zlib inflate. Needed for pack object decompression if we // move pack parsing into Postgres. PG_FUNCTION_INFO_V1(git_inflate); ``` ### Extension SQL File ```sql -- ext/gitgres/sql/gitgres--0.1.sql -- Type CREATE TYPE git_oid; CREATE FUNCTION git_oid_in(cstring) RETURNS git_oid AS 'gitgres' LANGUAGE C IMMUTABLE STRICT; CREATE FUNCTION git_oid_out(git_oid) RETURNS cstring AS 'gitgres' LANGUAGE C IMMUTABLE STRICT; -- ... recv, send, operators, operator class ... CREATE TYPE git_oid ( INPUT = git_oid_in, OUTPUT = git_oid_out, RECEIVE = git_oid_recv, SEND = git_oid_send, INTERNALLENGTH = 20, PASSEDBYVALUE = false, ALIGNMENT = char, STORAGE = plain ); -- Operator class for btree CREATE OPERATOR CLASS git_oid_ops DEFAULT FOR TYPE git_oid USING btree AS OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 =, OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 git_oid_cmp(git_oid, git_oid); -- Hash operator class CREATE OPERATOR CLASS git_oid_hash_ops DEFAULT FOR TYPE git_oid USING hash AS OPERATOR 1 =, FUNCTION 1 git_oid_hash(git_oid); -- Functions CREATE FUNCTION git_object_hash_c(smallint, bytea) RETURNS git_oid AS 'gitgres' LANGUAGE C IMMUTABLE STRICT; CREATE FUNCTION git_tree_entries_c(bytea) RETURNS TABLE(mode text, name text, entry_oid git_oid) AS 'gitgres' LANGUAGE C IMMUTABLE STRICT ROWS 100; CREATE FUNCTION git_inflate(bytea) RETURNS bytea AS 'gitgres' LANGUAGE C IMMUTABLE STRICT; CREATE FUNCTION git_unpack_objects(integer, bytea) RETURNS integer AS 'gitgres' LANGUAGE C VOLATILE; ``` ### Build System ```makefile # ext/gitgres/Makefile (PGXS) MODULE_big = gitgres OBJS = gitgres.o git_oid_type.o sha1_hash.o zlib_inflate.o pack_parse.o EXTENSION = gitgres DATA = sql/gitgres--0.1.sql SHLIB_LINK = -lssl -lcrypto -lz PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) ``` ## Phase 6: Import/Export Utility Before the full protocol bridge works, we need a way to get repos in and out of the database. A Ruby script that shells out to git and uses the `pg` gem. ```ruby # bridge/bin/gitgres-import # Usage: gitgres-import

# # 1. Opens the git repo with Rugged (or shells out to git) # 2. Walks all objects using `git rev-list --objects --all` # 3. For each object, reads its type and content with `git cat-file` # 4. Inserts into the objects table # 5. Reads all refs with `git show-ref` and inserts into refs table # bridge/bin/gitgres-export # Usage: gitgres-export

# # 1. Creates a bare git repo at output-path # 2. Reads all objects from the database # 3. Writes each with `git hash-object -w --stdin` (with type flag) # 4. Reads all refs from the database # 5. Writes them with `git update-ref` ``` The import path using `git cat-file --batch` is fast enough for initial testing. For homebrew-core scale (hundreds of thousands of objects), we'd batch the INSERT with `COPY`. ## Testing Strategy Tests use Minitest (per the preference for test unit style over rspec). Each test file connects to a test database, creates the schema in a transaction, runs assertions, and rolls back. ```ruby # test/test_helper.rb require 'minitest/autorun' require 'pg' class GitgresTest < Minitest::Test def setup @conn = PG.connect(dbname: 'gitgres_test') @conn.exec("BEGIN") # Load schema @conn.exec(File.read('sql/schema.sql')) # Load functions Dir['sql/functions/*.sql'].sort.each { |f| @conn.exec(File.read(f)) } # Create test repo @conn.exec("INSERT INTO repositories (name) VALUES ('test')") @repo_id = @conn.exec("SELECT id FROM repositories WHERE name = 'test'")[0]['id'].to_i end def teardown @conn.exec("ROLLBACK") @conn.close end end ``` Key test cases: 1. **`object_hash_test.rb`**: Compute SHA1 for known blob/tree/commit content, compare with `git hash-object`. 2. **`object_store_test.rb`**: Write an object, read it back, verify content and type match. 3. **`tree_parse_test.rb`**: Parse a known tree binary blob, verify mode/name/oid of each entry. 4. **`commit_parse_test.rb`**: Parse a known commit, verify tree, parents, author, committer, message. 5. **`ref_test.rb`**: Create/update/delete refs, test CAS (compare-and-swap) semantics. 6. **`pack_parse_test.rb`**: Generate a packfile with `git pack-objects`, parse it in Ruby, verify objects match. 7. **`pack_build_test.rb`**: Build a packfile from known objects, verify `git index-pack` accepts it. 8. **`pkt_line_test.rb`**: Encode/decode pkt-lines, test flush packets, test the "# service=" header. 9. **`integration_test.rb`**: Start the HTTP server, `git push` a small repo, `git clone` from it, verify the clone matches the original. This is the acid test. For the integration test, create a temp git repo with a few commits, push it to the gitgres server, clone from the server into a new directory, and `diff -r` the two working trees. ## Build Order The work naturally sequences into milestones where each produces something testable. **Milestone 1: Schema + Object Store (days 1-2)** - `sql/schema.sql` - `sql/functions/object_hash.sql` - `sql/functions/object_read_write.sql` - `test/schema_test.rb`, `test/object_hash_test.rb`, `test/object_store_test.rb` - At this point you can verify that `git_object_hash(3, 'hello')` produces the same SHA1 as `echo -n hello | git hash-object --stdin`. **Milestone 2: Parsing Functions (days 3-4)** - `sql/functions/tree_parse.sql` - `sql/functions/commit_parse.sql` - `test/tree_parse_test.rb`, `test/commit_parse_test.rb` - Test against real tree and commit objects extracted from a git repo with `git cat-file`. **Milestone 3: Import Utility (day 5)** - `bridge/bin/gitgres-import` - Import a small repo (say 50 commits). Verify the object count matches `git rev-list --objects --all | wc -l`. Verify a few random objects match `git cat-file -p`. **Milestone 4: Materialized Views (day 5)** - `sql/views/queryable.sql` - Run `REFRESH MATERIALIZED VIEW` after import. Write example queries against commits_view and tree_entries_view. **Milestone 5: Ref Management + Pkt-line (day 6)** - `sql/functions/ref_manage.sql` - `bridge/lib/gitgres/pkt_line.rb` - `test/ref_test.rb`, `test/pkt_line_test.rb` **Milestone 6: Pack Reader (days 7-8)** - `bridge/lib/gitgres/pack_reader.rb` - `test/pack_parse_test.rb` - Test by generating packfiles with `git pack-objects` and parsing them. Verify each extracted object matches `git cat-file`. **Milestone 7: Receive-Pack (HTTP, git push) (days 9-10)** - `bridge/lib/gitgres/server.rb` (receive-pack endpoint) - `bridge/lib/gitgres/receive_pack.rb` - `bridge/lib/gitgres/ref_advertisement.rb` - Test: start server, `git push` a repo, verify objects landed in database. **Milestone 8: Pack Writer + Upload-Pack (HTTP, git clone) (days 11-12)** - `bridge/lib/gitgres/pack_writer.rb` - `bridge/lib/gitgres/object_walk.rb` - `bridge/lib/gitgres/upload_pack.rb` - Test: start server, `git clone` from it, verify clone matches original. **Milestone 9: Full Integration (day 13)** - `test/integration_test.rb` - Push a repo, clone it, verify. Push more commits, fetch them, verify. - Try with a larger repo (a few hundred commits). **Milestone 10: C Extension (days 14-18)** - `ext/gitgres/git_oid_type.c` - `ext/gitgres/sha1_hash.c` - `ext/gitgres/zlib_inflate.c` - `ext/gitgres/pack_parse.c` - Tests: same test suite, but with C functions replacing PL/pgSQL ones. - Benchmark: compare hash computation, tree parsing, pack parsing times. **Milestone 11: Scale Testing (days 19-20)** - Import homebrew-core (or similar large repo) - Measure import time, database size, query performance - Tune: add `COPY` batching, possibly connection pooling, test with larger packs ## Key Decisions and Trade-offs **bytea vs text for OIDs in pure SQL version.** I chose `bytea(20)` over `char(40)` hex. The 20-byte binary form halves storage and index size, and binary comparison is faster. The downside is that every query needs `encode(oid, 'hex')` for human-readable output, but that is what the materialized views handle. **Storing raw content vs storing header+content.** The `objects` table stores only the content, not the git header (`blob 14\0...`). The type and size are in separate columns. This makes queries easier and avoids redundant data. The OID is recomputed from type+content when needed (rare -- mostly just at write time for verification). **HTTP bridge vs git remote helper.** The HTTP smart protocol is the better first choice. It is stateless, well-documented, and debuggable with standard HTTP tools. The remote helper protocol (stdin/stdout) is an alternative if you want `git push gitgres://...` URLs, but adds complexity for no functional benefit in an experimental project. **No delta compression in the pack writer initially.** The first version of pack_writer emits undeltified objects. Git clients will accept this -- it is valid if inefficient. Delta compression is a significant optimization that can be added later by finding similar objects and computing binary diffs. **Packfile parsing in Ruby vs in Postgres.** The bridge parses packfiles in Ruby and inserts objects one at a time (or in batches). Moving pack parsing into a C stored procedure (`git_unpack_objects`) is the C extension's primary optimization for the write path. For the pure SQL version, Ruby-side parsing is the only option since PL/pgSQL cannot do zlib decompression. **pgcrypto dependency.** The pure SQL version depends on `CREATE EXTENSION pgcrypto` for `digest()`. This is available on all managed Postgres services (RDS, Cloud SQL, Supabase, Neon) so it meets the "works on managed Postgres" requirement. ### Critical Files for Implementation - `/Users/andrew/code/gitgres/sql/schema.sql` - Core tables (repositories, objects, refs, reflog) that everything else builds on - `/Users/andrew/code/gitgres/sql/functions/object_hash.sql` - SHA1 computation matching git's format; correctness here is the foundation for the whole project - `/Users/andrew/code/gitgres/bridge/lib/gitgres/pack_reader.rb` - Packfile parser for the write path; the hardest single component (variable-length encoding, zlib decompression, delta resolution) - `/Users/andrew/code/gitgres/bridge/lib/gitgres/server.rb` - HTTP smart protocol server; the glue between git and the database - `/Users/andrew/code/gitgres/ext/gitgres/git_oid_type.c` - Native 20-byte OID type; the centerpiece of the C extension that changes the schema and all query patterns agentId: ad1f9bd62dcb6948c (for resuming to continue this agent's work if needed) total_tokens: 49407 tool_uses: 30 duration_ms: 448674

Summary

Summary