refactor: adopt DBML schema models and use relspecgo to generate SQL migrations #19
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: wdevs/amcs#19
Reference in New Issue
Block a user
Overview
Migrate the database schema source-of-truth from raw SQL migration files to DBML (Database Markup Language). SQL migrations should be generated from the DBML models using relspecgo rather than hand-authored.
Background
relspecgo reads DBML and can write PostgreSQL DDL, which gives us a single authoritative schema definition that tools, docs, and AI can all consume. This replaces the current workflow of writing migrations by hand.
Requirements
DBML Schema
schema/(ordb/schema/) directory containing.dbmlfiles representing all current tablesthoughts.dbml,projects.dbml,files.dbml,skills.dbml)Migration Generation
relspecgo(https://git.warky.dev/wdevs/relspecgo) to generate PostgreSQL SQL from the DBML filesmigrations/as beforemake schemaormake generate-migrationstarget that runs relspecgo and outputs the migration SQLDeveloper Workflow
.dbmlfiles to change the schemamake generate-migrations— relspecgo produces a new migration SQL file.dbmlchange and the generated migrationCI
Acceptance Criteria
schema/*.dbmlexists and covers all current tablesmake generate-migrationsruns relspecgo and produces valid PostgreSQL DDL inmigrations/Create db/schema directory with the .dbml files. Here is an example schema layout:
// Organization Schema - Core organizational entities and access control
// Optimized for consistency and maintainability
// ============================================================================
// CORE ORGANIZATIONAL ENTITIES
// ============================================================================
Table "org"."owner" {
"id_owner" bigserial [pk, not null, increment]
"guid" uuid [unique, not null]
"disabled" boolean [default: false]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"ownertype" text [not null]
"name" text [not null]
"jsonstore" jsonb
Note: 'Top-level organizational entity (company, department, etc.)'
}
Table "org"."role" {
"id_role" bigserial [pk, not null, increment]
"rid_owner" bigint [not null]
"rid_parent_role" bigint [ref: > "org"."role"."id_role"]
"rid_typelookup" bigint [ref: > "org"."owner_typelookup"."id_owner_typelookup", note: 'category: role_type']
"guid" uuid [unique, not null]
"disabled" boolean [default: false]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"name" text [not null]
"code" text
"description" text
"jsonstore" jsonb
Note: 'Hierarchical role definitions for access control'
}
Ref: "org"."role"."rid_owner" - "org"."owner"."id_owner" [delete: cascade, update: restrict]
Table "org"."owner_typelookup" {
"id_owner_typelookup" bigserial [pk, not null, increment]
"rid_owner" bigint [not null]
"guid" uuid [unique, not null]
"disabled" boolean [default: false]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"category" text [not null]
"key" text [not null]
"value" text [not null]
"jsonstore" jsonb
Indexes {
(category, key, rid_owner) [unique, name: "uidx_owner_typelookup_category_key"]
(category, key, value, rid_owner) [unique, name: "uidx_owner_typelookup_full"]
}
Note: 'Owner-specific type definitions and enumerations'
}
Ref: "org"."owner_typelookup"."rid_owner" - "org"."owner"."id_owner" [delete: cascade, update: restrict]
// ============================================================================
// API PROVIDER & AUTHENTICATION
// ============================================================================
Table "org"."api_provider" {
"id_api_provider" bigserial [pk, not null, increment]
"rid_owner" bigint [not null]
"guid" uuid [unique, not null]
"disabled" boolean [default: false]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"subtype" text [not null]
"apikey" text
"jsonstore" jsonb
Note: 'External API provider configurations (OAuth, SAML, etc.)'
}
Ref: "org"."api_provider"."rid_owner" - "org"."owner"."id_owner" [delete: cascade, update: restrict]
Table "org"."login" {
"id_login" bigserial [pk, not null, increment]
"rid_api_provider" bigint
"guid" uuid [unique, not null]
"disabled" boolean [default: false]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"authsource" text [not null]
"username" text [not null]
"passwordhash" text
"authreference" text
"jsonstore" jsonb
Indexes {
(username, authsource) [unique, name: "uidx_login_username_source"]
}
Note: 'User authentication credentials'
}
Ref: "org"."login"."rid_api_provider" - "org"."api_provider"."id_api_provider" [delete: restrict, update: restrict]
Table "org"."login_session" {
"id_loginsession" bigserial [pk, not null, increment]
"rid_login" bigint [not null]
"guid" uuid [unique, not null]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"expireat" timestamp [not null]
"authtoken" text [not null]
"ipaddress" text
"agentdata" jsonb
"jsonstore" jsonb
Indexes {
authtoken [unique, name: "uidx_login_session_token"]
expireat [name: "idx_login_session_expiry"]
}
Note: 'Active user sessions with tokens'
}
Ref: "org"."login_session"."rid_login" - "org"."login"."id_login" [delete: cascade, update: restrict]
Table "org"."login_setting" {
"id_login_setting" bigserial [pk, not null, increment]
"rid_login" bigint [not null]
"guid" uuid [unique, not null]
"disabled" boolean [default: false]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"name" text [not null]
"value" text
"jsonstore" jsonb
Indexes {
(rid_login, name) [unique, name: "uidx_login_setting_name"]
}
Note: 'User-specific settings and preferences'
}
Ref: "org"."login_setting"."rid_login" - "org"."login"."id_login" [delete: cascade, update: restrict]
// ============================================================================
// ROLE ASSIGNMENTS & ACCESS CONTROL
// ============================================================================
Table "org"."login_role" {
"id_login_role" bigserial [pk, not null, increment]
"rid_login" bigint [not null]
"rid_role" bigint [not null]
"rid_owner" bigint [not null]
"guid" uuid [unique, not null]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]Indexes {
(rid_login, rid_role, rid_owner) [unique, name: "uidx_login_role_assignment"]
}
Note: 'Maps users to roles within owners'
}
Ref: "org"."login_role"."rid_owner" - "org"."owner"."id_owner" [delete: cascade, update: restrict]
Ref: "org"."login_role"."rid_login" - "org"."login"."id_login" [delete: cascade, update: restrict]
Ref: "org"."login_role"."rid_role" - "org"."role"."id_role" [delete: cascade, update: restrict]
Table "org"."role_data_access" {
"id_role_data_access" bigserial [pk, not null, increment]
"rid_role" bigint [not null]
"guid" uuid [unique, not null]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"blockmode" smallint [not null, note: '1=Allow, 2=Deny']
"tablename" text [not null]
"rid_table" bigint
"jsonstore" jsonb
Indexes {
(rid_role, tablename) [name: "idx_role_data_access_table"]
}
Note: 'Table-level data access permissions'
}
Ref: "org"."role_data_access"."rid_role" - "org"."role"."id_role" [delete: cascade, update: restrict]
Table "org"."role_route_access" {
"id_role_route_access" bigserial [pk, not null, increment]
"rid_role" bigint [not null]
"guid" uuid [unique, not null]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"blockmode" smallint [not null, note: '1=Allow, 2=Deny']
"routetype" text [not null]
"routepath" text [not null]
"can_read" boolean [default: false]
"can_create" boolean [default: false]
"can_update" boolean [default: false]
"can_delete" boolean [default: false]
"jsonstore" jsonb
Indexes {
(rid_role, routepath) [name: "idx_role_route_access_path"]
}
Note: 'API route-level access permissions'
}
Ref: "org"."role_route_access"."rid_role" - "org"."role"."id_role" [delete: cascade, update: restrict]
Table "org"."role_component_access" {
"id_role_component_access" bigserial [pk, not null, increment]
"rid_role" bigint [not null]
"guid" uuid [unique, not null]
"createdat" timestamp [not null, default:
now()]"updatedat" timestamp [not null, default:
now()]"blockmode" smallint [not null, note: '1=Allow, 2=Deny']
"component" text [not null]
"reference" text
"jsonstore" jsonb
Indexes {
(rid_role, component, reference) [name: "idx_role_component_access_ref"]
}
Note: 'UI component-level access permissions'
}
Ref: "org"."role_component_access"."rid_role" - "org"."role"."id_role" [delete: cascade, update: restrict]
Use the relspec command line tool to generate the live migration to the database.
Add the call to the makefile.
If you ready the comment, give feedback here.