Technical name for tables and applications, CRUD routes by application and table #3094

Is your feature request related to a problem? Please describe.

As things stand now, rows can only be addressed by their numeric table and view ids, which differ per instance, while applications that are installed by manifest/scheme get new ids on every instance they are installed, so clients cannot use fixed urls. Columns and views already have technical names, but tables don’t. P15 and the rows API plan both rely on a technical name per table and application, which upstream doesn't exist yet.

Describe the solution you'd like

Tables

We would like to add the property technical_name to tables, made unique per application so that tables have a consistent meaning throughout an application, but two applications with similar data structures are not forced into using each other’s tables. This technical name should fit the same constraints as technical names on columns and views.

The technical name will by default be table_{id}, and a backfill would be needed to create technical names for already existing tables (or links, see the open question below). Invalid technical names or technical names that are already in use for an application will be rejected with a 400 with clear message, the same as for views.

Open question: enforcing application-level uniqueness

On enforcing application-level uniqueness of a table we would like your input. We would like to add the technical name on tables_tables, but that would mean enforcement through PHP code, while adding it on tables_contexts_rel_context_node would make enforcement through SQL possible, and also at once gives both tables and views a name within an application, making the routes (see below) consistent, but also giving views a second technical name.

The tables_tables solution would need checks on uniqueness in PHP both when a table is added to an application, and when the technical name of the table is changed, while there is no database guarantee against simultaneous changes and shared tables from someone else with the same name cannot be added as you cannot rename that.

The solution in the link would be enforceable with one unique index in the database, but it means that tables only have technical names within the application, and can have different names in all applications it is used in, while outside of an application, the table has no technical name.

Applications

Similarly, we would like to add a technical name to applications, which should be unique per user. Here also the default will be application_{id}, although the form may suggest a version of the app name that follows the convention for technical names.

Routing

This would clear the way to add CRUD routes on the v2 API for /api/2/apps/{application}/{table} (GET|POST) and /api/2/apps/{application}/{table}/{rowId} (GET|PUT|DELETE) for single rows and objects (rowId should accept both the incremental id and the uuid of the row).

The response of this route will be today’s row shape, for shaping the rows as object see the object shape issue.

As in issue #3093 a helper function will be added to resolve the technical names into ids to look up the tables and applications that they belong to.

Access to tables or views can come from an application: the link between application and table contains its own permissions, and the resolver will check the user’s rights within the application, not only direct rights to the table. A user who can work only through an application can do the same through these routes, and not more.

In the documentation the ResponseDefinitions.php and openapi.json should be regenerated.

For the moment views are out of scope for this issue, but the open question above might also solve the path for views.

Acceptance Criteria

• every table (or link) has a valid technical name after the backfill;
• every application has a valid technical name after the backfill;
• an invalid or duplicate name gives a 400;
• the routes behave the same as the id routes, including permissions through an application;
• an ambiguous application name gives an error, not an arbitrary choice;
• CI is green on all four databases.

Describe alternatives you've considered

We considered to have uniqueness for table technical names per user, but this would result in inconsistent behaviour among various applications, while globally unique technical names would be impractical across various applications that use similar data structures.

We also considered using the display name in the url, but this is not stable and not URL-safe.

Additional context

• Milestone M1, Sprint 1
• P15. One identifier: the technical name
• Rows API
• Architecture
• API index

Notes

• Until the rename of context to application (P14, sprint 4), the database and the API say contexts; the new route already uses apps/{application}.
• We choose now to have unique application technical names per user, but this might collide when applications are shared among users. We would prefer to make technical names fully unique, but this has also implications for the default example applications, so we discarded this for now.

Relevant files:

• lib/Migration/Version2200Date20260707000000.php and lib/Migration/Version2200Date20260723000000.php (the technical name pattern for columns and views);
• lib/Service/ViewService.php (validation of the technical name);
• lib/Db/Table.php, lib/Db/TableMapper.php;
• lib/Db/Context.php, lib/Db/ContextMapper.php, lib/Db/ContextNodeRelation.php, lib/Db/ContextNodeRelationMapper.php;
• lib/Service/ContextService.php, lib/Controller/ContextController.php;
• lib/Controller/RowOCSController.php (the existing api/2 row routes);

Related issues

This depends on the UUIDs for rows issue (row routes by integer id or uuid). The shape of the responses follows the rows v2 listing issue and the object shape issue. This comes before
the manifest export and the rename of context to application (issues yet to be filed).