Union Views

A union view is a read-only merged read over several sibling tables in one application: every member’s rows in a single filterable, sortable, keyset-paginable list, with a synthesized column naming the member each row came from.

Use one to answer “show me all my work” across tables that are genuinely separate things — tasks, incidents, and changes — without forcing them into one table.

When to use a union view

• Unified work lists – tasks, incidents, and changes in one queue
• Activity feeds – events from several tables on one timeline
• Cross-type search – filter and sort across record types that share fields

If the records are really the same thing, use one table with a status or category field instead. A union view is for reading across tables you have deliberately kept apart.

Two things a union view is not

Both of these are structural guarantees, not current limitations:

• It is read-only, at every layer. Create, update, delete, bulk, import, and restore all refuse a union name, and the error names the concrete member tables to write to instead. A union view mints no ids: every row’s id belongs to the member that created it. That is what keeps one record to one name.
• It grants nothing. A grant on the union authorizes addressing it. The rows you get back are those from members you can also read. A caller granted the union but none of its members gets 403, not an empty list.

A union view is never registered as a table. GET /api/apps/{app}/tables does not list one, and its name resolves in no table lookup.

Declaring a union view

POST /api/apps/{app}/unions
{
  "name": "work",
  "display_name": "All Work",
  "type_field": "work_type",
  "members": [
    {"table": "tasks",     "type_value": "task",     "display_name": "Task"},
    {"table": "incidents", "type_value": "incident", "display_name": "Incident"},
    {"table": "changes",   "type_value": "change",   "display_name": "Change"}
  ]
}

Each member names a table and the type_value that identifies its rows in the discriminator column. display_name, color, and position are optional per member.

type_field is optional too. When omitted, the discriminator column is _type — prefixed so it cannot collide with a member’s own field. Declaring a union whose type field matches a projected column is refused.

Managing union views requires admin. Declaring one rebuilds a physical view through the DDL layer, which is why there is no non-admin authoring path in this version.

• GET /api/apps/{app}/unions – list union views in the application
• POST /api/apps/{app}/unions – declare one
• GET /api/apps/{app}/unions/{name} – fetch one and its derived field set
• PUT /api/apps/{app}/unions/{name}/members – replace the member set
• DELETE /api/apps/{app}/unions/{name} – delete it

Reading a union view

Rows come back through the ordinary record route — there is no separate records endpoint:

GET /api/apps/{app}/tables/work/records

The name resolves in the table registry first and falls back to the union registry on a miss. Filtering, sorting, and keyset pagination work exactly as they do on a table:

GET /api/apps/{app}/tables/work/records?filter=eq(status,open)&sort=-created_at

Each row carries the discriminator naming its member:

[
  {"id": "...", "work_type": "task", "title": "Add login page"},
  {"id": "...", "work_type": "incident", "title": "Checkout returning 500"}
]

The value is the member’s type_value, not its table name.

Permissions

Each member’s permissions apply independently and on top of the union’s own grant:

• You must be able to address the union — a read grant on its name.
• You then see rows only from the members you can also read.
• Column masks intersect per column across the members that declare them.
• Each member’s row-level security binds only to that member’s own rows.

Because a grant on the union does not imply a grant on any member, widening access to a union view never widens access to data. See Permissions.

Migrating from virtual tables

Virtual tables were removed. They were writable, routed creates to a source table by discriminator value, and could carry a shared counter across sources; union views do none of those things by design, because a record that had two names — its own and the virtual table’s — was the source of the defects that removed the feature.

To port one:

Virtual table	Union view
table_type: "virtual" on POST .../tables	POST /api/apps/{app}/unions
virtual_config.sources[]	members[]
virtual_config.type_field	type_field (default _type)
Writes through the virtual table	Write to the member table directly
Shared counter across sources	Per-table counter fields
POST .../tables/{vt}/sources	PUT /api/apps/{app}/unions/{name}/members