soft-serve

  1CREATE TABLE IF NOT EXISTS settings (
  2  id SERIAL PRIMARY KEY,
  3  key TEXT NOT NULL UNIQUE,
  4  value TEXT NOT NULL,
  5  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  6  updated_at TIMESTAMP NOT NULL
  7);
  8
  9CREATE TABLE IF NOT EXISTS users (
 10  id SERIAL PRIMARY KEY,
 11  username TEXT NOT NULL UNIQUE,
 12  admin BOOLEAN NOT NULL,
 13  password TEXT,
 14  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 15  updated_at TIMESTAMP NOT NULL
 16);
 17
 18CREATE TABLE IF NOT EXISTS public_keys (
 19  id SERIAL PRIMARY KEY,
 20  user_id INTEGER NOT NULL,
 21  public_key TEXT NOT NULL UNIQUE,
 22  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 23  updated_at TIMESTAMP NOT NULL,
 24  CONSTRAINT user_id_fk
 25  FOREIGN KEY(user_id) REFERENCES users(id)
 26  ON DELETE CASCADE
 27  ON UPDATE CASCADE
 28);
 29
 30CREATE TABLE IF NOT EXISTS repos (
 31  id SERIAL PRIMARY KEY,
 32  name TEXT NOT NULL UNIQUE,
 33  project_name TEXT NOT NULL,
 34  description TEXT NOT NULL,
 35  private BOOLEAN NOT NULL,
 36  mirror BOOLEAN NOT NULL,
 37  hidden BOOLEAN NOT NULL,
 38  user_id INTEGER NOT NULL,
 39  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 40  updated_at TIMESTAMP NOT NULL,
 41  CONSTRAINT user_id_fk
 42  FOREIGN KEY(user_id) REFERENCES users(id)
 43  ON DELETE CASCADE
 44  ON UPDATE CASCADE
 45);
 46
 47CREATE TABLE IF NOT EXISTS collabs (
 48  id SERIAL PRIMARY KEY,
 49  user_id INTEGER NOT NULL,
 50  repo_id INTEGER NOT NULL,
 51  access_level INTEGER NOT NULL,
 52  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 53  updated_at TIMESTAMP NOT NULL,
 54  UNIQUE (user_id, repo_id),
 55  CONSTRAINT user_id_fk
 56  FOREIGN KEY(user_id) REFERENCES users(id)
 57  ON DELETE CASCADE
 58  ON UPDATE CASCADE,
 59  CONSTRAINT repo_id_fk
 60  FOREIGN KEY(repo_id) REFERENCES repos(id)
 61  ON DELETE CASCADE
 62  ON UPDATE CASCADE
 63);
 64
 65CREATE TABLE IF NOT EXISTS lfs_objects (
 66  id SERIAL PRIMARY KEY,
 67  oid TEXT NOT NULL,
 68  size INTEGER NOT NULL,
 69  repo_id INTEGER NOT NULL,
 70  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 71  updated_at TIMESTAMP NOT NULL,
 72  UNIQUE (oid, repo_id),
 73  CONSTRAINT repo_id_fk
 74  FOREIGN KEY(repo_id) REFERENCES repos(id)
 75  ON DELETE CASCADE
 76  ON UPDATE CASCADE
 77);
 78
 79CREATE TABLE IF NOT EXISTS lfs_locks (
 80  id SERIAL PRIMARY KEY,
 81  repo_id INTEGER NOT NULL,
 82  user_id INTEGER NOT NULL,
 83  path TEXT NOT NULL,
 84  refname TEXT,
 85  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 86  updated_at TIMESTAMP NOT NULL,
 87  UNIQUE (repo_id, path),
 88  CONSTRAINT repo_id_fk
 89  FOREIGN KEY(repo_id) REFERENCES repos(id)
 90  ON DELETE CASCADE
 91  ON UPDATE CASCADE,
 92  CONSTRAINT user_id_fk
 93  FOREIGN KEY(user_id) REFERENCES users(id)
 94  ON DELETE CASCADE
 95  ON UPDATE CASCADE
 96);
 97
 98CREATE TABLE IF NOT EXISTS access_tokens (
 99  id SERIAL PRIMARY KEY,
100  name text NOT NULL,
101  token TEXT NOT NULL UNIQUE,
102  user_id INTEGER NOT NULL,
103  expires_at TIMESTAMP,
104  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
105  updated_at TIMESTAMP NOT NULL,
106  CONSTRAINT user_id_fk
107  FOREIGN KEY (user_id) REFERENCES users(id)
108  ON DELETE CASCADE
109  ON UPDATE CASCADE
110);