← back to Bubbesblock

db/schema.sql

165 lines

-- BubbesBlock schema — block posts + comments + small site config.
CREATE TABLE IF NOT EXISTS site_config (
  k text PRIMARY KEY,
  v jsonb
);

CREATE TABLE IF NOT EXISTS posts (
  id            text PRIMARY KEY,
  position      int,
  author        text NOT NULL,
  initials      text,
  color         text,
  address       text,
  time_label    text,
  edited        boolean DEFAULT false,
  category      text,
  cat_color     text,
  body          jsonb NOT NULL DEFAULT '[]'::jsonb,
  photo         jsonb,
  reactions     jsonb NOT NULL DEFAULT '[]'::jsonb,
  reacted_by    text,
  shares        int DEFAULT 0,
  more_comments int DEFAULT 0,
  source        text DEFAULT 'seed',
  created_at    timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS comments (
  id          bigserial PRIMARY KEY,
  post_id     text NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  position    int,
  author      text NOT NULL,
  initials    text,
  color       text,
  nb          text,
  time_label  text,
  thanks      int DEFAULT 0,
  is_reply    boolean DEFAULT false,
  body        text,
  created_at  timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_posts_position ON posts(position);
CREATE INDEX IF NOT EXISTS idx_comments_post ON comments(post_id, position);

-- Opportunity leads (Nextdoor "Opp Alerts" model — similar but not exact).
CREATE TABLE IF NOT EXISTS opportunities (
  id          text PRIMARY KEY,
  position    int,
  neighbor    text,
  initials    text,
  color       text,
  category    text,
  for_you     boolean DEFAULT false,
  area        text,
  address     text,
  time_label  text,
  responses   int DEFAULT 0,
  locked      boolean DEFAULT false,
  body        text,
  created_at  timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_opps_position ON opportunities(position);

-- ============ Phase A: identity + interaction tables ============
CREATE TABLE IF NOT EXISTS users (
  id         text PRIMARY KEY,
  name       text NOT NULL,
  initials   text,
  color      text,
  address    text,
  verified   boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS reactions (
  id          bigserial PRIMARY KEY,
  target_type text NOT NULL,           -- post | comment | opportunity
  target_id   text NOT NULL,
  user_id     text NOT NULL,
  kind        text DEFAULT 'thank',
  created_at  timestamptz DEFAULT now(),
  UNIQUE(target_type, target_id, user_id, kind)
);

CREATE TABLE IF NOT EXISTS opp_responses (
  id            bigserial PRIMARY KEY,
  opportunity_id text NOT NULL,
  user_id       text NOT NULL,
  body          text,
  created_at    timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS opp_unlocks (
  opportunity_id text NOT NULL,
  user_id        text NOT NULL,
  created_at     timestamptz DEFAULT now(),
  PRIMARY KEY (opportunity_id, user_id)
);

CREATE TABLE IF NOT EXISTS notifications (
  id         bigserial PRIMARY KEY,
  user_id    text NOT NULL,
  type       text,
  actor      text,
  text       text,
  link       text,
  read       boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS conversations (
  id         bigserial PRIMARY KEY,
  user_id    text NOT NULL,
  peer       text,
  peer_initials text,
  peer_color text,
  created_at timestamptz DEFAULT now()
);
CREATE TABLE IF NOT EXISTS messages (
  id         bigserial PRIMARY KEY,
  conv_id    bigint NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  from_me    boolean DEFAULT false,
  body       text,
  created_at timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS events (
  id         text PRIMARY KEY,
  title      text,
  emoji      text,
  when_label text,
  location   text,
  going      int DEFAULT 0,
  body       text,
  position   int
);
CREATE TABLE IF NOT EXISTS event_rsvps (
  event_id text NOT NULL, user_id text NOT NULL, created_at timestamptz DEFAULT now(),
  PRIMARY KEY (event_id, user_id)
);

CREATE TABLE IF NOT EXISTS groups (
  slug text PRIMARY KEY, name text, emoji text, color text, members int DEFAULT 0, blurb text, position int
);
CREATE TABLE IF NOT EXISTS group_members (
  slug text NOT NULL, user_id text NOT NULL, created_at timestamptz DEFAULT now(),
  PRIMARY KEY (slug, user_id)
);

CREATE TABLE IF NOT EXISTS bookmarks (
  user_id text NOT NULL, post_id text NOT NULL, created_at timestamptz DEFAULT now(),
  PRIMARY KEY (user_id, post_id)
);

CREATE INDEX IF NOT EXISTS idx_react_target ON reactions(target_type, target_id);
CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, read);
CREATE INDEX IF NOT EXISTS idx_oppresp_opp ON opp_responses(opportunity_id);

-- one response per (opportunity,user) — prevents counter inflation via direct API
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname='uq_opp_user') THEN
    ALTER TABLE opp_responses ADD CONSTRAINT uq_opp_user UNIQUE (opportunity_id, user_id);
  END IF;
END $$;