SQL Edge Operations¶

This document details the SQL repository implementation for edge operations. Edges represent directed relationships between entities in the property graph model. The SQL implementation uses GORM to manage edges stored in relational databases (PostgreSQL and SQLite).

This page covers edge creation, querying, retrieval, and deletion operations. For entity management, see SQL Entity Operations. For edge tag operations, see SQL Tag Management. For the Neo4j graph database implementation of edge operations, see Neo4j Edge Operations.

Sources:

Edge Table Structure¶

The SQL repository stores edges in an edges table with the following structure:

Column	Type	Description
`edge_id`	uint64	Primary key, auto-incremented
`type`	string	Relation type from OAM
`content`	JSON	Serialized relation data
`from_entity_id`	uint64	Foreign key to source entity
`to_entity_id`	uint64	Foreign key to destination entity
`created_at`	timestamp	When the edge was first created
`updated_at`	timestamp	Last seen timestamp

The Edge struct in the SQL repository maps to this table structure:

type Edge struct {
    ID           uint64
    Type         string
    Content      []byte
    FromEntityID uint64
    ToEntityID   uint64
    CreatedAt    time.Time
    UpdatedAt    time.Time
}

Sources:

Edge Creation Flow¶

CreateEdge Method¶

flowchart TD
    Start["CreateEdge(edge)"]
    Validate["Validate input fields<br/>(line 23-26)"]
    TaxCheck["Validate against OAM taxonomy<br/>oam.ValidRelationship()<br/>(line 28-32)"]
    DupCheck["isDuplicateEdge()<br/>(line 41-43)"]
    Found{{"Duplicate<br/>found?"}}
    ParseFrom["Parse FromEntity.ID<br/>(line 45-48)"]
    ParseTo["Parse ToEntity.ID<br/>(line 50-53)"]
    Serialize["Serialize Relation to JSON<br/>(line 55-58)"]
    Create["Create Edge struct<br/>with timestamps<br/>(line 60-71)"]
    Insert["sql.db.Create(&r)<br/>(line 73-76)"]
    Convert["toEdge(r)<br/>(line 77)"]
    Return["Return edge"]

    Start --> Validate
    Validate --> TaxCheck
    TaxCheck --> DupCheck
    DupCheck --> Found
    Found -->|"Yes"| Return
    Found -->|"No"| ParseFrom
    ParseFrom --> ParseTo
    ParseTo --> Serialize
    Serialize --> Create
    Create --> Insert
    Insert --> Convert
    Convert --> Return

    style DupCheck fill:#f9f9f9
    style Found fill:#f9f9f9

Diagram: Edge Creation Process

The CreateEdge method implements comprehensive validation and duplicate detection:

Input Validation : Verifies that the edge, relation, and both entities are non-nil
Taxonomy Validation : Calls oam.ValidRelationship() to ensure the relationship is valid according to the Open Asset Model taxonomy
Duplicate Detection : Checks if an identical edge already exists
Timestamp Management : Uses provided LastSeen timestamp or defaults to current UTC time
Entity ID Parsing : Converts string entity IDs to uint64
Content Serialization : Serializes the relation to JSON format
Database Insert : Uses GORM to insert the edge record

Sources:

Duplicate Edge Detection¶

isDuplicateEdge Logic¶

flowchart TD
    Start["isDuplicateEdge(edge, updated)"]
    Query["OutgoingEdges(FromEntity)<br/>(line 85)"]
    Iterate["Iterate through outgoing edges<br/>(line 86-98)"]
    Match{{"ToEntity.ID matches<br/>AND<br/>Relation content matches?"}}
    Update["edgeSeen(out, updated)<br/>(line 88)"]
    Fetch["FindEdgeById(out.ID)<br/>(line 90)"]
    ReturnDup["Return edge, true"]
    ReturnNew["Return nil, false"]

    Start --> Query
    Query --> Iterate
    Iterate --> Match
    Match -->|"Yes"| Update
    Update --> Fetch
    Fetch --> ReturnDup
    Match -->|"No"| Iterate
    Iterate -->|"No more edges"| ReturnNew

    style Match fill:#f9f9f9

