Memebrships: Refactor Store interface
Use unified memberships table.
Add few internal API endpoints.
Change-Id: I80ac5a0f5c262e04d7898cca571b938a35d68d39
diff --git a/core/auth/memberships/schema.sql b/core/auth/memberships/schema.sql
new file mode 100644
index 0000000..be7d9c3
--- /dev/null
+++ b/core/auth/memberships/schema.sql
@@ -0,0 +1,35 @@
+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)
+);