CREATE EXTENSION IF NOT EXISTS hstore;

CREATE SCHEMA IF NOT EXISTS SELECTED_SCHEMA_NAME;
REVOKE ALL ON SCHEMA SELECTED_SCHEMA_NAME FROM public;

COMMENT ON SCHEMA SELECTED_SCHEMA_NAME IS 'Out-of-table audit/history logging tables and trigger functions';

--
-- Audited data. Lots of information is available, it's just a matter of how much
-- you really want to record. See:
--
--   http://www.postgresql.org/docs/9.1/static/functions-info.html
--
-- Remember, every column you add takes up more audit table space and slows audit
-- inserts.
--
-- Every index you add has a big impact too, so avoid adding indexes to the
-- audit table unless you REALLY need them. The hstore GIST indexes are
-- particularly expensive.
--
-- It is sometimes worth copying the audit table, or a coarse subset of it that
-- you're interested in, into a temporary table where you CREATE any useful
-- indexes and do your analysis.
--
CREATE TABLE IF NOT EXISTS SELECTED_SCHEMA_NAME.logged_actions (
  event_id bigserial primary key,
  schema_name text not null,
  table_name text not null,
  full_name text not null,
  relid oid not null,
  session_user_name text,
  app_user_id integer,
  app_user_type text,
  app_ip_address inet,
  action_tstamp_tx TIMESTAMP WITH TIME ZONE NOT NULL,
  action_tstamp_stm TIMESTAMP WITH TIME ZONE NOT NULL,
  action_tstamp_clk TIMESTAMP WITH TIME ZONE NOT NULL,
  transaction_id bigint,
  application_name text,
  client_addr inet,
  client_port integer,
  client_query text,
  action TEXT NOT NULL CHECK (action IN ('I','D','U', 'T', 'A')),
  row_id bigint,
  row_data hstore,
  changed_fields hstore,
  statement_only boolean not null
);

REVOKE ALL ON SELECTED_SCHEMA_NAME.logged_actions FROM public;

COMMENT ON TABLE SELECTED_SCHEMA_NAME.logged_actions IS 'History of auditable actions on audited tables, from SELECTED_SCHEMA_NAME.if_modified_func()';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.event_id IS 'Unique identifier for each auditable event';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.schema_name IS 'Database schema audited table for this event is in';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.table_name IS 'Non-schema-qualified table name of table event occured in';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.full_name IS 'schema-qualified table name of table event occured in';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.relid IS 'Table OID. Changes with drop/create. Get with ''tablename''::regclass';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.session_user_name IS 'Login / session user whose statement caused the audited event';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.app_user_id IS 'Application-provided polymorphic user id';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.app_user_type IS 'Application-provided polymorphic user type';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.app_ip_address IS 'Application-provided ip address of user whose statement caused the audited event';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.action_tstamp_tx IS 'Transaction start timestamp for tx in which audited event occurred';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.action_tstamp_stm IS 'Statement start timestamp for tx in which audited event occurred';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.action_tstamp_clk IS 'Wall clock time at which audited event''s trigger call occurred';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.transaction_id IS 'Identifier of transaction that made the change. May wrap, but unique paired with action_tstamp_tx.';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.client_addr IS 'IP address of client that issued query. Null for unix domain socket.';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.client_port IS 'Remote peer IP port address of client that issued query. Undefined for unix socket.';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.client_query IS 'Top-level query that caused this auditable event. May be more than one statement.';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.application_name IS 'Application name set when this audit event occurred. Can be changed in-session by client.';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.action IS 'Action type; I = insert, D = delete, U = update, T = truncate, A = archive';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.row_id IS 'Record primary_key. Null for statement-level trigger. Prefers NEW.id if exists';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.row_data IS 'Record value. Null for statement-level trigger. For INSERT this is the new tuple. For DELETE and UPDATE it is the old tuple.';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.changed_fields IS 'New values of fields changed by UPDATE. Null except for row-level UPDATE events.';
COMMENT ON COLUMN SELECTED_SCHEMA_NAME.logged_actions.statement_only IS '''t'' if audit event is from an FOR EACH STATEMENT trigger, ''f'' for FOR EACH ROW';

CREATE INDEX IF NOT EXISTS logged_actions_relid_idx ON SELECTED_SCHEMA_NAME.logged_actions(relid);
CREATE INDEX IF NOT EXISTS logged_actions_table_name_idx ON SELECTED_SCHEMA_NAME.logged_actions(table_name);
CREATE INDEX IF NOT EXISTS logged_actions_full_name_idx ON SELECTED_SCHEMA_NAME.logged_actions(full_name);
CREATE INDEX IF NOT EXISTS logged_actions_action_tstamp_tx_stm_idx ON SELECTED_SCHEMA_NAME.logged_actions(action_tstamp_stm);
CREATE INDEX IF NOT EXISTS logged_actions_action_idx ON SELECTED_SCHEMA_NAME.logged_actions(action);
CREATE INDEX IF NOT EXISTS logged_actions_row_id_idx ON SELECTED_SCHEMA_NAME.logged_actions(row_id);

DROP VIEW IF EXISTS SELECTED_SCHEMA_NAME.logged_actions_view CASCADE;
CREATE OR REPLACE VIEW SELECTED_SCHEMA_NAME.logged_actions_view AS SELECT * FROM SELECTED_SCHEMA_NAME.logged_actions;
ALTER VIEW SELECTED_SCHEMA_NAME.logged_actions_view ALTER COLUMN event_id SET DEFAULT NEXTVAL('"SELECTED_SCHEMA_NAME"."logged_actions_event_id_seq"');