schema.sql

-- Applied once via Neon (project sparkling-recipe-46789545). Idempotent.
CREATE TABLE IF NOT EXISTS members (
  email TEXT PRIMARY KEY,
  clerk_id TEXT UNIQUE,
  name TEXT NOT NULL DEFAULT '',
  headline TEXT NOT NULL DEFAULT '',
  skills TEXT NOT NULL DEFAULT '',
  contact_url TEXT NOT NULL DEFAULT '',
  avatar_url TEXT NOT NULL DEFAULT '',
  approved INTEGER NOT NULL DEFAULT 0,
  is_admin INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (now()::text)
);

CREATE TABLE IF NOT EXISTS projects (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_email TEXT NOT NULL REFERENCES members(email),
  title TEXT NOT NULL,
  pitch TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  progress_pct INTEGER NOT NULL DEFAULT 80,
  stack TEXT NOT NULL DEFAULT '',
  demo_url TEXT NOT NULL DEFAULT '',
  image_url TEXT NOT NULL DEFAULT '',
  favicon_url TEXT NOT NULL DEFAULT '',
  missing_roles TEXT NOT NULL DEFAULT '',
  compensation TEXT NOT NULL DEFAULT '',
  timeline TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'open',
  hidden INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (now()::text)
);

ALTER TABLE members ADD COLUMN IF NOT EXISTS avatar_url TEXT NOT NULL DEFAULT '';

ALTER TABLE projects ADD COLUMN IF NOT EXISTS image_url TEXT NOT NULL DEFAULT '';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS favicon_url TEXT NOT NULL DEFAULT '';
ALTER TABLE projects ADD COLUMN IF NOT EXISTS hidden INTEGER NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS interests (
  project_id INTEGER NOT NULL REFERENCES projects(id),
  member_email TEXT NOT NULL REFERENCES members(email),
  role TEXT NOT NULL DEFAULT '',
  note TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT (now()::text),
  PRIMARY KEY (project_id, member_email)
);

INSERT INTO members (email, name, approved, is_admin)
  VALUES ('upb.alexander@gmail.com', 'Alex Constantin', 1, 1)
  ON CONFLICT (email) DO NOTHING;