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]

Reply via email to