Hey everyone, I’m working on designing a database schema for a SaaS and plan to use Ory Keto for managing roles and permissions. I’ve decided to keep the

user_org_membership

and

user_project_membership

tables in PostgreSQL for easy querying, but I’m debating whether to include

owner_id

fields in my tables. Here are the key tables:

sql

-- Organizations table
CREATE TABLE orgs (
    id SERIAL PRIMARY KEY,
    org_id UUID NOT NULL UNIQUE DEFAULT uuid_generate_v4(),
    name VARCHAR(50) NOT NULL,
    description TEXT,
    owner_id UUID NOT NULL -- Kratos Ory UUID for the owner
);

-- Projects table
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    project_id UUID NOT NULL UNIQUE DEFAULT uuid_generate_v4(),
    name VARCHAR(50) NOT NULL,
    description TEXT,
    owner_id UUID NOT NULL, -- Kratos Ory UUID for the owner
    org_id INT NOT NULL,   -- Reference the internal serial key of orgs
    FOREIGN KEY (org_id) REFERENCES orgs(id)
);

-- User organization membership table
CREATE TABLE user_org_membership (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL,  -- Kratos Ory UUID for the user
    org_id INT NOT NULL,   -- Reference the internal serial key of orgs
    FOREIGN KEY (org_id) REFERENCES orgs(id)
);

-- User project membership table
CREATE TABLE user_project_membership (
    id SERIAL PRIMARY KEY,
    user_id UUID NOT NULL,  -- Kratos Ory UUID for the user
    project_id INT NOT NULL,   -- Reference the internal serial key of projects
    FOREIGN KEY (project_id) REFERENCES projects(id)
);

My thinking is that roles (including ownership) can be managed through Keto, making the

owner_id

field redundant. However, I’m concerned that removing

owner_id

might compromise the integrity and performance of the core app schema. Similarly with some of the helper tables like

user_org_membership

and `user_project_membership`… the data will technically exist in Keto, and I can do a RelationQuery to fetch the correct ids… but is that an antipattern? Would you recommend keeping the

owner_id

field in the database schema for integrity and easier querying, or should I fully rely on Keto (Single source of truth) for all role and ownership management? Thanks in advance for your insights!