Predicate Catalogue (Supabase)

Predicate Catalogue (Supabase)

This document describes the functional_predicates and spatial_predicates tables that now live in Supabase. They provide a dynamic catalogue of all relationships that can be created inside Neo4j.

Why store the catalogue in Postgres and not in Neo4j?

• The list is tiny and mostly reference/metadata — perfect for a relational table.
• Supabase Row-Level-Security lets us expose read-only access to every authenticated client while locking down writes to super-admins.
• Front-end dropdowns, icons, localisation, and “deprecated” flags can change without redeploying code; the UI just re-fetches the list.
• API resolvers validate requested predicates against the catalogue before writing an edge in Neo4j, guaranteeing data integrity.

---

1. Table Definitions

-- ▼ functional predicates (compatibility, control, power …)
create table functional_predicates (
  key            text primary key,                -- e.g. 'POWERS'
  category       text not null,                   -- Power, Control, Compatibility …
  description    text,
  inverse_key    text references functional_predicates(key),
  is_symmetric   boolean default false,           -- true = store only one direction
  icon_name      text,                            -- optional helper for UI (Tabler icon id)
  deprecated     boolean default false,
  created_at     timestamptz default now()
);

-- ▼ spatial predicates (LEFT_OF, IN, ON …)
create table spatial_predicates (
  key            text primary key,
  axis           text,                            -- horizontal / vertical / depth / containment
  description    text,
  inverse_key    text references spatial_predicates(key),
  is_symmetric   boolean default false,
  icon_name      text,
  deprecated     boolean default false,
  created_at     timestamptz default now()
);

Both tables have RLS enabled with a simple policy:

create policy read_predicates on functional_predicates for select using ( true );
create policy read_spatial_predicates on spatial_predicates  for select using ( true );

Writes are restricted to the service role or future system_owner policy.

---

2. Seed Data

The migration seed_predicate_catalogue.sql inserts the initial canonical list (see Plings-Docs/core-systems/functional-relationships-system.md and database/neo4j-core-schema.md). Highlights:

key	category / axis	symmetric	inverse_key
PART_OF	physical_composition	❌	null
POWERS	Power	❌	POWERED_BY
WORKS_WITH	Compatibility	✅	null
LEFT_OF	horizontal	❌	RIGHT_OF
NEXT_TO	horizontal	✅	null

Note: Symmetric predicates are stored in Neo4j once, in a canonical direction decided by the API.

---

3. Front-End Usage

1. Populate dropdowns – On app start call select key, category, description, icon_name from functional_predicates order by category, key and cache the result.
2. Determine inverse – When the user picks a directional predicate, the client can optionally show the inverse in tooltips by looking up inverse_key.
3. Visual cues – Grey-out or hide rows where deprecated = true.
4. Symmetric badge – Show a 🔄 badge if is_symmetric = true so users understand the edge will appear only once.

---

4. Backend / GraphQL Resolver Flow

Client ➜ mutation addFunctionalRelation(fromId, toId, predicate)
        ➜ Resolver
           1) select 1 from functional_predicates where key=$predicate
              – 404 if not found / deprecated
           2) Write edge in Neo4j
              MATCH (a:ObjectInstance {id:$fromId}), (b:ObjectInstance {id:$toId})
              CREATE (a)-[:POWERS]->(b)

Because the catalogue lives in Postgres, every API instance has the same authoritative list, deploys stay immutable, and you avoid “magic strings” scattered through code.

---

5. Future Extensions

• Localisation: add label_en, label_de, … columns.
• Tenant-scoped custom predicates: add organisation_id nullable fk + RLS checks.
• Ordering / UI groups: add display_order integer.

---

Last updated: 2025-07-05 11:28 CEST