VarAC Sqlite database structure (for developers)

Since VarAC V5, VarAC has a Sqlite database as its main data engine.

This allows developers to integrate in a super flexible, SQL based way to the VarAC traffic for various types of integrations.

Unlike APIs that are limited to a particular use case, accessing directly the VarAC 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 VarAC Sqlite DB can be found in the VarAC directory under the name: VarAC.db

By design, the VarAC Sqlite database is not protected by a password or encryption to allow anyone to integrate with its content.

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 VarAC instances in a VarAC 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 VarAC 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 VarAC 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

Comments (4)

W5DMH Dan

Mar 09, 2023

Thank you for taking the time to document the db structure! Hopefully there will be an API soon that will let us develop applications that can transmit as well as use the received data. I developed a form based application for JS8Call (old video, it has since been updated a lot) and I would like to something similar with VarAC

Silas Moeckel

Mar 07, 2023

These all look to be read only. I'm assuming I can not pop in a beacon recond and expect it to transmit.

irad d

Replying to

This is for consuming data for now

Thats unfortunate, sounds like I'll have to deal with varahf kiss including ptt to send a beacon then. Can processed the received ones via this interface.