Relations & Schema Management

Overview

Relations define how collections are linked to each other. ODP supports:

• Many-to-One (M2O) — A field on collection A references a single item in collection B.
• One-to-Many (O2M) — Collection B has many items pointing back to collection A (virtual, uses M2O field on B).
• Many-to-Many (M2M) — Uses a junction table with two M2O fields.

Relations are managed by RelationsService (src/services/relations.ts).

Data Model

Table: odp_relations

Column	Type	Description
id	integer	Primary key
many_collection	varchar	Collection with the FK column
many_field	varchar	FK column name
one_collection	varchar	Collection being referenced
one_field	varchar	Field on one_collection that exposes the O2M (nullable for pure M2O)
one_collection_field	varchar	Field used as the PK on one_collection (usually id)
one_allowed_collections	json	For polymorphic relations
junction_field	varchar	Second FK in M2M junction table
sort_field	varchar	Sort field on the junction table
one_deselect_action	varchar	What happens to related items on deselect: nullify or delete

Relation Types

Many-to-One (M2O)

The most common relation. A field in collection A stores the UUID of an item from collection B.

articles.author_id → odp_users.id

Row in odp_relations:

{
  "many_collection": "articles",
  "many_field": "author_id",
  "one_collection": "odp_users",
  "one_field": null
}

One-to-Many (O2M)

Virtual relation — no extra DB row needed beyond the M2O. The one_field on the referenced collection exposes the reverse list.

odp_users.articles → articles.author_id

Row in odp_relations:

{
  "many_collection": "articles",
  "many_field": "author_id",
  "one_collection": "odp_users",
  "one_field": "articles"
}

Many-to-Many (M2M)

Uses a junction table. Both ends of the M2M are M2O relations going through the junction.

articles ←→ articles_tags ←→ tags

Row in odp_relations:

{
  "many_collection": "articles_tags",
  "many_field": "article_id",
  "one_collection": "articles",
  "one_field": "tags",
  "junction_field": "tag_id"
}

Relation Endpoints

POST /relations

Create a relation.

Auth required: Yes (admin for schema changes)

Request Body (M2O)

{
  "many_collection": "comments",
  "many_field": "article_id",
  "one_collection": "articles",
  "one_field": "comments"
}

Request Body (M2M)

{
  "many_collection": "articles_categories",
  "many_field": "article_id",
  "one_collection": "articles",
  "one_field": "categories",
  "junction_field": "category_id"
}

Response 200 — Returns the created relation object.

---

GET /relations

Read all relations across all collections.

Auth required: Yes

Response 200

{
  "data": [
    {
      "id": 1,
      "many_collection": "articles",
      "many_field": "author_id",
      "one_collection": "odp_users",
      "one_field": null,
      "one_collection_field": "id",
      "junction_field": null,
      "sort_field": null,
      "one_deselect_action": "nullify"
    }
  ]
}

---

GET /relations/:collection

Read all relations for a specific collection.

Auth required: Yes

Response 200 — Filtered subset of relations where many_collection or one_collection matches.

---

PATCH /relations/:collection/:field

Update a relation.

Auth required: Yes

Request Body — Partial update:

{
  "one_deselect_action": "delete"
}

---

DELETE /relations/:collection/:field

Delete a relation (removes the metadata row, not the DB column).

Auth required: Yes

Response 204

---

Schema Management

The schema endpoint provides a complete overview of the current database schema.

GET /schema/snapshot

Get a full snapshot of the current schema (all collections, fields, and relations).

Auth required: Admin

Response 200

{
  "data": {
    "collections": [...],
    "fields": [...],
    "relations": [...]
  }
}

POST /schema/diff

Compare the current schema against a provided snapshot and return the differences.

Auth required: Admin

Request Body — Full or partial schema snapshot object.

Response 200

{
  "data": {
    "collections": { "added": [...], "removed": [...], "changed": [...] },
    "fields": { "added": [...], "removed": [...], "changed": [...] },
    "relations": { "added": [...], "removed": [...], "changed": [...] }
  }
}

---

POST /schema/apply

Apply a schema snapshot (migrate the database to match the snapshot).

Auth required: Admin

Request Body — Full schema snapshot object (from GET /schema/snapshot or diff output).

Response 204 (no body)

---

Using Relations in Queries

Relations enable deep queries with the fields, filter, and deep parameters.

Nested Field Selection

GET /items/articles/uuid?fields=*,author_id.first_name,author_id.email,tags.tag_id.*

Deep Query Parameters

Apply query params (_filter, _sort, _limit, _offset, _fields, _search) to relational fields using the _ prefix convention:

# Limit and filter nested tags
GET /items/articles?deep[tags][_limit]=5&deep[tags][_filter][tag_id][name][_eq]=typescript

{
  "deep": {
    "tags": {
      "_filter": { "tag_id": { "name": { "_eq": "typescript" } } },
      "_limit": 5
    }
  }
}

TIP

The _ prefix distinguishes query params from nested relation names. _limit is a query param; tag_id (no prefix) is a nested relation for deeper traversal.

Relational Filtering

Filters can traverse M2O, O2M, and A2O relations using dot notation:

M2O — filter articles by author name:

GET /items/articles?filter[author_id][first_name][_eq]=John

O2M — filter articles that have approved comments:

GET /items/articles?filter[comments][status][_eq]=approved

A2O (Polymorphic) — filter activities by related item title:

GET /items/activities?filter[item][title][_contains]=draft

A2O filters generate per-collection subqueries combined with OR logic, each gated by the collection field check at the outer level.

Aggregate Relation Counts

GET /items/articles?fields=id,title&aggregate[count]=tags.*

Known Limitation — M2M Filtering

M2M relational filtering from the parent side (e.g. ?filter[tags][name][_eq]=typescript on the articles collection) is not yet supported. ODP stores one relation record per M2M (junction → related). Parent-side detection requires a second record (junction → parent), which is planned for a future relation model update. Filtering from the junction side works correctly.

---

Business Logic Notes

• one_deselect_action: When an M2O/M2M relation is cleared:
  • nullify — Sets the FK to NULL (default)
  • delete — Deletes the related item (cascade delete)
• Schema cache invalidation: Creating/updating/deleting relations invalidates the schema cache. Subsequent requests will reload the schema from the database.
• Polymorphic relations: Use one_allowed_collections to define which collections can be referenced (for dynamic relations like any-type fields).