Diagram: Duplicate Edge Detection Mechanism

The duplicate detection mechanism prevents redundant edges in the database:

Query Existing Edges : Retrieves all outgoing edges from the source entity with the same label
Deep Comparison : Compares both the destination entity ID and the relation content using reflect.DeepEqual
Update Timestamp : If a duplicate is found, updates its updated_at timestamp via edgeSeen()
Return Existing : Fetches and returns the existing edge instead of creating a new one

This approach ensures that re-discovering the same relationship updates the temporal information without creating duplicate records.

Sources:

Edge Timestamp Management¶

edgeSeen Method¶

The edgeSeen method updates the updated_at timestamp for an existing edge:

flowchart LR
    Input["Edge + new timestamp"]
    ParseID["Parse edge.ID<br/>(line 105-108)"]
    Serialize["Serialize Relation to JSON<br/>(line 110-113)"]
    ParseFrom["Parse FromEntity.ID<br/>(line 115-118)"]
    ParseTo["Parse ToEntity.ID<br/>(line 120-123)"]
    BuildStruct["Build Edge struct<br/>with new UpdatedAt<br/>(line 125-133)"]
    Save["sql.db.Save(&r)<br/>(line 135-138)"]

    Input --> ParseID
    ParseID --> Serialize
    Serialize --> ParseFrom
    ParseFrom --> ParseTo
    ParseTo --> BuildStruct
    BuildStruct --> Save

Diagram: Edge Timestamp Update Flow

The method preserves the original created_at timestamp while updating updated_at to reflect the most recent observation of the relationship.

Sources:

Querying Edges¶

Incoming and Outgoing Edge Queries¶

Both IncomingEdges and OutgoingEdges methods support: - Temporal filtering via the since parameter - Label filtering to retrieve edges of specific relation types - Optional label filtering (returns all edges if no labels specified)

IncomingEdges Query Structure¶

flowchart TD
    Start["IncomingEdges(entity, since, labels...)"]
    ParseID["Parse entity.ID to int64<br/>(line 157-160)"]
    CheckSince{{"since.IsZero()?"}}
    QueryAll["WHERE to_entity_id = ?<br/>(line 165)"]
    QueryTime["WHERE to_entity_id = ?<br/>AND updated_at >= ?<br/>(line 167)"]
    Filter{{"labels<br/>provided?"}}
    FilterLoop["Filter by relation label<br/>(line 174-186)"]
    NoFilter["Use all edges<br/>(line 188)"]
    Return["Return filtered edges<br/>via toEdges()<br/>(line 194)"]

    Start --> ParseID
    ParseID --> CheckSince
    CheckSince -->|"Yes"| QueryAll
    CheckSince -->|"No"| QueryTime
    QueryAll --> Filter
    QueryTime --> Filter
    Filter -->|"Yes"| FilterLoop
    Filter -->|"No"| NoFilter
    FilterLoop --> Return
    NoFilter --> Return

    style CheckSince fill:#f9f9f9
    style Filter fill:#f9f9f9

Diagram: IncomingEdges Query Flow

OutgoingEdges Query Structure¶

The OutgoingEdges method follows the same pattern but queries on from_entity_id instead:

Query Parameter	SQL WHERE Clause
No time filter	`from_entity_id = ?`
With time filter	`from_entity_id = ? AND updated_at >= ?`

Label Filtering Implementation:

When labels are provided : 1. Parse each edge's JSON content to extract the relation 2. Compare the relation's label against the requested labels 3. Include the edge only if its label matches

Sources:

Edge Retrieval and Deletion¶

FindEdgeById¶

The FindEdgeById method retrieves a single edge by its ID:

func FindEdgeById(id string) (*types.Edge, error)

Implementation details : - Parses the string ID - Queries using WHERE edge_id = ? - Converts the database Edge to types.Edge via toEdge()

DeleteEdge¶

The DeleteEdge method removes an edge from the database:

func DeleteEdge(id string) error

