Task: Implement entity-specific authorization helpers and add to relevant queries

[emmbm]

Task Description

The current database schema provides all that is needed for a granular data access control. Although, its use requires us to first ideate a future-proof system (query filters to use in where clauses?, query header injection with schema-level RLS?, etc.) and then implement reusable query helpers.

List of actions

• Establish an authorization system and its level of implementation
• Implement query helpers
• Adjust current db schema (ONLY IF REQUIRED)
• Apply to authorization system to queries across app

[emmbm]

Chosen approach for now is to provide a module with helper filters (where values) or sub queries (select) that can then be added to existing queries. This enables for a modular building of queries with blocks from different concerns (retrieving translations, authorization, etc.).

See https://github.com/CUPUM/nplex/blob/v2/src/lib/db/authorization.server.ts for WIP.

[emmbm]

Refer to https://github.com/CUPUM/aipithet/blob/main/src/lib/queries/queries.ts for an example of how to solve this.

[emmbm]

RLS is coming soon to drizzle, but for the time being we will only rely on helper query filters and per-request authorization using the permission rules dictionnary.