Kronekeeper Logo
A web based application for recording and managing Krone frame wiring records, able to import existing KRIS databases.

Database Schema

This page documents the database schema used by Kronekeeper.

This schema uses a high degree of abstraction. It is more complicated than the KRIS database it seeks to replace, however that complexity allows it to provide more flexibility, overcome earlier shortcomings and adapt to differences in working practices between organisations. In general, interaction with the database is via stored procedures, which hides much of this low-level detail.

Tables

Housekeeping

account
field namenotes
idautoincrement
namee.g. Global Radio Technology
person
field namenotes
account_idReferences: account.id
emailUsed as login name
namee.g. James Taylor
passwordRFC2307 hash
role
field namenotes
idautoincrement
roletext code name for role, e.g. edit, import, view_activity_log
rankInteger. Higher number is more senior
colour
field namenotes
idautoincrement
namee.g. Red, Blue, Green
short_namee.g. R, Blu, Gn
html_codee.g. ff0000, 0000ff, 00ff00
contrasting_html_codee.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 namenotes
idautoincrement
namee.g. 'Analogue Audio LEFT'
designatione.g. 'L'
account_idReferences: account.id

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

jumper_template_wire
field namenotes
idautoincrement
jumper_template_idReferences: jumper_template.id
position1, 2, 3
colour_idReferences: colour.id
block_type
field namenotes
idautoincrement
account_idReferences: account.id
namee.g. '237A', 'EARTH', 'ABS'
colour_html_codee.g. ff0000, 0000ff, 00ff00
circuit_counte.g. 10 for a standard 10-pair block
circuit_pin_counte.g. 2 for standard pairs

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

import
field namenotes
idautoincrement
account_idReferences: account.id

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

Permanent Wiring

frame
field namenotes
idautoincrement
account_idReferences: account.id
namee.g. 'Studio 3U'
vertical
field namenotes
idautoincrement
frame_idReferences frame.id
positioncounts from 1, left-to-right; contiguous sequence
designationtypically 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 namenotes
idautoincrement
vertical_idReferences vertical.id
positioncounts from 1, bottom-to-top; contiguous sequence
designationtypically 01, 02, 03 etc... Stored as a free text field, but must be numeric for standard designation nomenclature to work.
namee.g. 'CTA Ties'
block_type_idReferences: block_type.id
colour_html_codeCan be NULL. Overrides colour set by block type. e.g. ff0000, 0000ff, 00ff00
circuit
field namenotes
idautoincrement
block_idReferences block.id
positioncounts from 1, left-to-right for Krones; contiguous sequence
designationtypically 1,2,3,4,5,6,7,8,9,0
namee.g. 'Off-Air Monitor', 'Mic Live Tally' etc
cable_referenceFree text, typically used for cable number or cable type. Replicates KRIS cable field
connectionFree text, replicates KRIS connection field e.g. 'RX Out', 'Desk Monitor In', 'pins 1/13' etc)
noteFree text
pin
field namenotes
idautoincrement
circuit_idReferences circuit.id
positioncounts from 1, left-to-right for Krones; contiguous sequence
designationtypically a,b,s
nametypically
wire_referencefree text, typically permanent wire colour

Jumpering

jumper
field namenotes
idautoincrement
jumper_wire
field namenotes
idautoincrement
jumper_idReferences jumper.id
colour_idReferences colour.id
connection
field namenotes
idautoincrement
jumper_wire_idReferences jumper_wire.id
pin_idReferences 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 namenotes
idautoincrement
account_idReferences: account.id
kris_wiretype_idReferenced by KRIS Jumpers.Wire field
namee.g. 'White/Red (Mono)'
a_wire_colour_codee.g. ffffff
b_wire_colour_codee.g. ff0000
jumper_template_idReferences 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 namenotes
idautoincrement
log_timestampTimestamp with timezone
by_person_idReferences person.id
account_idReferences account.id
frame_idReferences frame.id
functionText. Records the internal function name triggering the logged activity
noteText. Human readable description of the activity
block_id_areferences 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_areferences 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_idReferences person.id
commentOptional text comment on this log entry
completed_by_person_idReferences person.id
jumper_idReferences jumper.id, but this foreign key is not enforced as the block can be deleted, but we want the activity log entry to persist