CAP Node.js: Add authorization filter without breaking OData options

[PCASTANS1]

Hi all,

I’m working with SAP CAP (Node.js) exposing OData services consumed by a SAPUI5 SmartTable.

I have a data model with a many-to-many relationship:

• A MainEntity can have N SubEntities
• A SubEntity can belong to M MainEntities

Example (simplified):

entity MainEntity {
  key ID : UUID;
  name   : String;
  subs   : Composition of many MainSub on subs.main = $self;
}

entity MainSub {
  key ID   : UUID;
  main     : Association to MainEntity;
  subCode  : String;
}

 Requirement

I need to implement row-level authorization in the backend such that:

A user can only see MainEntities that are linked to SubEntities assigned to that user.

Conceptually:

WHERE EXISTS (
  SELECT 1
  FROM MainSub s
  WHERE s.main_ID = MainEntity.ID
    AND s.subCode IN (userAllowedSubs)
)

Problem:

The frontend (SmartTable) sends dynamic OData queries like:

$filter=contains(name,'ABC')
&$orderby=name asc
&$top=100
&$skip=0

If I override the query in a before READ handler like:

req.query.SELECT.where = ...

 I break the original OData query, losing filtering, sorting, paging, etc.

I’m looking for the correct CAP pattern to:

• Inject an authorization restriction (AND condition)
• Preserve all incoming OData query options:
• $filter
• $orderby
• $top / $skip
• $select
• Work correctly with SAPUI5 SmartTable

Expected behavior

Final query should behave like:

WHERE
  <original OData filter>
  AND EXISTS (authorization condition)

Additional context

This is conceptually similar to row-level authorization in SAP ERP (e.g. restricting data visibility based on organizational assignments), but implemented in CAP.

Questions

1. What is the recommended way in CAP to append conditions to the existing CQN instead of replacing it?
2. Is using req.query.where(...) with EXISTS the correct approach?
3. Are there better patterns (e.g. CDS views, aspects, annotations) for this type of authorization?

Any guidance or best practices would be greatly appreciated.

Thanks in advance!!