Metadata-Version: 2.4
Name: post-graph
Version: 0.1.5
Summary: High-performance PostgreSQL-backed graph database library supporting multi-tenant realms, 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: MIT
License-File: LICENSE
Keywords: asyncpg,database,graph,postgres,postgresql,sqlalchemy
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT 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'
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: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

A high-performance Python library for using PostgreSQL as a native graph database. It supports **multi-tenant realms**, **pgvector similarity search across main & data 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.
- **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"`).
- **Dual Isolation Multi-Tenancy**:
  - **Single-Schema Multi-Tenancy**: Logical isolation using a `realm` column partition.
  - **Schema-Per-Realm**: Physical isolation creating dedicated PostgreSQL schema namespaces per tenant (`CREATE SCHEMA IF NOT EXISTS "realm_name"`).
- **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`, `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

```mermaid
erDiagram
    users {
        text realm PK
        bigserial id PK
        text fqid
        jsonb payload
        vector embedding
        timestamptz created_at
        timestamptz updated_at
    }
    posts {
        text realm PK
        bigserial id PK
        text fqid
        jsonb payload
        vector embedding
        timestamptz created_at
        timestamptz updated_at
    }
    likes {
        text realm PK, FK
        bigserial id PK
        text fqid
        bigint from_id FK
        bigint to_id FK
        text relation_type
        jsonb payload
        timestamptz created_at
        timestamptz updated_at
    }
    users_audit {
        bigint audit_id PK
        text realm
        text action
        text changed_by
        timestamptz changed_at
        jsonb old_row
        jsonb new_row
    }
    users_data {
        bigserial data_id PK
        text realm FK
        bigint id FK
        jsonb payload
        vector embedding
        timestamptz timestamp
    }

    users ||--o{ likes : "from_id"
    posts ||--o{ likes : "to_id"
    users ||--o{ users_audit : "logs changes"
    users ||--o{ users_data : "appends records & embeddings"
```

---

## 🚀 Quick Start Examples

### 1. Basic Graph Storage & Traversals (`asyncpg`)

```python
import asyncio
from post_graph import AsyncPostGraph

async def main():
    client = AsyncPostGraph(dsn="postgresql://postgres:postgres@localhost:5432/postgres")
    await client.connect()

    # 1. Create schema tables
    await client.create_vertex_table("users")
    await client.create_vertex_table("posts")
    await client.create_edge_table("likes", from_vertex_table="users", to_vertex_table="posts")

    # 2. Add vertices
    alice = await client.add_vertex("users", realm="realm_a", payload={"name": "Alice"})
    post = await client.add_vertex("posts", realm="realm_a", payload={"title": "Hello PostGraph!"})

    # 3. Add edge connecting Alice -> Post
    edge = await client.add_edge(
        table_name="likes",
        realm="realm_a",
        from_id=alice.id,
        to_id=post.id,
        relation_type="like",
        payload={"reaction": "heart"}
    )

    # 4. Append data history record
    await alice.add_data({"status": "active", "login_count": 1})
    history = await alice.get_data()
    print(f"Alice history count: {len(history)}, last: {history[0].payload}")

    # 5. Object-oriented traversal
    steps = await alice.to("likes")
    for step in steps:
        target_post = step.vertex()
        print(f"Alice liked post: {target_post.payload['title']}")

    await client.close()

asyncio.run(main())
```

---

### 2. `pgvector` Similarity Search (`main`, `data`, or `both` scopes)

