External Linking: PostgreSQL Table Integration

⚠️ STATUS: 🔮 PLANNED REFACTORING - NOT YET IMPLEMENTED
Current Implementation: See Current Overview
Last Updated: November 10, 2025

This document describes planned OID-based integration with PostgreSQL tables. The current production system has limited external table integration.

Overview

This document describes how the graph integrates with existing PostgreSQL tables using the OID system, enabling seamless linking between graph resources and relational data.

Core Concepts

Tables as Resources

PostgreSQL tables are represented as graph resources:

-- Register table using graph function
SELECT graph.create_resource(
    p_type_namespace := 'pg.table',
    p_external_id := 'auth.secrets',
    p_data := jsonb_build_object(
        'schema_name', 'auth',
        'table_name', 'secrets',
        'table_oid', 'auth.secrets'::regclass::oid,
        'description', 'Encrypted secrets storage'
    )
);

Rows as Resources

Individual table rows can be linked:

-- Reference specific row using graph function
SELECT graph.create_resource(
    p_type_namespace := 'pg.table.row',
    p_data := jsonb_build_object(
        'table_oid', 'auth.secrets'::regclass::oid,
        'row_id', 123,
        'primary_key', jsonb_build_object('id', 123)
    )
);

Foreign Keys as Predicates

Foreign keys become graph predicates automatically:

-- Discover FK relationships
CREATE OR REPLACE FUNCTION graph.discover_foreign_keys(p_schema text)
RETURNS TABLE (
    constraint_name text,
    src_table text,
    src_column text,
    tgt_table text,
    tgt_column text
) AS $$
    SELECT 
        con.conname,
        src_class.relname,
        src_att.attname,
        tgt_class.relname,
        tgt_att.attname
    FROM pg_constraint con
    JOIN pg_class src_class ON src_class.oid = con.conrelid
    JOIN pg_namespace src_ns ON src_ns.oid = src_class.relnamespace
    JOIN pg_attribute src_att ON src_att.attrelid = con.conrelid 
        AND src_att.attnum = con.conkey[1]
    JOIN pg_class tgt_class ON tgt_class.oid = con.confrelid
    JOIN pg_attribute tgt_att ON tgt_att.attrelid = con.confrelid 
        AND tgt_att.attnum = con.confkey[1]
    WHERE con.contype = 'f'
      AND src_ns.nspname = p_schema;
$$ LANGUAGE sql STABLE;

-- Create predicates from FKs using graph functions
SELECT graph.create_resource(
    p_type_namespace := ('pg.fk.' || fk.constraint_name)::ltree,
    p_external_id := 'pg.fk.' || fk.constraint_name,
    p_data := jsonb_build_object(
        'label', fk.src_table || ' -> ' || fk.tgt_table,
        'from_table', fk.src_table,
        'from_column', fk.src_column,
        'to_table', fk.tgt_table,
        'to_column', fk.tgt_column,
        'constraint_name', fk.constraint_name
    )
)
FROM graph.discover_foreign_keys('auth') AS fk;

Use Cases

Use Case 1: Link User to Secret

-- User resource (already in graph)
SELECT id FROM graph.resource 
WHERE type_namespace = 'auth.user' AND external_id = 'alice';  -- Returns 100001

-- Secret row (in auth.secrets table)
SELECT id FROM auth.secrets WHERE id = 123;

-- Create resource reference to secret row using graph function
WITH secret_ref AS (
    SELECT graph.create_resource(
        p_type_namespace := 'pg.table.row',
        p_data := jsonb_build_object(
            'table_oid', 'auth.secrets'::regclass::oid,
            'row_id', 123
        )
    ) AS id
)
-- Link user to secret using graph function
SELECT graph.create_statement(
    p_subject_id := 100001,  -- alice
    p_predicate_id := (SELECT id FROM graph.resource WHERE external_id = 'pg.fk.hasSecret'),
    p_object_id := secret_ref.id
)
FROM secret_ref;

Use Case 2: Query User's Secrets

-- Get all secrets accessible to user via graph
SELECT 
    s.*,
    stmt.data as relationship_data
FROM graph.statement stmt
JOIN graph.resource row_ref ON row_ref.id = stmt.object_id
JOIN auth.secrets s ON s.id = (row_ref.data->>'row_id')::bigint
WHERE stmt.subject_id = 100001  -- alice
  AND stmt.predicate = 'pg.fk.hasSecret'
  AND row_ref.type_namespace = 'pg.table.row'
  AND (row_ref.data->>'table_oid')::oid = 'auth.secrets'::regclass::oid;

Use Case 3: Auto-Sync Foreign Keys

Keep graph in sync with table relationships:

Note: This trigger uses raw INSERT statements instead of graph functions for performance. Triggers fire on every row operation and need to be as fast as possible. This is an acceptable use of raw SQL per our database patterns.

