Tables

Housekeeping

account

field name	notes
id	autoincrement
name	e.g. Global Radio Technology

person

field name	notes
account_id	References: account.id
email	Used as login name
name	e.g. James Taylor
password	RFC2307 hash

role

field name	notes
id	autoincrement
role	text code name for role, e.g. edit, import, view_activity_log
rank	Integer. Higher number is more senior

colour

field name	notes
id	autoincrement
name	e.g. Red, Blue, Green
short_name	e.g. R, Blu, Gn
html_code	e.g. ff0000, 0000ff, 00ff00
contrasting_html_code	e.g. ffffff Used for text/legends displayed over the jumper colour

Colour definitions are system-wide and affect all accounts and users.

jumper_template

field name	notes
id	autoincrement
name	e.g. 'Analogue Audio LEFT'
designation	e.g. 'L'
account_id	References: account.id

Jumper templates are specific to an account and are shared by all users of that account.

jumper_template_wire

field name	notes
id	autoincrement
jumper_template_id	References: jumper_template.id
position	1, 2, 3
colour_id	References: colour.id

block_type

field name	notes
id	autoincrement
account_id	References: account.id
name	e.g. '237A', 'EARTH', 'ABS'
colour_html_code	e.g. ff0000, 0000ff, 00ff00
circuit_count	e.g. 10 for a standard 10-pair block
circuit_pin_count	e.g. 2 for standard pairs

Block types are specific to an account and are shared by all users of that account.

import

field name	notes
id	autoincrement
account_id	References: account.id

Import jobs are specific to an account and are shared by all users of that account.

Permanent Wiring

frame

field name	notes
id	autoincrement
account_id	References: account.id
name	e.g. 'Studio 3U'
is_template	Boolean. Template frames, complete with blocks and jumpering, can be copied into another frame
is_deleted	Boolean. Frames marked deleted are not shown via the user interface. This can be used to implement a "soft-delete" before completely purging the frame data.

vertical

field name	notes
id	autoincrement
frame_id	References frame.id
position	counts from 1, left-to-right; contiguous sequence
designation	typically A, B, C etc... Free text field, but must use word characters and end in a letter for standard designtion nomenclature to work.

block

field name	notes
id	autoincrement
vertical_id	References vertical.id
position	counts from 1, bottom-to-top; contiguous sequence
designation	typically 01, 02, 03 etc... Stored as a free text field, but must be numeric for standard designation nomenclature to work.
name	e.g. 'CTA Ties'
block_type_id	References: block_type.id
colour_html_code	Can be NULL. Overrides colour set by block type. e.g. ff0000, 0000ff, 00ff00
is_active	Boolean. Blocks can only be placed in this position if TRUE

The UI expects that every vertical in the frame has the same number of blocks and that within each frame, the same designation will be used for corresponding block positions in different verticals.

circuit

field name	notes
id	autoincrement
block_id	References block.id
position	counts from 1, left-to-right for Krones; contiguous sequence
designation	typically 1,2,3,4,5,6,7,8,9,0
name	e.g. 'Off-Air Monitor', 'Mic Live Tally' etc
cable_reference	Free text, typically used for cable number or cable type. Replicates KRIS cable field
connection	Free text, replicates KRIS connection field e.g. 'RX Out', 'Desk Monitor In', 'pins 1/13' etc)
note	Free text

pin

field name	notes
id	autoincrement
circuit_id	References circuit.id
position	counts from 1, left-to-right for Krones; contiguous sequence
designation	typically a,b,s
name	typically
wire_reference	free text, typically permanent wire colour

Jumpering

jumper

field name	notes
id	autoincrement

jumper_wire

field name	notes
id	autoincrement
jumper_id	References jumper.id
colour_id	References colour.id

connection

field name	notes
id	autoincrement
jumper_wire_id	References jumper_wire.id
pin_id	References pin.id

KRIS Import

These tables are used only when importing data files from legacy KRIS software. They are namespaced within their own 'kris' schema.

kris.jumper_type

field name	notes
id	autoincrement
account_id	References: account.id
kris_wiretype_id	Referenced by KRIS Jumpers.Wire field
name	e.g. 'White/Red (Mono)'
a_wire_colour_code	e.g. ffffff
b_wire_colour_code	e.g. ff0000
jumper_template_id	References jumper_template.id

Kris jumpers are always a two-wire pair. This table maps wire type ids within a KRIS Jumpers table to Kronekeeper jumper templates. The mapping is applied during the import process, so changing this table does not alter previously imported data.

Activity Log

activity_log

field name	notes
id	autoincrement
log_timestamp	Timestamp with timezone
by_person_id	References person.id
account_id	References account.id
frame_id	References frame.id
function	Text. Records the internal function name triggering the logged activity
note	Text. Human readable description of the activity
block_id_a	references block.id, but this foreign key is not enforced as the block can be deleted, but we want the activity log entry to persist
circuit_id_a	references circuit.id, but this foreign key is not enforced as the block can be deleted, but we want the activity log entry to persist
to_person_id	References person.id
comment	Optional text comment on this log entry
completed_by_person_id	References person.id
jumper_id	References jumper.id, but this foreign key is not enforced as the block can be deleted, but we want the activity log entry to persist