kemono2/db/schema/public/posts/posts.sql

-- Posts
CREATE TABLE posts(
  "id" varchar(255) NOT NULL,
  "user" varchar(255) NOT NULL,
  "service" varchar(20) NOT NULL,
  "title" text NOT NULL DEFAULT '',
  "content" text NOT NULL DEFAULT '',
  "embed" jsonb NOT NULL DEFAULT '{}',
  "shared_file" boolean NOT NULL DEFAULT '0',
  "added" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "published" timestamp,
  "edited" timestamp,
  "file" jsonb NOT NULL,
  "attachments" jsonb[] NOT NULL,
  poll jsonb,
  captions jsonb,
  -- wtf is this type?
  tags _CITEXT,
  PRIMARY KEY (id, service)
);

CREATE TABLE revisions(
  "revision_id" serial PRIMARY KEY,
  "id" varchar(255) NOT NULL,
  "user" varchar(255) NOT NULL,
  "service" varchar(20) NOT NULL,
  "title" text NOT NULL DEFAULT '',
  "content" text NOT NULL DEFAULT '',
  "embed" jsonb NOT NULL DEFAULT '{}',
  "shared_file" boolean NOT NULL DEFAULT '0',
  "added" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "published" timestamp,
  "edited" timestamp,
  "file" jsonb NOT NULL,
  "attachments" jsonb[] NOT NULL,
  poll jsonb,
  -- wtf is this type?
  tags _CITEXT,
  captions jsonb
);

CREATE TABLE introductory_messages(
  service varchar NOT NULL,
  user_id varchar NOT NULL,
  hash varchar NOT NULL,
  content varchar NOT NULL,
  added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (service, user_id, hash)
);

CREATE TABLE posts_incomplete_rewards(
  id varchar(255) NOT NULL,
  service varchar(20) NOT NULL,
  last_checked_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  incomplete_attachments_info jsonb NOT NULL DEFAULT '{}' ::jsonb,
  "user" varchar(255) NULL,
  CONSTRAINT posts_incomplete_rewards_pkey PRIMARY KEY (id, service)
);

CREATE TABLE posts_added_max(
  "user" varchar NOT NULL,
  service varchar NOT NULL,
  added timestamp NOT NULL,
  CONSTRAINT posts_added_max_pkey PRIMARY KEY ("user", service)
);

CREATE TRIGGER posts_added_max
  AFTER INSERT OR UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION posts_added_max();

CREATE TABLE public_posts(
  post_id text NOT NULL,
  creator_id text NOT NULL,
  service text NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  tier_price_required text,
  tier_required text[],
  published_at timestamp without time zone,
  edited_at timestamp without time zone,
  deleted_at timestamp without time zone,
  tags text[],
  like_count integer,
  comment_count integer,
  is_public boolean,
  is_nsfw boolean,
  refreshed_at timestamp without time zone,
  buy_price text,
  CONSTRAINT public_posts_pkey PRIMARY KEY (post_id, service)
);

CREATE INDEX id_idx ON posts USING HASH ("id");

CREATE INDEX service_idx ON posts USING btree("service");

CREATE INDEX added_idx ON posts USING btree("added");

CREATE INDEX published_idx ON posts USING btree("published");

CREATE INDEX user_idx ON posts USING btree("user");

CREATE INDEX updated_idx ON posts USING btree("user", "service", "added");

CREATE INDEX posts_tags_idx ON public.posts USING gin(tags);

CREATE INDEX posts_user_published_id_idx ON posts USING btree("user", published, id);

CREATE INDEX posts_incomplete_rewards_service_user_idx ON posts_incomplete_rewards USING btree(service, "user");

CREATE INDEX revisions_id_idx ON revisions USING HASH (id);

CREATE INDEX introductory_messages_user_id_added_idx ON introductory_messages USING btree(user_id, added);

CREATE INDEX public_posts_creator_id_service_idx ON public_posts USING btree(service, creator_id);