core/auth/memberships/schema.sql

CREATE TABLE IF NOT EXISTS users (
	   id TEXT PRIMARY KEY,
	   username TEXT NOT NULL,
	   email TEXT NOT NULL,
	   UNIQUE(username),
	   UNIQUE (email)
);

CREATE TABLE IF NOT EXISTS keys (
	   user_id TEXT,
	   public_key TEXT,
	   UNIQUE (public_key),
	   FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS groups (
	   id TEXT PRIMARY KEY,
	   title TEXT NOT NULL,
	   description TEXT,
	   external_id TEXT
);

CREATE TABLE IF NOT EXISTS memberships (
	   id TEXT NOT NULL,
	   membership_type TEXT CHECK(membership_type IN ('member', 'owner')) NOT NULL,
	   member_type TEXT CHECK(member_type IN ('user', 'group')) NOT NULL,
	   user_id TEXT,
	   group_id TEXT,
	   FOREIGN KEY(id) REFERENCES groups(id)
	   FOREIGN KEY(user_id) REFERENCES users(id),
	   FOREIGN KEY(group_id) REFERENCES groups(id),
	   CHECK(user_id IS NULL OR group_id IS NULL),
	   CHECK((member_type = 'user' AND user_id IS NOT NULL) OR (member_type = 'group' AND group_id IS NOT NULL)),
	   UNIQUE (id, membership_type, member_type, user_id, group_id)
);