1 files changed, 27 insertions, 0 deletions
diff --git a/migrations/20240116060938_init_x500s.sql b/migrations/20240116060938_init_x500s.sql
new file mode 100644
index 0000000..3d5ca68
--- /dev/null
+++ b/migrations/20240116060938_init_x500s.sql
@@ -0,0 +1,27 @@
+-- We know all the X.500s of students we expect to have.
+CREATE TABLE IF NOT EXISTS student_x500s
+  ( x500 TEXT NOT NULL
+  , UNIQUE(x500)
+  );
+
+-- In the common case, we can just map users to their X.500s.
+CREATE TABLE IF NOT EXISTS uids_to_x500s_clean
+  ( uid  TEXT NOT NULL
+  , x500 TEXT NOT NULL
+  , FOREIGN KEY(x500) REFERENCES student_x500s(x500)
+  , UNIQUE(uid)
+  , UNIQUE(x500)
+  );
+
+-- ...until we see someone change X.500, or try to take someone else's X.500,
+-- or use an unenrolled student's X.500.
+--
+-- These show up as a violation of one of the constraints in
+-- uids_to_x500s_clean. In that case, we still add the failing tuple to
+-- all_seen_uids_to_x500s. This lets us get a list of "suspicious tuples" by
+-- subtracting uids_to_x500s_clean from all_seen_uids_to_x500s.
+CREATE TABLE IF NOT EXISTS all_seen_uids_to_x500s
+  ( uid  TEXT NOT NULL
+  , x500 TEXT NOT NULL
+  , UNIQUE(uid, x500)
+  );