Metadata-Version: 2.5
Name: post-graph
Version: 0.6.2
Summary: High-performance PostgreSQL-backed graph database library supporting multi-tenant realms, space sub-grouping, schema-per-realm, shadow auditing, and append-only data history.
Project-URL: Homepage, https://github.com/crajah/post-graph
Project-URL: Repository, https://github.com/crajah/post-graph
Author-email: Chandan Rajah <chandan.rajah@gmail.com>
License: Apache-2.0
License-File: LICENSE
Keywords: asyncpg,database,graph,postgres,postgresql,sqlalchemy
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Requires-Python: >=3.9
Requires-Dist: asyncpg>=0.29.0
Provides-Extra: all
Requires-Dist: sqlalchemy>=2.0.0; extra == 'all'
Provides-Extra: sqlalchemy
Requires-Dist: sqlalchemy>=2.0.0; extra == 'sqlalchemy'
Provides-Extra: test
Requires-Dist: pytest-asyncio>=0.24; extra == 'test'
Requires-Dist: pytest>=8.0; extra == 'test'
Description-Content-Type: text/markdown

# post-graph: PostgreSQL-Backed Graph Database Library

[![PyPI version](https://img.shields.io/pypi/v/post-graph.svg)](https://pypi.org/project/post-graph/)
[![Python Versions](https://img.shields.io/pypi/pyversions/post-graph.svg)](https://pypi.org/project/post-graph/)
[![License: Apache 2.0](https://img.shields.io/badge/License-Apache_2.0-blue.svg)](https://www.apache.org/licenses/LICENSE-2.0)

A high-performance Python library for using PostgreSQL as a native graph database. It supports **multi-tenant realms**, **application-level space sub-grouping (`space`)**, **pgvector similarity search across main & history tables**, automatic **shadow audit logging**, **append-only history tables**, and high-speed **recursive graph traversals (CTEs)**.

---

## 🌟 Key Features

- **Table-Per-Vertex & Table-Per-Edge Architecture**: Maps graph elements directly to relational tables, taking advantage of PostgreSQL's foreign keys, indexes, and constraints.
- **Hierarchical Multi-Tenancy & Space Sub-grouping**:
  - **Macro-Isolation (`realm`)**: Primary tenant partitioning at database or schema level.
  - **Micro-Isolation (`space`)**: Optional application-level sub-grouping (`space VARCHAR(255) DEFAULT 'default'`) within tables to segregate environments (e.g. `production`, `staging`, `sandbox`, or workspace spaces).
  - **Dual Tenant Topologies**:
    - **Single-Schema Multi-Tenancy**: Logical isolation using `realm` & `space` column partitions.
    - **Schema-Per-Realm**: Physical isolation creating dedicated PostgreSQL schema namespaces per tenant (`CREATE SCHEMA IF NOT EXISTS "realm_name"`).
- **pgvector Similarity Search (`vector_search`)**:
  - Native HNSW vector indexing (`vector(dim)`) for vertex embeddings.
  - Cosine distance (`<=>`), Euclidean L2 (`<->`), and Inner Product (`<#>`).
  - Multi-scope searching across **main tables**, **associated data history tables**, or **both combined** (`search_scope="both"`).
- **Autogenerated `BIGSERIAL` Primary Keys & Computed `fqid`**:
  - Vertex FQID: `{realm}/{table_name}/{id}`
  - Edge FQID: `{realm}/{from_table}-{to_table}/{id}` (using hyphen separator)
  - Automatically populated at PostgreSQL level via `GENERATED ALWAYS AS ... STORED NOT NULL`.
- **Append-Only History Tables (`{table_name}_data`)**:
  - Automatically created alongside vertex and edge tables.
  - Stores timestamped JSONB payload updates (`data_id`, `realm`, `space`, `id`, `payload`, `timestamp`, `embedding`).
  - Supports vector embeddings on data records for historical semantic matching.
  - Cascades deletion when main vertex/edge is deleted (`ON DELETE CASCADE`).
- **Shadow Audit Logging (`{table_name}_audit`)**:
  - Operates via PostgreSQL triggers to capture all `INSERT`, `UPDATE`, and `DELETE` events.
  - Logs old/new row state and the initiating `user_id` passed via session parameters (`app.current_user_id`).
- **Advanced Graph Traversals & Cycle Detection**:
  - Recursive CTE queries for neighbor exploration, path discovery, and cycle-free shortest path calculation.
  - Optional `check_cycle=True` raising `CyclicReferenceError` during edge creation.
  - Direct object-oriented traversal APIs (`vertex.to()`, `vertex.from_()`, `step.vertex()`, `step.add_edge_to()`).
- **Multiple Async Client Drivers**:
  - High-speed `asyncpg` client (`AsyncPostGraph`).
  - `SQLAlchemy` v2.0 async client (`SQLAlchemyPostGraph`).

---

## 📦 Installation

Install `post-graph` from PyPI:

```bash
# Basic installation (includes asyncpg)
pip install post-graph

# Installation with SQLAlchemy support
pip install "post-graph[sqlalchemy]"

# Installation with all optional dependencies
pip install "post-graph[all]"
```

Using `uv`:
```bash
uv add post-graph
# or with SQLAlchemy support:
uv add "post-graph[sqlalchemy]"
```

### PostgreSQL Setup
To enable `pgvector` similarity search, ensure the `pgvector` extension is enabled in PostgreSQL:

```sql
CREATE EXTENSION IF NOT EXISTS vector;
```

---

## 🏗️ Database Architecture & Multi-Tenancy Hierarchy

```mermaid
erDiagram
    REALM ||--o{ SPACE : contains
    SPACE ||--o{ VERTICES : contains
    SPACE ||--o{ EDGES : contains

    agent_registry {
        text realm PK
        bigserial id PK
        varchar_255 space "Indexed optional sub-grouping (e.g. production, sandbox)"
        text fqid "Generated: realm/table/id"
        uuid uuid "Unique UUID identifier"
        jsonb payload "JSON metadata"
        vector embedding "HNSW Cosine Vector"
        timestamptz created_at
        timestamptz updated_at
    }

    agent_registry_data {
        bigserial data_id PK
        text realm FK
        bigint id FK
        varchar_255 space
        jsonb payload
        vector embedding
        timestamptz timestamp
    }

    spawns {
        text realm PK, FK
        bigserial id PK
        varchar_255 space
        bigint from_id FK
        bigint to_id FK
        text relation_type
        jsonb payload
        timestamptz created_at
    }

    agent_registry ||--o{ agent_registry_data : "appends history"
    agent_registry ||--o{ spawns : "from_id / to_id"
```

---

## 🚀 Quick Start Guide

```python
import asyncio
from post_graph import AsyncPostGraph

async def main():
    # 1. Initialize client
    client = AsyncPostGraph(dsn="postgresql://user:password@localhost:5432/mydb")
    await client.connect()

    realm = "proj_alpha"

    # 2. Declare Vertex & Edge tables
    await client.create_vertex_table("agents", realm=realm, vector_dim=1536)
    await client.create_edge_table("spawns", from_vertex_table="agents", to_vertex_table="agents", realm=realm)

    # 3. Upsert Vertices with Space Sub-grouping
    v_parent = await client.upsert_vertex(
        table_name="agents",
        realm=realm,
        vertex_id=1,
        space="production",
        payload={"name": "The Prime Orchestrator", "caste": "genesis"},
        embedding=[0.01] * 1536
    )

    v_progeny = await client.upsert_vertex(
        table_name="agents",
        realm=realm,
        vertex_id=2,
        space="production",
        payload={"name": "Specialized Worker", "caste": "progeny"},
        embedding=[0.02] * 1536
    )

    # 4. Create Directed Edge
    edge = await client.add_edge(
        table_name="spawns",
        realm=realm,
        from_id=v_parent.id,
        to_id=v_progeny.id,
        space="production",
        relation_type="spawned_progeny",
        payload={"reason": "Task Delegation"}
    )

    # 5. Query Vertices by Realm & Space
    prod_agents = await client.get_vertices("agents", realm=realm, space="production")
    print(f"Production agents: {[a.payload['name'] for a in prod_agents]}")

    # 6. Object-Oriented Neighbor Traversal
    neighbors = await v_parent.outgoing("spawns")
    for step in neighbors:
        print(f"Parent -> {step.vertex().payload['name']} (Edge: {step.edge.relation_type})")

    await client.close()

if __name__ == "__main__":
    asyncio.run(main())
```

---

## 📚 Comprehensive API Reference

### Client Initialization & Management

#### `AsyncPostGraph(dsn, schema_per_realm=False)`
Initializes the high-performance `asyncpg` graph client.

```python
client = AsyncPostGraph(
    dsn="postgresql://postgres:postgres@localhost:5432/postgres",
    schema_per_realm=False  # Set True for physical PostgreSQL schema isolation
)
await client.connect()
```

#### `SQLAlchemyPostGraph(dsn_or_engine, schema_per_realm=False)`
Initializes the `SQLAlchemy` v2.0 async graph client.

---

### Schema Definition APIs

#### `create_vertex_table(table_name, realm=None, vector_dim=None)`
Creates a vertex table, associated audit table (`{table_name}_audit`), and append-only data history table (`{table_name}_data`).

```python
await client.create_vertex_table(
    table_name="agents",
    realm="proj_alpha",
    vector_dim=1536  # Enables pgvector HNSW index
)
```

#### `create_edge_table(table_name=None, from_vertex_table=..., to_vertex_table=..., cascade_delete_from=False, cascade_delete_to=False, realm=None, vector_dim=None)`
Creates a directed edge table linking two vertex tables.

Pass `vector_dim` to give edges their own pgvector `embedding` column and HNSW
index, enabling `vector_search_edges`. Optional — most workloads reach edges by
traversing from a vertex rather than by similarity.

```python
await client.create_edge_table(
    "relations",
    from_vertex_table="entities",
    to_vertex_table="entities",
    realm=realm,
    vector_dim=1536  # Optional: enables semantic search over relationships
)
```

---

### Vertex Operations

#### `add_vertex` / `upsert_vertex`
Upserts a vertex object into a specific `{realm}` and optional `{space}`.

```python
vertex = await client.upsert_vertex(
    table_name="agents",
    realm="proj_alpha",
    vertex_id=101,                  # Optional numeric ID or FQID
    space="production",             # Optional space sub-grouping (default: 'default')
    payload={"name": "Polymath Node"},
    embedding=[0.05] * 1536,         # Optional vector embedding
    user_id="user_admin"            # Optional for shadow audit attribution
)
```

#### `get_vertex(table_name, realm, vertex_id)`
Fetches a single vertex by its numeric ID, UUID, or FQID.

#### `get_vertices(table_name, realm, space=None, limit=None)`
Fetches all vertices belonging to a `{realm}`, with optional filtering by `{space}`.

```python
# Fetch only production space vertices
prod_vertices = await client.get_vertices("agents", realm="proj_alpha", space="production")

# Fetch all vertices in realm regardless of space
all_vertices = await client.get_vertices("agents", realm="proj_alpha")
```

#### `delete_vertex(table_name, realm, vertex_id, user_id=None)`
Deletes a vertex and automatically cascades deletion to referencing edges and history records.

---

### Edge Operations

#### `add_edge`
Creates a directed edge between `from_id` and `to_id`.

```python
edge = await client.add_edge(
    table_name="spawns",
    realm="proj_alpha",
    from_id=101,
    to_id=102,
    space="production",
    relation_type="spawned_progeny",
    payload={"timestamp": "2026-07-27"},
    check_cycle=True                # Raises CyclicReferenceError if edge creates a cycle
)
```

#### `get_edges(table_name, realm, space=None, limit=None)`
Fetches edges in a realm, optionally filtered by `{space}`.

---

### pgvector Semantic Search (`vector_search`)

Performs high-speed cosine, L2, or inner product vector search using HNSW indexes.

```python
results = await client.vector_search(
    table_name="agents",
    realm="proj_alpha",
    query_vector=[0.05] * 1536,
    top_k=5,
    distance_metric="cosine",      # 'cosine', 'l2', or 'inner_product'
    search_scope="both"             # 'main', 'data', or 'both'
)

for vertex, distance in results:
    print(f"Agent: {vertex.payload['name']} | Distance: {distance:.4f}")
```

---

### Edge Semantic Search (`vector_search_edges`)

Edges can carry embeddings too, when the edge table was created with a
`vector_dim`. Supply the vector on `add_edge` / `upsert_edge`, then search:

```python
await client.add_edge(
    "relations", realm=realm,
    from_id=zeus.id, to_id=hera.id,
    relation_type="married_to",
    payload={"description": "spouse"},
    embedding=[0.05] * 1536          # Optional: stored when the table has a vector column
)

results = await client.vector_search_edges(
    table_name="relations",
    realm=realm,
    query_vector=[0.05] * 1536,
    top_k=5,
    distance_metric="cosine",
    space="production",              # Optional space filter
    relation_type="married_to"       # Optional relation type filter
)

for edge, distance in results:
    print(f"{edge.relation_type} | Distance: {distance:.4f}")
```

If the edge table has no vector column, a supplied `embedding` is ignored with a
warning and `vector_search_edges` returns `[]`.

---

### Append-Only Data History (`{table_name}_data`)

Appends timestamped data records to vertices or edges for full auditability and semantic versioning.

```python
# Append historical data record
record = await client.add_vertex_data(
    table_name="agents",
    realm="proj_alpha",
    vertex_id=101,
    space="production",
    payload={"checkpoint": 18, "status": "active"},
    embedding=[0.02] * 1536
)

# Retrieve history records
history = await client.get_vertex_data("agents", realm="proj_alpha", vertex_id=101, limit=10)
```

---

### Advanced Graph Traversals & Shortest Path (CTEs)

#### `traverse`
Executes recursive CTE traversals across edge tables.

```python
paths = await client.traverse(
    realm="proj_alpha",
    start_table="agents",
    start_id=101,
    edge_tables=["spawns", "collaborates"],
    max_depth=4
)
for p in paths:
    print(f"Depth: {p['depth']} | Path: {' -> '.join(p['path'])}")
```

#### `shortest_path`
Finds the shortest cycle-free path between two vertices.

```python
sp = await client.shortest_path(
    realm="proj_alpha",
    start_table="agents",
    start_id=101,
    target_table="agents",
    target_id=105,
    edge_tables=["spawns", "collaborates"],
    max_depth=5
)
if sp:
    print(f"Shortest path found at depth {sp['depth']}: {sp['path']}")
```

---

### Multi-Tenant Realm & Space Deletion

```python
# Delete an entire realm across all tables
deleted_count = await client.delete_realm(realm="tenant_to_remove")
```

---

## 📄 License

This project is licensed under the [Apache License 2.0](LICENSE).

Developed by **Chandan Rajah** (<chandan.rajah@gmail.com>).
