Metabase housekeeping.sql

CREATE TABLE IF NOT EXISTS public.activity_history (
  id integer,
  topic character varying(32) not null,
  timestamp timestamp with time zone not null,
  user_id integer,
  model character varying(16),
  model_id integer,
  database_id integer,
  table_id integer,
  custom_id character varying(48),
  details text not null
);

CREATE TABLE IF NOT EXISTS public.application_permissions_revision_history (
  id integer,
  before text not null,
  after text not null,
  user_id integer not null,
  created_at timestamp without time zone not null,
  remark text
);

CREATE TABLE IF NOT EXISTS public.collection_permission_graph_revision_history (
  id integer,
  before text not null,
  after text not null,
  user_id integer not null,
  created_at timestamp without time zone not null,
  remark text
);

CREATE TABLE IF NOT EXISTS public.core_session_history (
  id character varying(254),
  user_id integer not null,
  created_at timestamp with time zone not null,
  anti_csrf_token text
);

CREATE TABLE IF NOT EXISTS public.login_history (
  id integer,
  timestamp timestamp with time zone not null ,
  user_id integer not null,
  session_id character varying(254),
  device_id character(36) not null,
  device_description text not null,
  ip_address text not null
);

CREATE TABLE IF NOT EXISTS public.query_history (
  query_hash bytea not null,
  average_execution_time integer not null,
  query text
);

CREATE TABLE IF NOT EXISTS public.query_execution_history (
  id integer ,
  hash bytea not null,
  started_at timestamp with time zone not null,
  running_time integer not null,
  result_rows integer not null,
  native boolean not null,
  context character varying(32),
  error text,
  executor_id integer,
  card_id integer,
  dashboard_id integer,
  pulse_id integer,
  database_id integer,
  cache_hit boolean
);

CREATE TABLE IF NOT EXISTS public.revision_history (
  id integer,
  model character varying(16) not null,
  model_id integer not null,
  user_id integer not null,
  timestamp timestamp with time zone not null,
  object text not null,
  is_reversion boolean not null default false,
  is_creation boolean not null default false,
  message text
);

CREATE TABLE IF NOT EXISTS public.task_history_history (
  id integer,
  task character varying(254) not null,
  db_id integer,
  started_at timestamp with time zone not null,
  ended_at timestamp with time zone not null,
  duration integer not null,
  task_details text
);

CREATE TABLE IF NOT EXISTS public.view_log_history (
  id integer,
  user_id integer,
  model character varying(16) not null,
  model_id integer not null,
  timestamp timestamp with time zone not null,
  metadata text
);

-- This needs to be run as a cron job every 30 days

INSERT INTO public.activity_history (SELECT * FROM public.activity WHERE "timestamp" < NOW() - INTERVAL '30 day');
DELETE FROM public.activity WHERE "timestamp" < NOW() - INTERVAL '30 day';
VACUUM FULL public.activity;

INSERT INTO public.application_permissions_revision_history (SELECT * FROM public.application_permissions_revision WHERE "created_at" < NOW() - INTERVAL '30 day');
DELETE FROM public.application_permissions_revision WHERE "created_at" < NOW() - INTERVAL '30 day';
VACUUM FULL public.application_permissions_revision;

INSERT INTO public.collection_permission_graph_revision_history (SELECT * FROM public.collection_permission_graph_revision WHERE "created_at" < NOW() - INTERVAL '30 day');
DELETE FROM public.collection_permission_graph_revision WHERE "created_at" < NOW() - INTERVAL '30 day';
VACUUM FULL public.collection_permission_graph_revision;

INSERT INTO public.core_session_history (SELECT *  FROM public.core_session WHERE "created_at" < NOW() - INTERVAL '30 day');
DELETE FROM public.core_session WHERE "created_at" < NOW() - INTERVAL '30 day';
VACUUM FULL public.core_session;

INSERT INTO public.login_history_history (SELECT * FROM public.login_history WHERE "timestamp" < NOW() - INTERVAL '30 day');
DELETE FROM public.login_history WHERE "timestamp" < NOW() - INTERVAL '30 day';
VACUUM FULL public.login_history;

INSERT INTO public.query_execution_history (SELECT * FROM public.query_execution WHERE "started_at" < NOW() - INTERVAL '30 day');
DELETE FROM public.query_execution WHERE "started_at" < NOW() - INTERVAL '30 day';
VACUUM FULL public.query_execution;

INSERT INTO public.revision_history (SELECT * FROM public.revision WHERE "timestamp" < NOW() - INTERVAL '30 day');
DELETE FROM public.revision WHERE "timestamp" < NOW() - INTERVAL '30 day';
VACUUM FULL public.revision;

INSERT INTO public.task_history_history (SELECT * FROM public.task_history WHERE "ended_at" < NOW() - INTERVAL '30 day');
DELETE FROM public.task_history WHERE "ended_at" < NOW() - INTERVAL '30 day';
VACUUM FULL public.task_history;

INSERT INTO public.view_log_history (SELECT * FROM public.view_log WHERE "timestamp" < NOW() - INTERVAL '30 day');
DELETE FROM public.view_log WHERE "timestamp" < NOW() - INTERVAL '30 day';
VACUUM FULL public.view_log;