Skip to content

Adjust the database schema for unified resource management #9484

Open

Description

@bobbai00

Task Summary

Today each kind keeps its common parts in its own tables: 17 of the 42 tables are per-kind copies, such as the four *_user_access tables. This task adds the tables of #9395's model next to today's tables and fills them, without changing any of today's code.

Before

before-er-diagram

After

after-er-diagram
  1. Schema updates in every deployment. Today only local development applies sql/updates: single-node and Helm load texera_ddl.sql only into a new database, and the Liquibase changelog lists only updates 23 to 52. Every deployment first needs to apply new updates by itself, and the jOOQ classes should be generated from the updates rather than from each developer's own database.
  2. New tables. resource (keyed by a random rid), resource_version (keyed by (rid, vno)), access ((rid, uid) with a level: own, write or read) and lineage. Every existing resource gets a rid, and today's rows are copied in.
  3. Kept in step. Triggers copy every write to today's tables into the new ones, so new code can read every kind while today's code does not change. When a kind moves to the resource service (Introduce the unified resource service and move each kind onto it #9489), its triggers are dropped and its old tables become read-only views with the same names, so code that still reads them keeps working until Remove the legacy resource routes, tables and services #9492 removes them.

Discussion points

  1. A kind's own fields: JSON in resource.spec, checked against the API definition (Define the resource service's API in one contract and generate code from it #9485), or a table for every kind. With JSON, adding a field needs no migration, and a kind gets a table only when its fields need an index, a constraint or a foreign key.
  2. Ids: rid is a random UUID, so ids do not reveal how many resources exist (Design of sharing and access control of resources (computing units, datasets, and workflows) #3749). Old ids (wid, did and so on) stay as a mapping to rid, because links and stored result paths contain them.
  3. Executions: keep their own eid, or become the weak entity that Design of sharing and access control of resources (computing units, datasets, and workflows) #3749 decided.

Done when every resource, grant and version has its row in the new tables, the triggers keep them current, and today's tests pass unchanged.

Task Type

  • Refactor / Cleanup
  • DevOps / Deployment / CI
  • Testing / QA
  • Documentation
  • Performance
  • Other

Activity

  1. added theissue type on Nov 16, 2026
  2. changed the title [-]Apply database schema updates automatically in every deployment[/-] [+]Adjust the database schema for unified resource management[/+] on Nov 17, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions