SafecomLink Sqlite database structure (for developers)
Since SafecomLink V5, SafecomLink has a Sqlite database as its main data engine.
This allows developers to integrate in a super flexible, SQL based way to the SafecomLink traffic for various types of integrations.
Unlike APIs that are limited to a particular use case, accessing directly the SafecomLink database provides unlimited options for integrations.
To allow “track changes” data reading, each table has 2 identifiers for every row that you can use:
A sequential ID – which can be used as a high watermark
A unique UUID based identifier.
The SafecomLink Sqlite DB can be found in the SafecomLink directory under the name: SafecomLink.db
By design, the SafecomLink Sqlite database is not protected by a password or encryption to customers decide on their own protection mechanism.
alert
Description: storing all broadcasts (incoming & outgoing)
Columns:
id (INTEGER) - Auto increment ID
guid (TEXT) - unique row identifier
source_id (INTEGER) - where this alert originated from (see alert_source lookup table)
from_callsign (TEXT)
to_callsign (TEXT)
alert_tag (TEXT) - the alert tag (text) that triggered the alert.
source_text (TEXT) - the full source text that includes the tag that triggered the alert.
alert_time (DATETIME) - time in which the alert was received.
read_status (BOOLEAN) - True if it was read already. False if not-read.
folder_id (INTEGER) - Where this alert is stored (Incoming / Archived folder)
frequency (INTEGER) – in Hz
is_deleted (BOOLEAN) - true if the alert was deleted.
Indexes: id, guid, (folder_id, alert_time), alert_tag, from_callsign, to_callsign
alert_source
Description: Lookup table for the "alert" table
Columns:
source_id (INTEGER)
source (TEXT) - the source of the alert (1-Beacon/2-CQ/3-Broadcast/4-Datastream)
Indexes: source_id
alert_folder
Description: Lookup table for the "alert" table
Columns:
folder_id (INTEGER)
folder (TEXT) - the status of the alert (Incoming/Archived)
Indexes: folder_id
broadcast
Description: storing all broadcasts (incoming & outgoing)
Columns:
id (INTEGER) - Auto increment ID
guid (TEXT) - unique row identifier
broadcast_time (DATETIME) – in UTC
frequency (INTEGER) – in Hz
from_callsign (TEXT)
to_callsign (TEXT)
via_callsign (TEXT) – digipeater (one or more)
broadcast_message (TEXT) – The actual broadcast message
snr (INTEGER) – the SNR of received broadcasts.
Indexes: id, guid, broadcast_time, from_callsign, to_callsign
contact
Description: All callsigns you made a QSO with or entered manually
Columns:
id (NTEGER) - Auto increment ID
guid (TEXT) - unique row identifier
callsign (TEXT)
name (TEXT)
qth (TEXT)
comments (TEXT) - additional comments made by you for that contact
rig (TEXT)
time_added (DATETIME) - When this contact was first added to the contacts list
favorite (BOOLEAN) - if this contact is a favorite one
is_deleted (BOOLEAN) - deletion status of this contact
Indexes: id, guid, starttime, callsign
cqframe
Description: Storing all received Beacons and CQ
Columns:
id (INTEGER) - Auto increment ID
guid (TEXT) - unique row identifier
cqframe_time (DATETIME) – in UTC
cqframe_type_id (INTEGER) – Reference to the table "cqframe_type"
frequency (INTEGER) – in Hz
bandwidth (TEXT) - 500 or 2300
from_callsign (TEXT)
snr (INTEGER) – the SNR of received cqframe.
slot (INTEGER) – slot id where the cqframe was received
data (TEXT) - used for additional data. In CQ for example it can be a special CQ (POTA/SOTA...)
locator (TEXT) - in special CQs a locator is also sent
is_emcomm (BOOLEAN) - weather the beacon came from an EmComm station.
Indexes: id, guid, cqframe_time, from_callsign
cqframe_type
Description: Lookup table of the "cqframe" table. identifying a cqframe as either CQ (1) or Beacon (2)
Columns:
cqframe_type_id (INTEGER)
cqframe_type (TEXT)
Indexes: cqframe_type_id
datastream_entry_type
Description: Lookup table of the "datastream" table. identifying a type of an entry.
1 - Incoming message
2 - Outgoing message
3 - System message
Columns:
datastream_entry_type_id (INTEGER)
datastream_entry_type (TEXT)
Indexes: datastream_entry_type_id
datastream
Description: Contains all the datastream entries including chat message and system messages.
Columns:
id (INTEGER) - unique sequential row identifier
guid (TEXT) - unique row identifier (guid)
datastream_entry_type_id (INTEGER) - type of record as described in datastream_entry_type table
qso_guid (TEXT) - the QSO identifier (from the qso table) that this entry belong to
callsign (TEXT) - the callsign who wrote this entry
entry (TEXT) - the message it self (chat or info message)
file_path (TEXT) - if this message contains a file (file transfer) - the local path of this file
chat_id (INTEGER) - sequentia number of the message in this QSO
reply_on_chat_id (INTEGER) - a chat ID for which this message is a reply to
creation_time (DATETIME) - when this message was received
is_deleted (BOOLEAN) - deletion status of this record.
Indexes: id, guid, (callsign, creation_time), creation_time
instance
Description: List of SafecomLink instances in a SafecomLink cluster.
Columns:
id (INTEGER) - unique instance ID
guid (TEXT) - unique row identifier
name (TEXT) - instance name
comments (TEXT) - additional info
busy (BOOLEAN) - set to True if the instance is currently_busy
busy_last_time(DATETIME) - last time this instance was busy
last_keepalive_time (DATETIME) - last time the instance was seen alive in the cluster
creation_time (DATETIME) - when this row was created for the first time
is_deleted (BOOLEAN) - if the instance was deleted or not
Indexes: id, guid, busy_last_time, last_keepalive_time
parameter
Description: Global SafecomLink parameters. currently hold only one parameter which is the sqlite structure version.
Columns:
parameter_id (INTEGER)
parameter_name (TEXT)
parameter_value (TEXT)
Indexes: parameter_id
qso
Description: All Valid QSOs are stored here. It's a mirror of ADIF data plus additional information.
Columns:
id (INTEGER) - Auto increment ID
guid (TEXT) - unique row identifier
vara_modem_type (TEXT) - What modem was used during the QSO (VARA HF/FM/SAT)
mode (TEXT) - the ADIF mode that was reported (usually DYNAMIC)
submode (TEXT) - the ADIF submode that was reported (VARA HF/FM/SAT)
starttime (DATETIME) - QSO start time (date and time)
endtime (DATETIME) - QSO end time (date and time)
frequency (INTEGER) - in Hz
band (TEXT) - band based on the frequency (ex. 20m, 40m...)
bandwidth (TEXT) - 500 or 2300
callsign (TEXT) - the callsign of the station you connected with
my_callsign (TEXT) - your callsign at the time of the QSO
digipeater (TEXT) - if a digipeater was used - it will be logged here
snr_received (INTEGER) - received RST
snr_sent (INTEGER) - sent RST
name (TEXT) - operator name
qth (TEXT) - operator QTH
my_power (INTEGER) - based on the value at your profile at the time of the QSO
my_rig (INTEGER) - based on the value at your profile at the time of the QSO
my_antenna (INTEGER) - based on the value at your profile at the time of the QSO
comments (TEXT) - additional comments
varac_version (TEXT) - the SafecomLink version at the time of the QSO
is_ping (BOOLEAN) - if this QSO was a ping (obsolete since V7 Pings are not listed as valid QSOs)
is_deleted (BOOLEAN) - deletion status of this record.
slot (TEXT) - slot number on which this QSO took place
Indexes: id, guid, starttime, (callsign,starttime)
vmail
Description: Store all Vmails (incoming / outgoing / sent / parking)
Columns:
id (INTEGER) - Auto increment ID
guid (TEXT) - unique row identifier
creation_time (DATETIME) – in UTC
sent_time (DATETIME) – in UTC
received_time (DATETIME) – in UTC
folder_id (INTEGER) - reference to the "vmail_folder" table. Says if it is an inbox/outbox/parked/sent vmail.
vmail_to (TEXT) - the callsign of the vmail destination
vmail_from (TEXT)- the callsign of the vmail source
vmail_via (TEXT) - the callsign of the vmail intermediate station
delivery_band (TEXT) - Band in which the Vmail was received
delivery_snr (TEXT) - SNR in which the Vmail was received
subject (TEXT) - Vmail subject
msg (TEXT) - Vmail body
read_status (BOOLEAN) - True if it was read already. False if not-read.
is_deleted (BOOLEAN) - if the vmail was deleted or not
frequency (INTEGER) – in Hz - on which frequency the VMail was received
Indexes: id, guid, (vmail_to,folder_id), (folder_id,read_status,is_deleted)
vmail_folder
Description: Lookup table for the "vmail" table
Columns:
folder_id (INTEGER)
folder (TEXT) - the folder name (Inbox/Sent/Outbox/Parking)
Indexes: folder_id
vmail_relay_notification
Description: Storing all relay notifications - which are indications received from other stations regarding parked Vmails that awaits for your retrieval.
Columns:
id (INTEGER) - Auto increment ID
guid (TEXT) - unique row identifier
relay_notification_time (DATETIME) – in UTC
frequency (INTEGER) – in Hz
from_callsign (TEXT)
is_deleted (INTEGER) – A boolean (1/0) field - saying if you already deleted the notification or not.
Indexes: id, guid, from_callsign, is_deleted