Index

public.page_visit_datum

Description

An anonymous piece of data used to count the number of visitors that has viewed a page.

Columns

Name Type Default Nullable Children Parents Comment
anonymous_identifier varchar(255) true An anonymous identifier of the user that is reversible and is not traceable to the user or their ip. It is hashed using a daily rotating hashing key, meaning this identifier changes for all visitors every day. The identifier is also unique for each course.
browser varchar(255) true What browser the visitor was using e.g. Firefox.
browser_version varchar(255) true What what was the version of the browser the visitor was using e.g. 252.0
country varchar(255) true Where the visitor is from. Two letter short code e.g. fi
course_id uuid true public.courses Redundant, same information available though pages.course_id.
created_at timestamp with time zone now() false Timestamp when the record was created.
deleted_at timestamp with time zone true Timestamp when the record was deleted. If null, the record is not deleted.
device_type varchar(255) true What kind of device the user was using e.g. mobile or pc.
exam_id uuid true public.exams Redundant, same information available though pages.exam_id. Here to make some queries easier.
id uuid uuid_generate_v4() false A unique, stable identifier for the record.
is_bot boolean false false Whether the user was detected to be a bot. The detection is not foolproof but it’s useful enough for example for excluding the bots from the statistics.
operating_system varchar(255) true What was the visitor’s operating system e.g. Linux
operating_system_version varchar(255) true What what was the version of the operating system the visitor was using.
page_id uuid false public.pages The page that was visited.
referrer varchar(1024) true What was the referrer of the visitor. Tells where the visitor came from.
updated_at timestamp with time zone now() false Timestamp when the record was last updated. The field is updated automatically by the set_timestamp trigger.
utm_campaign varchar(255) true
utm_content varchar(255) true
utm_medium varchar(255) true
utm_source varchar(255) true
utm_term varchar(255) true

Constraints

Name Type Definition
page_visit_datum_course_id_fkey FOREIGN KEY FOREIGN KEY (course_id) REFERENCES courses(id)
page_visit_datum_created_at_not_null n NOT NULL created_at
page_visit_datum_exam_id_fkey FOREIGN KEY FOREIGN KEY (exam_id) REFERENCES exams(id)
page_visit_datum_id_not_null n NOT NULL id
page_visit_datum_is_bot_not_null n NOT NULL is_bot
page_visit_datum_page_id_fkey FOREIGN KEY FOREIGN KEY (page_id) REFERENCES pages(id)
page_visit_datum_page_id_not_null n NOT NULL page_id
page_visit_datum_pkey PRIMARY KEY PRIMARY KEY (id)
page_visit_datum_updated_at_not_null n NOT NULL updated_at

Indexes

Name Definition
page_visit_datum_pkey CREATE UNIQUE INDEX page_visit_datum_pkey ON public.page_visit_datum USING btree (id)

Triggers

Name Definition
set_timestamp CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.page_visit_datum FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp()

Relations

er

Generated by tbls