searchhut/schema.sql

CREATE TABLE domain (
	id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	hostname text NOT NULL UNIQUE,
	authoritative boolean NOT NULL,
	tags text[] NOT NULL DEFAULT '{}',
	exclusion_patterns text[] NOT NULL DEFAULT '{}',
	last_index_date timestamptz,
	crawl_duration interval
);

CREATE TABLE page (
	id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	domain_id integer NOT NULL REFERENCES domain(id),
	source varchar NOT NULL,
	url text NOT NULL UNIQUE,
	page_size integer NOT NULL,
	checksum bytea NOT NULL UNIQUE,
	last_index_date timestamptz NOT NULL,
	fts_vector tsvector NOT NULL,
	javascript boolean NOT NULL,
	title text,
	language text,
	description text,
	author text,
	excerpt text
);

CREATE EXTENSION rum;

CREATE INDEX idx_page_content ON page USING RUM (fts_vector rum_tsvector_ops);