```python
import asyncio
from post_graph import AsyncPostGraph

async def main():
    client = AsyncPostGraph(dsn="postgresql://postgres:postgres@localhost:5432/postgres")
    await client.connect()

    # 1. Create vertex table with 4D embeddings
    await client.create_vertex_table("documents", realm="tech_realm", vector_dim=4)

    # 2. Add vertex with embedding to MAIN table
    doc1 = await client.add_vertex(
        "documents",
        realm="tech_realm",
        payload={"title": "PostgreSQL Overview"},
        embedding=[1.0, 0.0, 0.0, 0.0]
    )

    # 3. Add historical entry with embedding to DATA table
    await doc1.add_data(
        payload={"version": "v2.0", "notes": "Added pgvector support"},
        embedding=[0.0, 1.0, 0.0, 0.0]
    )

    # 4. Vector search on MAIN table
    results_main = await client.vector_search(
        "documents",
        realm="tech_realm",
        query_vector=[0.9, 0.1, 0.0, 0.0],
        top_k=3,
        search_scope="main"
    )
    print("Main Search Hit:", results_main[0][0].payload['title'], "Dist:", results_main[0][1])

    # 5. Vector search on BOTH main & data history tables combined
    results_both = await client.vector_search(
        "documents",
        realm="tech_realm",
        query_vector=[0.05, 0.95, 0.0, 0.0],
        top_k=3,
        search_scope="both"
    )
    print("Combined Scope Best Hit:", results_both[0][0].payload['title'], "Dist:", results_both[0][1])

    await client.close()

asyncio.run(main())
```

---

### 3. Using `SQLAlchemyPostGraph`

```python
import asyncio
from sqlalchemy.ext.asyncio import create_async_engine
from post_graph import SQLAlchemyPostGraph

async def main():
    engine = create_async_engine("postgresql+asyncpg://postgres:postgres@localhost:5432/postgres")
    client = SQLAlchemyPostGraph(engine)

    # DDL & CRUD methods share identical signatures
    await client.create_vertex_table("users")
    await client.create_edge_table("follows", from_vertex_table="users", to_vertex_table="users")

    v1 = await client.add_vertex("users", realm="default", payload={"name": "Bob"})
    v2 = await client.add_vertex("users", realm="default", payload={"name": "Charlie"})
    e = await client.add_edge("follows", realm="default", from_id=v1.id, to_id=v2.id, relation_type="follow")

    await engine.dispose()

asyncio.run(main())
```

---

## 📖 Detailed API Reference

### Client Initialization

```python
AsyncPostGraph(
    dsn: Optional[str] = None,
    connection_or_pool: Optional[Union[asyncpg.Pool, asyncpg.Connection]] = None,
    schema_per_realm: bool = False
)

SQLAlchemyPostGraph(
    engine_or_connection: Union[AsyncEngine, AsyncConnection],
    schema_per_realm: bool = False
)
```

- **`schema_per_realm`**: If `True`, creates physically isolated schema namespaces per `realm` (e.g. `"realm_a"."users"`). If `False` (default), uses single-schema logical multi-tenancy with a `realm` column partition.

---

### Schema DDL Operations

#### `create_vertex_table`
```python
await client.create_vertex_table(
    table_name="users",
    realm=None,
    vector_dim=None  # Optional integer (e.g. 1536, 4096) for pgvector HNSW index
)
```
Creates:
1. `{table_name}` main vertex table with `(realm, id)` primary key.
2. `{table_name}_audit` shadow audit log table.
3. `{table_name}_data` append-only history table with foreign key `(realm, id) ON DELETE CASCADE`.
4. If `vector_dim` is provided, creates `embedding vector(dim)` column and HNSW index on both `{table_name}` and `{table_name}_data`.

#### `create_edge_table`
```python
await client.create_edge_table(
    table_name="likes",               # Optional; defaults to "{from_table}TO{to_table}" if empty
    from_vertex_table="users",
    to_vertex_table="posts",
    realm=None,
    cascade_delete_from=False,       # If True, deleting edge deletes source vertex
    cascade_delete_to=False          # If True, deleting edge deletes target vertex
)
```

---

### Vector Similarity Search (`vector_search`)

```python
results = await client.vector_search(
    table_name="documents",
    realm="tech_realm",
    query_vector=[0.1, 0.2, 0.3, 0.4],
    top_k=5,
    distance_metric="cosine",        # "cosine" (<=>), "l2" (<->), or "inner_product" (<#>)
    search_data_table=False,         # Deprecated alias; set search_scope="data" instead
    search_scope="main"              # "main", "data", or "both"
)
```

- **`search_scope="main"`**: Searches vector column on main vertex table `{table_name}`.
- **`search_scope="data"`**: Searches vector column on associated data table `{table_name}_data` joined with main table `{table_name}`.
- **`search_scope="both"`**: Performs a unified CTE vector distance query across main and associated data tables, returning distinct matching `Vertex` objects sorted by minimum distance.

