GitHub user bobbai00 closed a discussion: Proposal: Database Schema For Unified Resource Management (Layer 1)
This discussion details Layer 1 of #9395, the database schema. #9395 proposed to store the common parts of every resource once, under two supertypes, `Resource` and `Resource Version`. Here I propose the tables for that model, and a way to move today's data into them while today's code keeps working. ## The Model The model is the one from #9395. Every kind is a subtype of `Resource`, and only workflows, datasets, and models have versions, which are subtypes of `Resource Version`, keyed by `(rid, vno)`. One `access` relationship links a resource to a user with a level, and `lineage` links a version to the versions it relates to. <img width="2000" height="900" alt="after-er-diagram" src="https://github.com/user-attachments/assets/b4371c2f-7500-4837-8538-aae985b38063" /> ## Proposed Tables | Table | Key | Other columns | Replaces | | --- | --- | --- | --- | | `resource` | `rid` | `kind`, `name`, `description`, `is_public`, `created_by`, `created_at`, `updated_at`, `spec` | The common columns of `workflow`, `workflow_computing_unit`, and `user_warehouse`, and all of `dataset`, `model`, and `virtual_environments` | | `access` | `(rid, uid)` | `level`: own, write, or read | `workflow_of_user`, the four `*_user_access` tables, and the `owner_uid` and `uid` columns | | `resource_version` | `(rid, vno)` | `name`, `created_by`, `created_at`, `content_ref` | `dataset_version`, `model_version`, and the common columns of `workflow_version` | | `lineage` | `(rid, vno, source_rid, source_vno, relation)` | `eid` | Nothing: no table records lineage today | | `workflow`, `computing_unit`, `warehouse` | `rid` | Only the kind's own columns, such as `content`, `uri`, or `lakekeeper_warehouse_name` | Today's three tables without their common columns | | `workflow_version` | `(rid, vno)` | `content` | The content of today's `workflow_version` | | `execution` | `eid` | `(workflow_rid, vno)`, `computing_unit_rid`, `warehouse_rid`, `uid`, `status`, `error`, and today's other columns | `workflow_executions` | Likes, view counts, and upload sessions follow the same rule: one table each, keyed by `rid`, instead of one per kind. Tables that do not describe a resource, such as `user` and `site_settings`, stay as they are. In total, today's 42 tables become 30. ## Decisions In The Schema 1. **One id for every resource**: `rid` is new, global, and random (a UUID), so it does not reveal how many resources exist, as #3749 asked. Each kind keeps its old id (`wid`, `did`, and so on) until the legacy routes are gone, and afterwards as a mapping to `rid`, because links and stored result paths contain old ids. 2. **Tables only where SQL needs them**: a kind's own fields are stored as JSON in `resource.spec` and checked against the kind's definition in the API (Layer 2). A kind gets its own table only when its fields need an index, a constraint, or a foreign key, which today means workflows, computing units, and warehouses. Adding a field to a kind then needs no migration. 3. **Versions are metadata**, as Xiao-zhen-Liu suggested: `resource_version` records who made a version and when, and `content_ref` points to where the kind keeps the content. For a dataset or a model that is a lakeFS commit, so the files stay in lakeFS, and replacing lakeFS later changes no table. 4. **Versions are numbered within their resource**: a version is a weak entity keyed by `(rid, vno)`, as #3749 decided for workflow versions. 5. **References are lineage**, which zyratlo asked about: `uses` means a version needs another version to execute, such as a workflow version that reads a dataset version. `derived_from` means a version was made from another, such as a dataset version written by an execution, or a cloned workflow. `eid` names the execution that made it, if any. 6. **Executions keep their outcome**, as eugenegujing asked: `execution.error` stores the fatal errors, which today are not stored with the execution. An execution keeps its own `eid` instead of the composite key that #3749 decided, because lineage and stored results refer to it by one id. 7. **Paths keep working**: a file path in a workflow names a dataset by its owner's email, its name, and a version name. `resource` therefore keeps `created_by`, and `(kind, created_by, name)` stays unique for datasets and models, as it is today. ## Moving The Data Without Breaking Today's Code The rule is that at any moment each kind has one source of truth, and the other side follows it automatically. | Stage | Source of truth | Kept in step by | Today's code | | --- | --- | --- | --- | | 1. Add | Today's tables | Triggers that copy every write into the new tables | Unchanged | | 2. Switch, one kind at a time | The new tables | Read-only views with the kind's old table names and columns | Its old routes call the new code, and its old readers read the views | | 3. Remove | The new tables | Nothing | The old routes and views are gone | 1. **Add**: one migration creates the new tables next to today's, gives every resource a `rid`, and copies the existing rows. From then on, 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. 2. **Switch**: kinds move one at a time, from the smallest to the largest: warehouses and environments, then datasets and models, then computing units, and workflows last. A kind's writes move to the new code, its triggers are dropped, and its old routes call the new code, so the frontend does not notice. Its old tables become read-only views, so code that still reads them, such as search and the file resolver, keeps working. A resource created after the switch still gets an old id, so the current frontend and old links keep working. 3. **Remove**: once the frontend and the agent service use the new endpoints and nothing reads a view, the views and the old routes are dropped. Each step is checked the same way: the existing tests run unchanged against it, and a write to a read-only view fails with an error instead of going to the wrong place. One thing has to come first: today no deployment applies schema updates by itself. Single-node and Helm run only `texera_ddl.sql`, on a new database, and the Liquibase changelog lists only updates 23 to 52. Every deployment needs to apply new migrations automatically before the first stage can ship. ## Questions For This Discussion 1. **JSON or a table per kind**: are you comfortable storing the own fields of datasets, models, and environments as JSON in `resource.spec`, or do you prefer a table for every kind? 2. **Executions**: should an execution keep its own `eid`, as proposed here, or become the weak entity that #3749 decided? 3. **When a workflow version is made**: today one is made on every save that changes the workflow. Should that stay, or should a version be made only when what executes changes? 4. **What lineage holds**: `uses` and `derived_from` in one table, or only `derived_from`, with references kept by each kind? Once the schema is agreed, a separate discussion will cover Layer 2, the API definition. GitHub link: https://github.com/apache/texera/discussions/9511 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected]
