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  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 52  updated_at TIMESTAMP NOT NULL,
 53  UNIQUE (user_id, repo_id),
 54  CONSTRAINT user_id_fk
 55  FOREIGN KEY(user_id) REFERENCES users(id)
 56  ON DELETE CASCADE
 57  ON UPDATE CASCADE,
 58  CONSTRAINT repo_id_fk
 59  FOREIGN KEY(repo_id) REFERENCES repos(id)
 60  ON DELETE CASCADE
 61  ON UPDATE CASCADE
 62);
 63
 64CREATE TABLE IF NOT EXISTS lfs_objects (
 65  id SERIAL PRIMARY KEY,
 66  oid TEXT NOT NULL,
 67  size INTEGER NOT NULL,
 68  repo_id INTEGER NOT NULL,
 69  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 70  updated_at TIMESTAMP NOT NULL,
 71  UNIQUE (oid, repo_id),
 72  CONSTRAINT repo_id_fk
 73  FOREIGN KEY(repo_id) REFERENCES repos(id)
 74  ON DELETE CASCADE
 75  ON UPDATE CASCADE
 76);
 77
 78CREATE TABLE IF NOT EXISTS lfs_locks (
 79  id SERIAL PRIMARY KEY,
 80  repo_id INTEGER NOT NULL,
 81  user_id INTEGER NOT NULL,
 82  path TEXT NOT NULL,
 83  refname TEXT,
 84  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 85  updated_at TIMESTAMP NOT NULL,
 86  UNIQUE (repo_id, path),
 87  CONSTRAINT repo_id_fk
 88  FOREIGN KEY(repo_id) REFERENCES repos(id)
 89  ON DELETE CASCADE
 90  ON UPDATE CASCADE,
 91  CONSTRAINT user_id_fk
 92  FOREIGN KEY(user_id) REFERENCES users(id)
 93  ON DELETE CASCADE
 94  ON UPDATE CASCADE
 95);
 96
 97CREATE TABLE IF NOT EXISTS access_tokens (
 98  id SERIAL PRIMARY KEY,
 99  name text NOT NULL,
100  token TEXT NOT NULL UNIQUE,
101  user_id INTEGER NOT NULL,
102  expires_at TIMESTAMP,
103  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
104  updated_at TIMESTAMP NOT NULL,
105  CONSTRAINT user_id_fk
106  FOREIGN KEY (user_id) REFERENCES users(id)
107  ON DELETE CASCADE
108  ON UPDATE CASCADE
109);