Implementation : - Parses the string ID to uint64 - Delegates to deleteEdges() which executes: DELETE FROM edges WHERE edge_id IN ?

The deleteEdges helper method accepts a slice of IDs, enabling batch deletion operations.

Sources:

Type Conversion Functions¶

Database to Domain Model Conversion¶

flowchart LR
    DB["Edge<br/>(database struct)"]
    Parse["Parse JSON content<br/>to oam.Relation<br/>(line 260)"]
    Build["Build types.Edge<br/>with converted fields<br/>(line 265-278)"]
    Domain["types.Edge<br/>(domain model)"]

    DB --> Parse
    Parse --> Build
    Build --> Domain

    Note1["FromEntity and ToEntity<br/>contain only IDs,<br/>not full asset content<br/>(line 272-276)"]

    Build -.-> Note1

Diagram: Edge Type Conversion

toEdge Function¶

The toEdge function converts a database Edge struct to a types.Edge:

Parses the JSON Content field back to an oam.Relation
Converts numeric timestamps to time.Time in local timezone
Creates entity references with IDs only (does not join to fetch full entity data)
Converts uint64 IDs to string format

toEdges Function¶

The toEdges helper applies toEdge to a slice of edges, filtering out any that fail to parse.

Important Note: The converted FromEntity and ToEntity objects contain only the ID field. Full entity content is not joined during edge queries for performance reasons. To retrieve complete entity data, use FindEntityById() on the entity IDs.

Sources:

Integration Testing Examples¶

The test suite demonstrates typical edge operations:

Basic Edge Creation and Query¶

From :

edge := &types.Edge{
    Relation:   tc.relation,
    FromEntity: sourceEntity,
    ToEntity:   destinationEntity,
}

e, err := store.CreateEdge(edge)
// Query incoming edges
incoming, err := store.IncomingEdges(destinationEntity, start, tc.relation.Label())
// Query outgoing edges  
outgoing, err := store.OutgoingEdges(sourceEntity, start, tc.relation.Label())

Duplicate Handling¶

From :

// Store duplicate relation - last_seen is updated
rr, err := store.CreateEdge(edge2)
// Verify LastSeen timestamp increased
assert(rr.LastSeen > originalEdge.LastSeen)

Unfiltered Edge Queries¶

From :

// Retrieve all outgoing edges (no label filter)
outs, err := store.OutgoingEdges(sourceEntity, time.Time{})
// Returns all edges regardless of relation type

Sources: ,

Error Handling¶

Edge operations return errors for the following conditions:

Operation	Error Condition	Error Type
`CreateEdge`	Nil input fields	"failed input validation checks"
`CreateEdge`	Invalid taxonomy relationship	"%s -%s-> %s is not valid in the taxonomy"
`CreateEdge`	Entity ID parse failure	Parse error
`CreateEdge`	JSON serialization failure	Serialization error
`CreateEdge`	Database insert failure	GORM error
`IncomingEdges`	Entity ID parse failure	Parse error
`IncomingEdges`	Zero edges found	"zero edges found"
`OutgoingEdges`	Entity ID parse failure	Parse error
`OutgoingEdges`	Zero edges found	"zero edges found"
`FindEdgeById`	Edge not found	GORM error (record not found)
`DeleteEdge`	ID parse failure	Parse error
`DeleteEdge`	Database delete failure	GORM error

Sources:

Performance Considerations¶

Query Optimization¶

Indexed Lookups: The from_entity_id and to_entity_id columns should be indexed for efficient edge traversal queries
Time-based Filtering: The updated_at column enables temporal queries for incremental data retrieval
Label Filtering: Performed in-memory after database query, requires parsing JSON content

Duplicate Detection Cost¶

The isDuplicateEdge check queries all outgoing edges with the same label and performs deep equality checks. For entities with many outgoing edges, this can be expensive. The cost is traded off against preventing duplicate records.

Lazy Entity Loading¶

Edge queries return entity references with IDs only, avoiding JOIN operations. This design improves query performance but requires separate queries to fetch full entity data when needed.

Sources: