Proposal: Evolve PostgreSQL Index (PR #10) into Primary Store + GitHub for Version Control

[damienriehl]

Proposal: PostgreSQL + GitHub Architecture for OntoKit

Date: 2026-04-01
Authors: Damien Riehl, with analysis from Claude
Status: Draft for discussion with Fr. John D'Orazio
Purpose: Basis for a PRD to refactor OntoKit's storage and version control layers

---

1. Executive Summary

We propose evolving OntoKit's architecture into a two-layer system:

• PostgreSQL as the live working layer -- all entity-level CRUD, search, browsing, and analytics happen against granular database rows
• GitHub as the version control layer -- formal commits, branches, pull requests, and review happen through GitHub's API and UI

Crucially, we are not starting from scratch. PR #10 on ontokit-api (feat: PostgreSQL index tables for ontology query optimization) already built the foundation: five PostgreSQL index tables, an OntologyIndexService with SQL-based tree/search/detail queries, an IndexedOntologyService facade that transparently routes reads through the index with RDFLib fallback, and background reindexing via ARQ workers. The proposed architecture evolves PR #10's read-only index into a read-write primary store.

---

2. What PR #10 Already Built

PR #10 (branch feature/postgresql-ontology-index) implemented:

2.1 Five PostgreSQL Tables

Table	Purpose	Key Columns
ontology_index_status	Tracks indexing state per (project, branch)	project_id, branch, commit_hash, status (pending/indexing/ready/failed), entity_count
indexed_entities	One row per OWL entity	project_id, branch, iri, local_name, entity_type, deprecated
indexed_labels	Multilingual labels	entity_id FK, property_iri, value, lang
indexed_hierarchy	Parent-child class edges	project_id, branch, child_iri, parent_iri
indexed_annotations	Non-label annotations	entity_id FK, property_iri, value, lang, is_uri

Indexes include: GIN trigram indexes on local_name, iri, and label value for fast fuzzy/substring search. Composite B-tree indexes on (project_id, branch, entity_type) for type filtering. Hierarchy indexes on both parent_iri and child_iri for bidirectional traversal.

2.2 OntologyIndexService (1,134 lines)

• full_reindex(project_id, branch, graph, commit_hash) -- atomically deletes existing index data and rebuilds from an RDFLib graph, batching inserts (1,000 rows/batch) for performance
• get_root_classes() -- finds classes not appearing as children in hierarchy
• get_class_children() -- JOINs entities with hierarchy table
• get_class_detail() -- fetches entity + labels + comments + parents + annotations
• get_ancestor_path() -- recursive CTE walking up the hierarchy (depth-limited to 100)
• search_entities() -- ILIKE search across local_name, IRI, and labels
• get_class_count() -- simple COUNT query
• Concurrency guard -- PostgreSQL ON CONFLICT DO UPDATE prevents concurrent reindexing, with 10-minute stale lock reclamation

2.3 IndexedOntologyService (Facade)

Transparently routes queries through the SQL index when ready, falling back to RDFLib when the index hasn't been built yet, the index query throws an error, or the migration hasn't been run. Also auto-enqueues reindexing when the stored commit_hash doesn't match git HEAD.

2.4 Reindex Triggers

Four triggers already wired up: after file import, after GitHub clone, after source save (commit), and a manual admin endpoint (POST /projects/{id}/ontology/reindex). Branch deletion cleans up index rows.

2.5 What PR #10 Noted as Limitations

• instance_count always returns 0 (rdf:type relationships not indexed)
• equivalent_iris and disjoint_iris always return empty lists (not indexed)
• Search prefix-match sorting happens client-side after SQL LIMIT

---

3. The Problem: Why Read-Only Index Isn't Enough

PR #10 is a read cache. The actual data still lives in RDF files managed by pygit2 bare repos. Every edit still follows the expensive path:

Load entire RDF file from Git bare repo
  → Parse into in-memory RDFLib graph
    → Modify the graph
      → Serialize entire graph back to Turtle
        → Commit to Git bare repo
          → Trigger background reindex to update PostgreSQL

3.1 What This Means in Practice

Problem	Impact
Writes are still whole-file	Changing one label still requires reading, parsing, and rewriting the entire ontology file
Index lag	After an edit, the index is stale until the background ARQ job completes the full reindex
Dual source of truth	Git is canonical, PostgreSQL is a projection -- any bug in reindexing creates silent data divergence
Full reindex on every edit	full_reindex() deletes all rows and rebuilds from scratch, even if only one entity changed
Memory pressure unchanged	RDFLib still loads the entire graph for every write operation
Merge conflicts unchanged	pygit2 bare repos still manage branching on monolithic Turtle files

3.2 Custom Code Still Maintained

Even with PR #10, the backend still maintains:

• pygit2 bare repository management (git/bare_repository.py)
• Custom PR workflow (create, review, comment, approve, merge)
• Custom branch management
• MinIO object storage
• Two-way GitHub sync bridge
• Full RDFLib graph loading for every write

---

4. Proposed Architecture: Evolve the Index into the Primary Store

4.1 The Two-Layer Model

                    ┌───────────────────────┐
                    │     OntoKit Web UI    │
                    │   (Next.js frontend)  │
                    └───────────┬───────────┘
                                │
                    ┌───────────▼───────────┐
                    │    OntoKit API        │
                    │   (FastAPI backend)   │
                    └───────────┬───────────┘
                         │             │
              Live CRUD  │             │  Version Control
                         ▼             ▼
                  ┌────────────┐  ┌──────────┐
                  │ PostgreSQL │  │  GitHub   │
                  │  (entities,│  │  (commits,│
                  │   triples, │  │  branches,│
                  │   search)  │  │  PRs,     │
                  └────────────┘  │  reviews) │
                                  └──────────┘

4.2 What Changes from PR #10

Aspect	PR #10 (Read-Only Index)	Proposed (Read-Write Primary Store)
Writes	Go to Git, then reindex async	Go directly to PostgreSQL
Reads	PostgreSQL with RDFLib fallback	PostgreSQL only (no fallback needed)
Source of truth	Git bare repo + MinIO	PostgreSQL (working state), GitHub (versioned snapshots)
Reindexing	Full rebuild from RDFLib graph on every edit	Not needed -- DB is always current
Serialization	Not relevant (Git stores files)	On-demand: DB → Turtle when committing to GitHub
Branching	pygit2 bare repos	GitHub (system/admin level)
PRs	Custom internal workflow	GitHub PRs

4.3 The Evolution Path: From PR #10 to Primary Store

PR #10's tables are already close to what we need. Here's what evolves:

indexed_entities → ontology_entities

Add columns for write support:

-- Existing (from PR #10):
id, project_id, branch, iri, local_name, entity_type, deprecated

-- Add:
created_at    TIMESTAMPTZ DEFAULT now()
updated_at    TIMESTAMPTZ DEFAULT now()
created_by    UUID REFERENCES users(id)
updated_by    UUID REFERENCES users(id)

indexed_labels → entity_labels -- add created_at, updated_at

indexed_hierarchy → class_hierarchy -- add created_at, updated_at

indexed_annotations → entity_annotations -- add created_at, updated_at

New tables needed:

-- Safety valve for complex OWL constructs
complex_axioms (
    id              UUID PRIMARY KEY,
    project_id      UUID REFERENCES projects(id) ON DELETE CASCADE,
    branch          VARCHAR(255),
    axiom_type      VARCHAR(100),
    involved_iris   TEXT[],
    turtle_fragment TEXT,
    metadata        JSONB,
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
)

-- Namespace/prefix management
ontology_prefixes (
    id              UUID PRIMARY KEY,
    project_id      UUID REFERENCES projects(id) ON DELETE CASCADE,
    prefix          VARCHAR(50),
    namespace_iri   TEXT,
    UNIQUE(project_id, prefix)
)

-- Ontology-level metadata
ontology_metadata (
    id              UUID PRIMARY KEY,
    project_id      UUID REFERENCES projects(id) ON DELETE CASCADE,
    ontology_iri    TEXT,
    version_iri     TEXT,
    imports         TEXT[],
    annotations     JSONB,
    created_at      TIMESTAMPTZ DEFAULT now(),
    updated_at      TIMESTAMPTZ DEFAULT now()
)

-- Property domain/range declarations
property_domains (
    id              UUID PRIMARY KEY,
    property_id     UUID REFERENCES ontology_entities(id) ON DELETE CASCADE,
    class_iri       TEXT
)

property_ranges (
    id              UUID PRIMARY KEY,
    property_id     UUID REFERENCES ontology_entities(id) ON DELETE CASCADE,
    target_iri      TEXT
)

-- Individual type assertions and property values
individual_types (
    id              UUID PRIMARY KEY,
    individual_id   UUID REFERENCES ontology_entities(id) ON DELETE CASCADE,
    class_iri       TEXT
)

individual_property_values (
    id              UUID PRIMARY KEY,
    individual_id   UUID REFERENCES ontology_entities(id) ON DELETE CASCADE,
    property_iri    TEXT,
    value           TEXT,
    datatype        TEXT,
    language        VARCHAR(20),
    target_iri      TEXT
)

ontology_index_status evolves to project_sync_status:

project_sync_status (
    id              UUID PRIMARY KEY,
    project_id      UUID REFERENCES projects(id) ON DELETE CASCADE,
    branch          VARCHAR(255),
    github_repo     TEXT,
    last_commit_sha VARCHAR(40),
    last_synced_at  TIMESTAMPTZ,
    sync_status     VARCHAR(20),
    entity_count    INTEGER DEFAULT 0,
    error_message   TEXT,
    UNIQUE(project_id, branch)
)

4.4 OntologyIndexService Evolves to OntologyEntityService

PR #10's OntologyIndexService (1,134 lines) already has all the read methods. We add write methods:

class OntologyEntityService:
    """Evolved from OntologyIndexService -- now handles reads AND writes."""

    # === READS (already built in PR #10) ===
    async def get_root_classes(...)
    async def get_class_children(...)
    async def get_class_detail(...)
    async def get_ancestor_path(...)
    async def search_entities(...)
    async def get_class_count(...)

    # === WRITES (new) ===
    async def create_entity(self, project_id, branch, iri, entity_type, labels, ...)
    async def update_entity(self, project_id, branch, iri, changes: dict)
    async def delete_entity(self, project_id, branch, iri)
    async def add_label(self, entity_id, property_iri, value, lang)
    async def update_label(self, label_id, value, lang)
    async def remove_label(self, label_id)
    async def set_parent(self, project_id, branch, child_iri, parent_iri)
    async def remove_parent(self, project_id, branch, child_iri, parent_iri)
    async def reparent_class(self, project_id, branch, class_iri, old_parent, new_parent)
    async def add_annotation(self, entity_id, property_iri, value, lang)
    async def update_annotation(self, annotation_id, value, lang)
    async def remove_annotation(self, annotation_id)

    # === SERIALIZATION (new) ===
    async def export_to_turtle(self, project_id, branch) -> str
    async def import_from_turtle(self, project_id, branch, turtle_content: str, user_id)

4.5 GitHub Sync Workflow

User edits in OntoKit UI
  → DB updates immediately (fast, granular, per-entity SQL)
  → No reindexing needed -- DB is always current

User clicks "Commit" (or auto-commit on save, TBD)
  → export_to_turtle() serializes DB → deterministic Turtle
  → GitHub API: create commit on branch with the Turtle file
  → project_sync_status updated with new commit SHA

Admin creates PR on GitHub (or via OntoKit UI wrapper)
  → Review happens on GitHub
  → Merge happens on GitHub
  → GitHub webhook → OntoKit
    → import_from_turtle() updates DB from merged Turtle
    → project_sync_status updated

---

5. Handling Hard Problems

5.1 RDF Round-Tripping

Challenge: Import RDF → DB rows → Export RDF must be lossless.

Solution -- Hybrid decomposition:

• ~90% of constructs (named classes, properties, hierarchy, labels, comments, annotations, individuals, simple restrictions) decompose cleanly into relational tables. Fast CRUD path.
• ~10% of complex constructs (class expressions with nested boolean operators, property chains, GCI axioms, SWRL rules) stored in complex_axioms as serialized Turtle fragments. Lossless round-trip by construction.
• Same approach used by TopBraid and PoolParty.

5.2 Deterministic Serialization

OntoKit already has serialize_deterministic() using RDFLib's to_isomorphic(). The export service builds an RDFLib graph from DB rows, then serializes deterministically.

5.3 Branching Model

PR #10's tables already have a branch column on every table. Multiple branch states can coexist in the DB. Proposed workflow:

• main branch is the primary working state, always in the DB
• Feature branches can also live in the DB or only on GitHub
• System/admin users create GitHub branches and PRs for formal review
• Regular users edit the main branch working state via the OntoKit UI

5.4 Linting

PostgreSQL makes most lint rules simpler, not harder:

Lint Rule	PostgreSQL Query
undefined-parent	LEFT JOIN class_hierarchy h ON h.parent_iri = e.iri WHERE e.id IS NULL
circular-hierarchy	Recursive CTE with cycle detection
duplicate-label	GROUP BY value HAVING COUNT(*) > 1
missing-label	LEFT JOIN entity_labels WHERE labels.id IS NULL
orphan-class	Classes with no parent and no children

---

6. What Changes in Each Codebase

6.1 ontokit-api (Backend)

Evolve from PR #10:

• Rename indexed_* tables to ontology_* / entity_* / class_*
• Add write methods to the service layer
• Add new tables (complex_axioms, ontology_prefixes, ontology_metadata, property/individual tables)
• Add export_to_turtle() and import_from_turtle()
• Add GitHub API service and webhook handler
• Add timestamp + user tracking columns

Remove / Simplify:

• git/bare_repository.py -- no more internal Git repos
• git/repository.py -- already deprecated
• MinIO storage integration
• services/ontology.py -- RDFLib-based entity operations replaced by SQL
• services/indexed_ontology.py -- facade no longer needed
• api/routes/pull_requests.py -- simplify to thin wrapper around GitHub API
• services/pull_request_service.py, services/github_sync.py

Keep (mostly unchanged):

• Auth (Zitadel OIDC), Project/member management
• Embedding/semantic search (already PostgreSQL-based via pgvector)
• Linting (reimplemented as SQL queries), WebSocket collaboration
• Notifications, analytics, change events

6.2 ontokit-web (Frontend)

Add: "Commit to GitHub" action, GitHub PR integration views, improved import flow

Remove: Internal branch management, PR workflow, and diff viewer UIs

Keep: Ontology editor (both modes), entity tree, Monaco editor, search, analytics, graph visualization

---

7. Migration Path

Phase 0: Merge & Rename PR #10 (Foundation)

• Merge PR feat: PostgreSQL index tables for ontology query optimization #10 to get the index tables and query infrastructure
• Rename tables, add write-support columns, add new tables
• Alembic migrations for all schema changes

Phase 1: Write Path (Make PostgreSQL Primary for Writes)

• Implement write methods (create, update, delete)
• Implement import_from_turtle() and export_to_turtle()
• Validate round-trip fidelity: import → export → diff
• Keep Git-based storage running in parallel (dual-write)

Phase 2: GitHub Integration

• GitHub API service (commits, branches, PRs)
• "Commit to GitHub" UI workflow
• GitHub webhook handler

Phase 3: Cut Over

• Switch all reads/writes to PostgreSQL, version control to GitHub
• Migrate existing project data
• Remove pygit2, MinIO

Phase 4: Cleanup & Polish

• Remove dead code
• Optimize queries, enhance GitHub integration
• Fill PR feat: PostgreSQL index tables for ontology query optimization #10 gaps (instance counts, equivalent/disjoint tracking)

---

8. Comparison with Prior Proposals

This supersedes the "Ontology Atomization" plan and addresses every concern from its critical analysis:

Concern	How This Addresses It
Abandoning Turtle as source of truth	Turtle remains the versioned format on GitHub
Git performance with 50K+ files	GitHub stores one Turtle file, not 50K entity files
Lossy JSON schema for OWL	No proprietary schema; complex_axioms stores Turtle fragments
Turtle regeneration bottleneck	Serialization only on explicit commit, not every edit
Linter requires full rewrite	SQL-based linting is simpler for most rules
Big-bang migration risk	Phased migration with dual-write period
Tool ecosystem compatibility	Full -- Turtle on GitHub works with Protege, ROBOT, OWL API

---

9. Why Build on PR #10

What PR #10 Provides	Lines of Code	Effort Saved
Five PostgreSQL tables with indexes	~340	Schema design, index tuning, trigram setup
Full reindex + graph extraction	~400	Entity extraction, label resolution, hierarchy walking
Query methods (tree, detail, ancestors, search)	~500	Recursive CTEs, label preference, bulk loading
Transparent fallback facade	~300	Strategy pattern, schema conversion
Background worker + API integration	~240	ARQ job, Redis pubsub, dependency injection
Total	~1,780	Weeks of development

---

10. Risks and Mitigations

Risk	Likelihood	Impact	Mitigation
RDF round-trip data loss	Medium	High	Test suite + complex_axioms Turtle fragments
GitHub API rate limits	Low	Medium	5,000 req/hour; commits are infrequent
GitHub dependency	Low	Medium	Serialization is Git-host-agnostic
Complex OWL not form-editable	High	Low	Same as today; Turtle editor handles these
Branch state confusion	Medium	Medium	Clear UX messaging
Migration breaks existing projects	Low	Medium	Dual-write period for validation

---

11. Open Questions for Discussion

1. GitHub org/repo structure -- one GitHub repo per OntoKit project, or monorepo?
2. Modular Turtle -- split large ontologies into multiple files (using owl:imports), or single file?
3. Branch UX -- branch switcher in OntoKit that loads from GitHub, or GitHub-only branching?
4. Commit granularity -- auto-commit on every save, or user-initiated only?
5. GitHub App vs. PAT -- GitHub App (fine-grained) or user PATs (simpler)?
6. Self-hosted option -- GitHub-only for v1, or support Gitea/GitLab from day one?
7. Suggestion workflow -- keep custom suggestion flow or replace with GitHub fork-and-PR?
8. PR feat: PostgreSQL index tables for ontology query optimization #10 merge timing -- merge now and evolve, or develop full write path first?

---

cc @JohnRDOrazio @damienriehl

[JohnRDOrazio]

Thanks for the detailed write-up, @damienriehl. There's good thinking here, especially around evolving PR #10's read-only PostgreSQL index into a read-write primary store. I want to address some foundational assumptions before we go further.

Git is not GitHub

The proposal treats GitHub as the version control layer, but OntoKit already has a fully functional git layer via pygit2 bare repositories — no GitHub account or dependency required. Every commit is attributed to the authenticated user via their name and email, extracted from the Zitadel-issued JWT. Users authenticate through whatever OIDC/OAuth provider Zitadel is configured with — GitHub, Google, Microsoft, SAML, passkeys, etc. None of these require a GitHub account just to make a commit to an ontology project.

Making GitHub the mandatory version control backend would:

• Make self-hosted and air-gapped deployments impossible
• Subject collaborative editing to GitHub API rate limits (5,000 req/hr)
• Create a hard dependency on a third-party commercial service for core functionality
• Replace an already-working internal PR workflow with a less integrated external one

GitHub integration is valuable as an optional sync/mirror (which github_service.py already provides), not as the primary version control layer.

How the sync model works

When GitHub (or GitLab, Gitea, or any other provider) sync is enabled, the relationship is not a fork — there is no "upstream." The git repository on the OntoKit server is simply a local working copy, and the sync provider becomes the origin. If a user were to clone the GitHub repository to their own machine, their local checkout and the OntoKit server's repository would both be equally "local working copies" of the same origin. There's no forking mechanism involved — just standard git push/pull against a shared remote.

This means the sync provider is interchangeable. GitHub, GitLab, Gitea, or any git-compatible hosting service can serve as the origin. OntoKit's internal git layer (branching, commits, PRs) works identically regardless of whether a sync provider is configured at all.

Section 5.3: The branching model is inverted

The proposal suggests that "regular users edit the main branch working state via the OntoKit UI" and that only system/admin users create branches and PRs. This inverts the design that's already implemented and defeats the purpose of bare repositories.

The current architecture:

• Editors work in their own branches and create PRs — they never touch main directly
• Admins/owners review and merge PRs into main
• Suggesters (the default role for new members) can view and comment

The key UX insight: the user making a "suggestion" doesn't need to understand git branching, PRs, or merging. From their perspective, they make an edit and it becomes a suggestion that an admin can accept or reject. The branching and PR mechanics are invisible to them — they're implementation details handled by the backend. Opening a PR is making a suggestion; merging a PR is accepting it.

This is exactly why bare repos exist: every user can work on their own branch concurrently without a working directory, and the PR workflow provides the review gate. Pushing regular users onto main would remove that gate entirely.

The five-role model already covers these workflows

OntoKit defines five project-scoped roles: owner, admin, editor, suggester, viewer. The permission matrix:

Action	Owner	Admin	Editor	Suggester	Viewer
Delete project	✓
Manage members & settings	✓	✓
Merge PRs / approve reviews	✓	✓
Create branches & PRs	✓	✓	✓
Edit ontology / commit	✓	✓	✓
Comment on PRs	✓	✓	✓	✓
Browse & view	✓	✓	✓	✓	✓

The suggester role (default for new members) is purpose-built for contributors whose work goes through review. The editor role handles users who can create content but can't merge it themselves.

What I do agree with

The idea of evolving PR #10's PostgreSQL index into a read-write primary store has real merit:

• Entity-level CRUD avoids the expensive load → parse → modify → serialize → commit cycle for the entire Turtle file
• No reindex lag after edits — the DB is always current
• SQL-based linting is simpler and faster for most rules
• Search and traversal queries are already implemented

But this evolution can happen without replacing the internal git layer with GitHub. The architecture would be:

PostgreSQL  →  live working layer (entity CRUD, search, linting)
pygit2 bare repos  →  version control (commits serialized from DB state)
GitHub/GitLab/Gitea  →  optional sync origin (unchanged from current)

On commit, the system serializes the DB state to Turtle and commits to the local bare repo. If a sync provider is configured, changes are pushed to origin like any local working copy would. This gives us all the performance benefits of the PostgreSQL primary store while preserving self-hosted deployability and the existing auth/branching/PR infrastructure.

Happy to discuss further — especially the round-trip fidelity question and the complex_axioms safety valve, which are the hardest technical problems regardless of which git backend we use.

[JohnRDOrazio]

On Open Question #1: Repo structure

GitHub org/repo structure — one GitHub repo per OntoKit project, or monorepo?

This question again conflates GitHub with git. Each OntoKit project already has its own git repository (a pygit2 bare repo on the server). If a sync provider is configured, that project's repo syncs to its own remote repository — one project, one repo.

A monorepo for all OntoKit projects would be architecturally unsound:

• Privacy: Projects can be private. A monorepo would expose all projects to anyone with access to the repo.
• Governance: Each project has its own members, roles, and permissions. A monorepo would collapse all of those boundaries.
• Branching: Branch names would collide across projects, and merges in one project would create noise for every other project.
• Scale: Clone size grows with every project added, penalizing users who only work on one.

One project = one git repository. This is already how it works, and there's no reason to change it.

[JohnRDOrazio]

On Open Question #3: Branch UX

Branch UX — branch switcher in OntoKit that loads from GitHub, or GitHub-only branching?

Same conflation of git with GitHub. Branches are created locally on the OntoKit server's bare git repository. PRs are opened, reviewed, and merged locally. The branch switcher in the OntoKit UI operates against the local git repo — GitHub is not involved unless sync is explicitly configured.

PR sync between OntoKit and a remote

To answer the natural follow-up question — when GitHub (or another provider) sync is enabled, can PRs be synced bidirectionally? Yes, this is already implemented.

OntoKit → GitHub

When a PR is created, merged, updated, closed, or reopened in OntoKit, the system calls the GitHub API to mirror that action:

• PR created → github_service.create_pull_request() creates a corresponding PR on GitHub; the GitHub PR number and URL are stored locally (PullRequest.github_pr_number, PullRequest.github_pr_url)
• PR merged → github_service.merge_pull_request() merges the GitHub PR
• PR updated (title/description) → github_service.update_pull_request() syncs the change
• Review created → github_service.create_review() posts the review to GitHub
• Comment created → github_service.create_comment() posts the comment to GitHub

If the GitHub sync fails for any reason, the local operation still succeeds — GitHub sync is fire-and-forget with logging. The local bare repo is always authoritative for in-progress work.

GitHub → OntoKit

When events occur on GitHub, webhooks notify OntoKit at POST /api/v1/projects/webhooks/github/{project_id} (verified via HMAC-SHA256 signature):

• PR merged on GitHub → local PR status set to MERGED
• PR closed on GitHub → local PR status set to CLOSED
• PR reopened on GitHub → local PR status set to OPEN
• PR edited on GitHub → title and description updated locally
• Review submitted on GitHub → local PullRequestReview record created
• Push to default branch → triggers git pull to sync the branch

Additionally, github_sync.py provides periodic branch-level sync (fetch/push/fast-forward) as a fallback for missed webhooks.

Established pattern: how Weblate does it

This architecture follows the same model used by Weblate for translation management:

1. Local repo is the working copy — Weblate clones the remote and treats its server-side repo as the primary working state. All translator edits are committed locally first.
2. Remote is the origin — Weblate pushes changes (to a branch or fork) and creates PRs via the GitHub/GitLab API.
3. Merge detection via webhooks — when a PR is merged on GitHub, the webhook triggers a pull. Weblate detects that its commits are now in the upstream branch and resets its working state accordingly.
4. Conflict handling — Weblate fetches and rebases before pushing. If conflicts arise, the component is flagged for manual resolution.
5. Graceful degradation — if the remote is unavailable, local work continues uninterrupted.

The same push-then-PR-via-API pattern is used by Renovate, Dependabot, and other tools that maintain server-side state and sync with GitHub through PRs.

The key principle

The OntoKit server's bare git repo and a developer's local clone are both equally "local working copies" of the same origin. There is no forking involved — just standard git push/git pull against a shared remote. The sync provider (GitHub, GitLab, Gitea) is interchangeable and optional. OntoKit's branching, PRs, and review workflow function identically with or without a remote configured.

[JohnRDOrazio]

On Open Question #4: Commit granularity

Commit granularity — auto-commit on every save, or user-initiated only?

Auto-commit on every save, whether user-initiated or automatically triggered.

Since every editor works on their own branch, frequent commits don't pollute anyone else's history — they're isolated until a PR is merged. This also removes a git concept from the UX: the user just "saves," they don't need to understand committing. This aligns with the design principle that editors and suggesters shouldn't see git mechanics. And every save point becomes a recoverable undo point.

To keep main clean, commits are squashed on merge — when an admin merges a PR, all the granular auto-commits collapse into a single meaningful commit. This is the same model Weblate uses: every translator save is an auto-commit on the working branch, and commits are squashed into a clean commit before pushing to the remote.

The granular edit history remains visible during PR review, which is useful for understanding the progression of changes. The squash happens at merge time, not PR creation time, so reviewers get full context.

[JohnRDOrazio]

On Open Question #6: Self-hosted option

GitHub-only for v1, or support Gitea/GitLab from day one?

Support any git-compatible remote from day one. GitHub, GitLab, Gitea, Bitbucket, or any self-hosted git server should all work as the sync origin. The sync layer should be provider-agnostic.

Currently the codebase only implements GitHub-specific API calls (github_service.py) for PR sync, webhook handling, and repository operations. This needs to be abstracted into a provider-agnostic interface so that the same operations (create PR, merge PR, handle webhooks, push/pull branches) work against any supported provider. The underlying git operations via pygit2 are already provider-agnostic — it's only the API layer (PR creation, webhook setup, etc.) that is GitHub-specific.

Opening a separate issue to track this work.

[JohnRDOrazio]

On Open Question #7: Suggestion workflow

Suggestion workflow — keep custom suggestion flow or replace with GitHub fork-and-PR?

There is no need for forks. The suggestion workflow is already handled by the branching + PR mechanism built into OntoKit's local git layer — this is git, not GitHub.

When an editor makes a change, the system commits it to their branch and opens a PR against main. That is the suggestion. The admin reviews and merges or rejects. The user never sees branches, PRs, or git — they just see "my edit is pending review" or "my edit was accepted/rejected."

Forks are a GitHub-specific collaboration model for open-source projects where contributors don't have write access to the repository. In OntoKit, every project member with editor or suggester rights is already a known participant with a defined role — there's no need for the indirection of forking. They have their own branch in the same repository, which is simpler, faster, and doesn't depend on any external service.

[JohnRDOrazio]

On Open Question #8: PR #10 merge timing

PR #10 merge timing — merge now and evolve, or develop full write path first?

Merge PR #10 now and evolve incrementally. The read-only PostgreSQL index is already useful on its own as a performance optimization for tree traversal, search, and entity detail queries. There's no reason to block that value while the read-write primary store is still being evaluated.

Once merged, the read-write evolution can be explored in subsequent PRs with the foundation already in place.

[JohnRDOrazio]

Corrected architecture diagram

The two-layer model in section 4.1 is misleading: it places commits, branches, PRs, and reviews in the GitHub realm, when they actually live in the git layer that is an integral part of the OntoKit API itself. GitHub (or any other provider) is simply an optional shared remote.

graph TB
    subgraph frontend["OntoKit Web UI (Next.js)"]
        UI["Browser Client"]
    end

    subgraph backend["OntoKit Backend"]
        subgraph api["OntoKit API (FastAPI)"]
            GIT["Git Layer (pygit2 bare repos)<br/>commits · branches · PRs · reviews"]
            RDFLIB["RDFLib (in-memory)<br/>parse ↔ serialize · query · diff · lint"]
        end

        PG["PostgreSQL<br/>core: projects · members · PRs · reviews<br/>index: entities · labels · hierarchy"]
        REDIS["Redis<br/>ARQ job queue · pub/sub events"]
        MINIO["MinIO (S3-compat)<br/>file import staging · fallback"]

        GIT -->|"read / write"| RDFLIB
        RDFLIB --> PG
        RDFLIB --> REDIS
        RDFLIB --> MINIO
    end

    subgraph remote["Shared Remote (optional)"]
        PROVIDER["GitHub / GitLab /<br/>Gitea / Bitbucket"]
    end

    UI -->|REST| api
    UI <-->|WebSocket| REDIS
    GIT -->|"push / pull"| PROVIDER

Loading

Key differences from the proposal's diagram:

• Git layer is inside the OntoKit API — commits, branches, PRs, and reviews are managed by pygit2 bare repos + PostgreSQL, not delegated to GitHub
• PostgreSQL, Redis, and MinIO are within the backend boundary — they are integral infrastructure, not external services
• Shared remote is optional — a direct sync target of the git layer, interchangeable between GitHub/GitLab/Gitea/Bitbucket
• RDFLib sits between git and the data stores — it parses files from git into in-memory graphs, and serializes graphs back to Turtle for git commits
• PostgreSQL has two roles — core metadata (projects, members, PRs) and the optional performance index (entities, labels, hierarchy)
• Redis feeds back to the frontend — pub/sub channels drive WebSocket updates for real-time progress (linting, indexing, sync status)
• MinIO is an import staging area and fallback — not a primary data store

[JohnRDOrazio]

Authentication Architecture — Where Zitadel fits

A common misconception is that Zitadel is "just another OAuth provider" like GitHub or Microsoft. It's not — Zitadel is the identity broker that sits between external OAuth/OIDC providers and our application. It's an integral part of our backend infrastructure, similar to how PostgreSQL handles data or Redis handles pub/sub.

Crucially, Zitadel is not an external hosted service — it's a self-hosted component that runs on the same server as OntoKit, just like PostgreSQL or Redis. It ships as part of the OntoKit deployment stack (via Docker Compose). Anyone who self-hosts OntoKit runs their own Zitadel instance — there is no dependency on a third-party auth service. Zitadel's frontend (login UI) and backend (OIDC/token services) are fully integrated into OntoKit's own frontend and backend — they share the same PostgreSQL instance and the same infrastructure.

What Zitadel does

• Identity broker: Users can sign in via GitHub, Microsoft, Google, etc. — Zitadel federates these external identity providers and normalizes them into a single OIDC interface for our app.
• Token issuer: Regardless of which external provider the user authenticated with, Zitadel issues the JWT access tokens that OntoKit validates.
• User management: Zitadel maintains the canonical user directory — roles, sessions, MFA policies, account linking (e.g., same user signs in via GitHub today, Microsoft tomorrow).
• OIDC server: Both the frontend and backend speak OIDC exclusively to Zitadel — they never talk directly to GitHub/Microsoft/Google for auth.

graph TB
    subgraph providers["External Identity Providers"]
        GH["GitHub"]
        MS["Microsoft / Entra ID"]
        GOOGLE["Google"]
        OTHER["SAML / LDAP / etc."]
    end

    subgraph ontokit["OntoKit (self-hosted)"]
        ZITADEL["Zitadel (Identity Broker)<br/>user directory · sessions · MFA<br/>token issuance · IdP federation"]
        WEB["Next.js Frontend<br/>(NextAuth.js v5)"]
        API["FastAPI Backend<br/>(JWT validation)"]
        PG["PostgreSQL<br/>(shared: OntoKit data + Zitadel auth)"]
    end

    GH -->|"OAuth2 / OIDC"| ZITADEL
    MS -->|"OAuth2 / OIDC"| ZITADEL
    GOOGLE -->|"OAuth2 / OIDC"| ZITADEL
    OTHER -->|"SAML / LDAP"| ZITADEL

    ZITADEL --> PG
    API --> PG

    WEB -->|"1. OIDC login redirect"| ZITADEL
    ZITADEL -->|"2. JWT access token"| WEB
    WEB -->|"3. API call + Bearer token"| API
    API -->|"4. Validate JWT (JWKS)"| ZITADEL

Loading

The flow in practice

1. User clicks "Sign in" → frontend redirects to Zitadel (not to GitHub directly)
2. Zitadel presents its login page, which may offer GitHub/Microsoft/Google as sign-in options
3. If user picks GitHub → Zitadel handles the OAuth2 exchange with GitHub behind the scenes
4. Zitadel issues its own JWT to the frontend, with a standardized set of claims
5. Frontend passes that JWT to the API on every request
6. API validates the JWT against Zitadel's JWKS endpoint — it never needs to know which external provider was used

Why this matters

• Self-hosted and self-contained — Zitadel runs alongside OntoKit on the same server, sharing the same PostgreSQL instance. No external auth dependency, full control over user data.
• Adding a new identity provider (e.g., institutional SAML for a university) is a Zitadel config change — zero code changes in OntoKit.
• The API has one auth implementation — validate JWTs from Zitadel. It doesn't need GitHub OAuth logic, Microsoft token exchange, etc.
• User identity is decoupled from provider — a user who signed up via GitHub can later link their Microsoft account, and it's the same OntoKit user.
• Portable deployments — each OntoKit instance has its own Zitadel, so there's no shared auth state between different deployments.