-- Trigger on auth.secrets (uses raw INSERT for performance)
CREATE OR REPLACE FUNCTION graph.sync_secret_owner()
RETURNS TRIGGER AS $$
DECLARE
    v_secret_ref_id BIGINT;
    v_owner_id BIGINT;
BEGIN
    -- Get or create resource reference for secret
    SELECT id INTO v_secret_ref_id
    FROM graph.resource
    WHERE type_namespace = 'pg.table.row'
      AND data->>'table_oid' = 'auth.secrets'::regclass::oid::text
      AND data->>'row_id' = NEW.id::text;
    
    IF v_secret_ref_id IS NULL THEN
        INSERT INTO graph.resource (type_namespace, data)
        VALUES (
            'pg.table.row',
            jsonb_build_object(
                'table_oid', 'auth.secrets'::regclass::oid,
                'row_id', NEW.id
            )
        )
        RETURNING id INTO v_secret_ref_id;
    END IF;
    
    -- If secret has node_id, link them
    IF NEW.node_id IS NOT NULL THEN
        INSERT INTO graph.statement (subject_id, predicate_id, object_id)
        VALUES (
            NEW.node_id,
            (SELECT id FROM graph.resource WHERE external_id = 'pg.fk.hasSecret'),
            v_secret_ref_id
        )
        ON CONFLICT DO NOTHING;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_secret_owner_trigger
AFTER INSERT OR UPDATE ON auth.secrets
FOR EACH ROW
EXECUTE FUNCTION graph.sync_secret_owner();

Bidirectional Navigation

From Graph to Table

-- Start with graph resource, get table data
WITH user_secrets AS (
    SELECT 
        stmt.object_id,
        row_ref.data
    FROM graph.statement stmt
    JOIN graph.resource row_ref ON row_ref.id = stmt.object_id
    WHERE stmt.subject_id = 100001
      AND row_ref.type_namespace = 'pg.table.row'
)
SELECT s.*
FROM user_secrets us
JOIN auth.secrets s ON s.id = (us.data->>'row_id')::bigint;

From Table to Graph

-- Start with table row, traverse graph
WITH secret_ref AS (
    SELECT id
    FROM graph.resource
    WHERE type_namespace = 'pg.table.row'
      AND data->>'table_oid' = 'auth.secrets'::regclass::oid::text
      AND data->>'row_id' = '123'
)
SELECT 
    r.*,
    stmt.predicate
FROM secret_ref sr
JOIN graph.statement stmt ON stmt.object_id = sr.id
JOIN graph.resource r ON r.id = stmt.subject_id;

Materialized Views

For performance, materialize common joins:

-- View of all graph-linked secrets
CREATE MATERIALIZED VIEW graph.v_user_secrets AS
SELECT 
    u.id as user_id,
    u.external_id as user_external_id,
    s.id as secret_id,
    s.name as secret_name,
    s.encrypted_value,
    stmt.data as link_metadata
FROM graph.resource u
JOIN graph.statement stmt ON stmt.subject_id = u.id
JOIN graph.resource row_ref ON row_ref.id = stmt.object_id
JOIN auth.secrets s ON s.id = (row_ref.data->>'row_id')::bigint
WHERE u.type_namespace = 'auth.user'
  AND row_ref.type_namespace = 'pg.table.row'
  AND (row_ref.data->>'table_oid')::oid = 'auth.secrets'::regclass::oid;

CREATE INDEX ON graph.v_user_secrets (user_id);
CREATE INDEX ON graph.v_user_secrets (secret_id);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY graph.v_user_secrets;

Schema Evolution

Adding New Tables

-- Function to onboard table
CREATE OR REPLACE FUNCTION graph.onboard_table(
    p_schema text,
    p_table text,
    p_description text DEFAULT NULL
)
RETURNS BIGINT AS $$
DECLARE
    v_table_resource_id BIGINT;
BEGIN
    -- 1. Register table
    v_table_resource_id := graph.external().registerTable(p_schema, p_table);
    
    -- 2. Create predicates from foreign keys
    PERFORM graph.external().createPredicatesFromForeignKeys(p_schema);
    
    -- 3. Create sync triggers
    -- (implementation depends on requirements)
    
    RETURN v_table_resource_id;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT graph.onboard_table('auth', 'secrets', 'Encrypted secrets storage');

Performance Considerations

1. Index OID references: Create indexes on (data->>'table_oid') for fast lookups
2. Materialize hot paths: Use views for frequent table-graph joins
3. Batch sync operations: Don't trigger on every row change
4. Cache table metadata: Store column info to avoid catalog queries

Limitations

1. No automatic cascade: Deleting table row doesn't auto-delete graph resource
2. OID instability: OIDs can change on dump/restore (use table name as fallback)
3. Multi-column FKs: Implementation assumes single-column keys (can be extended)
4. Cross-database: Only works within same PostgreSQL instance

Next Steps

See the following for implementation details:

• API Overview - General API architecture
• External Tables API - PostgreSQL integration API
• Statement Model - Using statements for external links