depot/go/twitterchiver/archiver/schema.sql

-- SPDX-FileCopyrightText: 2020 Luke Granger-Brown <depot@lukegb.com>
--
-- SPDX-License-Identifier: Apache-2.0

CREATE TABLE tweets (
	id BIGINT NOT NULL,
	text TEXT NOT NULL,
	object JSONB NOT NULL
);

CREATE INDEX tweets_retweet_text_idx ON public.tweets USING gin (to_tsvector('english'::regconfig, ((object -> 'retweeted_status'::text) ->> 'full_text'::text)));
CREATE INDEX tweets_retweet_user_idx ON public.tweets USING btree (((((object -> 'retweeted_status'::text) -> 'user'::text) ->> 'screen_name'::text)));
CREATE INDEX tweets_text_idx ON public.tweets USING gin (to_tsvector('english'::regconfig, text));
CREATE INDEX tweets_user_idx ON public.tweets USING btree ((((object -> 'user'::text) ->> 'screen_name'::text)));

CREATE TABLE user_accounts (
	username TEXT NOT NULL PRIMARY KEY,
	userid BIGINT NOT NULL,
	access_token TEXT NOT NULL,
	access_secret TEXT NOT NULL,
	latest_tweet BIGINT NOT NULL,
	CONSTRAINT unique_userid
		UNIQUE (userid)
);

CREATE TABLE public.user_accounts_tweets (
	userid bigint NOT NULL,
	tweetid bigint NOT NULL,
	PRIMARY KEY (userid, tweetid),
	UNIQUE (tweetid, userid),
	CONSTRAINT fk_userid
		FOREIGN KEY (userid)
			REFERENCES user_accounts(userid),
	CONSTRAINT fk_tweetid
		FOREIGN KEY (tweetid)
			REFERENCES tweets(id))
);