---

### Vertex Operations

#### `add_vertex`
```python
vertex = await client.add_vertex(
    table_name="users",
    realm="tenant_1",
    vertex_id=None,                 # Optional autogenerated BIGSERIAL ID
    payload={"name": "Alice"},       # Dict payload stored as JSONB
    embedding=[0.1, 0.2, 0.3, 0.4],  # Optional vector embedding
    user_id="admin_1"               # Optional user attribution for audit log
)
```

#### `get_vertex`
```python
vertex = await client.get_vertex(table_name="users", realm="tenant_1", vertex_id=1)
# Seamlessly accepts integer ID, FQID ("tenant_1/users/1"), or UUID string ("550e8400-e29b-41d4-a716-446655440000")
```

#### `get_vertex_by_uuid`
```python
vertex = await client.get_vertex_by_uuid(
    table_name="users",
    realm="tenant_1",
    uuid="550e8400-e29b-41d4-a716-446655440000"
)
```
Performs fast $O(1)$ indexed lookup on the automatically assigned `uuid` column.

#### `get_edge_by_uuid`
```python
edge = await client.get_edge_by_uuid(
    table_name="likes",
    realm="tenant_1",
    uuid="463bf3f0-33f7-47cf-9fa9-46ab83035033"
)
```
Performs fast indexed lookup for edge records by UUID.

#### `update_vertex`
```python
updated = await client.update_vertex(
    table_name="users",
    realm="tenant_1",
    vertex_id=1,
    payload={"name": "Alice Smith"},
    user_id="admin_1"
)
```

#### `delete_vertex`
```python
deleted = await client.delete_vertex(table_name="users", realm="tenant_1", vertex_id=1, user_id="admin_1")
```
Automatically cascade deletes all connected edges in the database.

---

### Edge Operations

#### `add_edge`
```python
edge = await client.add_edge(
    table_name="follows",
    realm="tenant_1",
    edge_id=None,                   # Optional autogenerated BIGSERIAL ID
    from_id=1,
    to_id=2,
    relation_type="friend",
    payload={"since": "2026-01-01"},
    user_id="admin_1",
    check_cycle=True                # Raises CyclicReferenceError if edge creates a cycle
)
```

#### `delete_edge`
```python
deleted = await client.delete_edge(table_name="follows", realm="tenant_1", edge_id=100, user_id="admin_1")
```

---

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

Vertices and edges support appending timestamped historical data snapshots with optional vector embeddings.

#### Client Data APIs
```python
record = await client.add_vertex_data(
    table_name="users",
    realm="realm_a",
    vertex_id=1,
    payload={"status": "active", "login_count": 10},
    timestamp=None,                 # Optional; defaults to CURRENT_TIMESTAMP
    embedding=[0.1, 0.2, 0.3, 0.4]   # Optional vector embedding
)

history = await client.get_vertex_data(table_name="users", realm="realm_a", vertex_id=1, limit=5)
```

#### Model Instance Methods
```python
rec = await vertex.add_data({"status": "active"}, embedding=[0.1, 0.2, 0.3, 0.4])
history = await vertex.get_data(limit=10)
```

---

### Multi-Tenant Realm Deletion

```python
rows_deleted = await client.delete_realm(realm="tenant_to_remove")
```
Completely removes all vertices, edges, audit logs, and data history records belonging to the target realm across all tables.

---

### Graph Traversals & Shortest Path (CTEs)

#### `traverse`
```python
paths = await client.traverse(
    realm="realm_a",
    start_table="users",
    start_id=1,
    edge_tables=["follows", "likes"],
    max_depth=3
)
for p in paths:
    print(f"Depth: {p['depth']} | Path: {' -> '.join(p['path'])}")
```

#### `shortest_path`
```python
sp = await client.shortest_path(
    realm="realm_a",
    start_table="users",
    start_id=1,
    target_table="posts",
    target_id=42,
    edge_tables=["follows", "likes"],
    max_depth=5
)
if sp:
    print(f"Shortest path found at depth {sp['depth']}: {sp['path']}")
```

---

## 📄 License

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

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