# ADR-042: Scoping Columns on Graph Instances

## Status

Accepted

## Context

The original `graphs` table had no concept of ownership, organization, or project. A `graph` row was identified by `id` and `name` with no way to answer "which org owns this call graph?" or "list all graphs for this project."

In the system/tenant DB model (ADR-040), the tenant DB is inherently org-scoped (the entire `.db` file is one org). But within a tenant DB, graph instances still need to be scoped to:

- **An owner** — which account created/owns this graph
- **A project** — which project this graph belongs to (for project-scoped graphs like call graphs and session trees)

The metagraph pattern stores node/edge attributes as JSON, but scoping columns must be real columns because they appear in WHERE clauses, JOIN conditions, and need indexes.

## Decision

Add `ownerId` and `projectId` columns to the `graphs` table:

```
graphs {
  ...commonCols,
  graphTypeId,
  name,
  description,
  status,
  ownerId,    -- TEXT, nullable — logical reference to accounts.id in system DB
  projectId,  -- TEXT, nullable — logical reference to projects (graph or domain table)
}
```

No `orgId` column — the tenant DB itself IS the org scope. Adding `orgId` would be redundant within a single-tenant DB file.

These are **logical references** consistent with ADR-020 (no nodeTypeId on nodes) and OQ-24 (identityId as logical reference). No FK constraint because the referenced tables live in a different database file (system DB). The hub/consumer enforces referential integrity at the application layer.

**Nullability semantics**:

- `ownerId` NULL — system-owned graph (e.g., the ACL graph type definition seeded at setup). Not associated with any account.
- `projectId` NULL — org-level graph (e.g., the org's ACL instance). Not scoped to a specific project.

**Indexes**: `idx_graphs_owner_id` on `(ownerId)`, `idx_graphs_project_id` on `(projectId)`, `idx_graphs_owner_id_project_id` on `(ownerId, projectId)` for combined lookups.

## Consequences

**Positive:**

- "List all graphs for project X" is a simple indexed query, not a JSON path extraction
- "Who owns this graph?" is a column read, not a traversal
- Consistent with the rule from the hub's architecture: "if a field appears in WHERE clauses, JOIN conditions, or needs a constraint, it should be a proper column — not buried in metadata or JSON"
- No FK constraints means no cross-DB coupling — the tenant DB works without the system DB open

**Negative:**

- Orphaned graphs possible if an account is deleted in the system DB but the tenant DB's `graphs.ownerId` still references it. Application-layer cleanup required.
- Adding columns to the `graphs` table is a schema change that affects all consumers. The columns are nullable to ease the transition.

## References

- ADR-040: System DB + tenant DB (explains why no `orgId`)
- ADR-020: No nodeTypeId on nodes (same logical-reference pattern)
- ADR-008: Common columns pattern