Database Schema

List of Table

SELECT relname, relkind
FROM   pg_class
WHERE relreplident = 'd'
AND relhasindex = true;

List of Table

relname	relkind
alembic_version	r
administration	r
access	r
question_group	r
form	r
question	r
data	r
answer	r
organisation	r
jobs	r
log	r
option	r
history	r
user	r

Table Relationship

List of Relationship

Schema	Name	Type	Owner
public	access	table	wai
public	access_id_seq	sequence	wai
public	administration	table	wai
public	administration_id_seq	sequence	wai
public	alembic_version	table	wai
public	answer	table	wai
public	answer_id_seq	sequence	wai
public	answer_search	view	wai
public	data	table	wai
public	data_id_seq	sequence	wai
public	form	table	wai
public	form_id_seq	sequence	wai
public	history	table	wai
public	history_id_seq	sequence	wai
public	jobs	table	wai
public	jobs_id_seq	sequence	wai
public	log	table	wai
public	log_id_seq	sequence	wai
public	option	table	wai
public	option_id_seq	sequence	wai
public	organisation	table	wai
public	organisation_id_seq	sequence	wai
public	question	table	wai
public	question_group	table	wai
public	question_group_id_seq	sequence	wai
public	question_id_seq	sequence	wai
public	score_view	view	wai
public	user	table	wai
public	user_id_seq	sequence	wai

Table Details

Administration

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'administration'
ORDER  BY ordinal_position;

List of Relationship

pos	column_name	data_type	udt_name	column_default	is_nullable
1	id	integer	int4	nextval(‘administration_id_seq’::regclass)	NO
2	parent	integer	int4		YES
3	name	character varying	varchar		YES

User

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'user'
ORDER  BY ordinal_position;

User Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘user_id_seq’::regclass)	NO
2	email	character varying		YES
3	active	boolean		YES
4	role	USER-DEFINED		YES
5	created	timestamp without time zone		YES
6	organisation	integer		YES
7	name	character varying		YES
8	__ts_vector__	tsvector		YES
9	manage_form_passcode	boolean	false	NO

User Access

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'access'
ORDER  BY ordinal_position;

Access Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘access_id_seq’::regclass)	NO
2	user	integer		YES
3	administration	integer		YES

Organisation

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'organisation'
ORDER  BY ordinal_position;

Organisation Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘organisation_id_seq’::regclass)	NO
2	name	character varying		YES
3	type	USER-DEFINED		YES
4	created	timestamp without time zone		YES

Form

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'form'
ORDER  BY ordinal_position;

Form Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘form_id_seq’::regclass)	NO
2	name	character varying		YES
3	description	text		YES
4	default_language	character varying		YES
5	languages	ARRAY		YES
6	translations	ARRAY		YES
7	version	double precision		YES

Question Group

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'question_group'
ORDER  BY ordinal_position;

Question Group Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘question_group_id_seq’::regclass)	NO
2	order	integer		YES
3	name	character varying		YES
4	form	integer		YES
5	description	text		YES
6	repeatable	boolean	false	YES
7	repeat_text	character varying		YES
8	translations	ARRAY		YES

Question

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'question'
ORDER  BY ordinal_position;

Question Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘question_id_seq’::regclass)	NO
2	order	integer		YES
3	name	character varying		YES
4	form	integer		YES
5	meta	boolean		NO
6	type	USER-DEFINED		YES
7	question_group	integer		YES
8	required	boolean	true	NO
9	rule	jsonb		YES
10	dependency	ARRAY		YES
11	tooltip	jsonb		YES
12	translations	ARRAY		YES
13	api	jsonb		YES
14	addons	jsonb		YES

Question Option

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'option'
ORDER  BY ordinal_position;

Question Option Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘option_id_seq’::regclass)	NO
2	order	integer		YES
3	name	character varying		YES
4	question	integer		YES
5	color	character varying		YES
6	score	integer		YES
7	code	character varying		YES
8	translations	ARRAY		YES

Data

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'data'
ORDER  BY ordinal_position;

Data Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘data_id_seq’::regclass)	NO
2	name	character varying		YES
3	form	integer		YES
4	administration	integer		YES
5	geo	ARRAY		YES
6	created_by	integer		YES
7	updated_by	integer		YES
8	created	timestamp without time zone	CURRENT_TIMESTAMP	YES
9	updated	timestamp without time zone		YES

Data Answer

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'answer'
ORDER  BY ordinal_position;

Data Answer Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘answer_id_seq’::regclass)	NO
2	question	integer		YES
3	data	integer		YES
4	value	double precision		YES
5	text	text		YES
6	options	ARRAY		YES
7	created_by	integer		YES
8	updated_by	integer		YES
9	created	timestamp without time zone	CURRENT_TIMESTAMP	YES
10	updated	timestamp without time zone		YES

Data History

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'history'
ORDER  BY ordinal_position;

Data Answer Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘history_id_seq’::regclass)	NO
2	question	integer		YES
3	data	integer		YES
4	value	double precision		YES
5	text	text		YES
6	options	ARRAY		YES
7	created_by	integer		YES
8	updated_by	integer		YES
9	created	timestamp without time zone	CURRENT_TIMESTAMP	YES
10	updated	timestamp without time zone		YES

Jobs

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'jobs'
ORDER  BY ordinal_position;

Jobs Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘jobs_id_seq’::regclass)	NO
2	type	USER-DEFINED		YES
3	status	USER-DEFINED	‘pending’::jobstatus	YES
4	payload	text		NO
5	info	jsonb		YES
6	attempt	integer	0	YES
7	created_by	integer		NO
8	created	timestamp without time zone	CURRENT_TIMESTAMP	YES
9	available	timestamp without time zone		YES

Logs

SELECT ordinal_position as pos, column_name, data_type, column_default, is_nullable
FROM   information_schema.columns
WHERE  table_name = 'log'
ORDER  BY ordinal_position;

Log Table

pos	column_name	data_type	column_default	is_nullable
1	id	integer	nextval(‘log_id_seq’::regclass)	NO
2	user	integer		YES
3	message	text		YES
4	at	timestamp without time zone	CURRENT_TIMESTAMP	YES
5	jobs	integer		YES