Adding support for LTREE operators

[ibarakaiev]

First, let me say that Electric is an impressive piece of engineering—thanks for building it.

I've been working on setting up hierarchical authorization system to protected resources. Consider a documents table where documents can be shared across multiple departments via a join table:

CREATE TABLE departments (
  id UUID PRIMARY KEY,
  path LTREE UNIQUE
);

CREATE TABLE documents (
  id UUID PRIMARY KEY,
  title TEXT,
  content TEXT,
  created_at TIMESTAMP
);

CREATE TABLE document_department_access (
  document_id UUID REFERENCES documents(id),
  department_id UUID REFERENCES departments(id),
  PRIMARY KEY (document_id, department_id)
);

departments:

id	path
d1	company.sales
d2	company.sales.enterprise

documents:

id	title
doc1	Sales Playbook
doc2	Enterprise Pricing

document_department_access:

document_id	department_id
doc1	d1
doc2	d2

User Authorization:

• Alice is in company.sales → should see doc1, doc2 (documents shared with sales or any sub-department)
• Bob is in company.sales.enterprise → should only see doc2 (documents shared with enterprise, but NOT parent departments)

With native ltree support (and recent allow_subqueries experimental feature flag), setting up a shape to get live docs is straightforward:

{
  table: 'documents',
  where: `id IN (
    SELECT document_id
    FROM document_department_access
    WHERE department_id IN (
      SELECT id FROM departments
      WHERE path <@ 'company.sales'
    )
  )`
}

Without it, I either need to:
a) fetch all departments and documents to the client and do client-side filtering, which becomes a potential security issue, or
b) construct an explicit list of departments first in the backend proxy, e.g. something like:

SELECT DISTINCT d.id
  FROM user_department_memberships udm
  JOIN departments user_dept ON udm.department_id = user_dept.id
  JOIN departments d ON d.path <@ user_dept.path
  WHERE udm.user_id = 'alice';

and then pass into a dynamic list of IN (...), which becomes complex and requires caching, shape invalidation etc.

Perhaps this is beyond Electric's scope to begin with—if so, I'd be curious if anyone has any ideas on what's a better way to make reactive client-side sync for complex hierarchical authorization patterns like these.

[KyleAMathews]

It seems like we could support LTREE though it's not come up before so wouldn't be our first priority.

cc @icehaunter is there alternative approaches here? And could we support LTREE reasonably well? I'm not sure if we've talked about the implications of supporting extensions e.g. different extensions could add conflicting syntax?

[icehaunter]

I'm not familiar with ltree extension, but reading the docs it seems that the operator is just a subarray search with a mask it shouldn't be hard to add. As for alternative approaches, I think this is mappable to a json array with json path operations, but we don't support that either right now. It might take a little more to make an index for ltree after we add the operator, but given ltree is less generic than jsonpath it should be also easier

[ibarakaiev]

Is there anything I can do in the meantime or be helpful with? I'm not at all familiar with Electric's codebase, but looking at defpostgres "anyarray @> anyarray -> bool" I'm wondering if this is something I can do for myself in a fork (in an unoptimized fashion) by just adding a new defpostgres definition. But again, maybe I'm oversimplifying without understanding the full picture.