Skip to content

Database Description

Introduction

This document is a guide for those interested in writing queries for BrainFrame's SQL database. Included are examples of common queries and an explanation of each table and their columns. This document is intended for those with a basic understanding of SQL.

BrainFrame hosts Postgres in a container and makes it available to the host machine through the default port, 5432.

Relationship Diagram

The following is a visual of how BrainFrame's various tables relate to each other. This is a useful reference when writing queries that span multiple tables. Click the image to enlarge it.

This image is generated at release-time. If it isn't here, please contact Aotu.

Example Queries

Below are some queries intended to be used as examples for common tasks. Fields that must be filled in are wrapped in brackets.

Getting the number of detections right now in a zone for a class

This query finds the number of detections that are currently in a zone, filtered by a class. If you want to know how many people are currently in the "Couch Area" zone, for instance, this is the query to use.

SELECT COUNT(*) FROM detection
    JOIN detection_zone_status ON detection.id = detection_zone_status.detection_id
    WHERE detection_zone_status.zone_status_id=(SELECT id FROM zone_status 
        WHERE zone_status.zone_id=[your zone_id here]
            ORDER BY zone_status.tstamp DESC LIMIT 1)
    AND detection.class_name=[class_name];

Getting the traffic history of a zone

This query gets cumulative data on how many objects of the given class name have entered and exited the zone. This could be used to build a graph of traffic in the zone.

SELECT total_count.count_enter, total_count.count_exit FROM total_count
    JOIN zone_status ON zone_status.id=total_count.zone_status_id
    WHERE zone_status.zone_id=[your zone_id here]
    AND total_count.class_name=[your class name here];

Getting the last zone that an identity was seen in

This query finds the last zone that an identity was found in.

SELECT * FROM zone
    JOIN zone_status ON zone_status.zone_id = zone.id
    JOIN detection_zone_status ON detection_zone_status.zone_status_id = zone_status.id
    JOIN detection ON detection.id=detection_zone_status.detection_id
    JOIN identity ON identity.id=detection.identity_id
    WHERE identity.unique_name = [your unique name here]
    ORDER BY zone_status.tstamp DESC
    LIMIT 1;

Getting the number of times a zone alarm has been triggered

This query counts the total amount of times a zone alarm has been triggered given its' alarm ID.

SELECT COUNT(*) FROM alert WHERE alert.zone_alarm_id = 1

Get the number of people entering or exiting a specific zone, with timestamps

This will return the rows for the following columns: tstamp, count_enter, count_exit

SELECT zone_status.tstamp, total_count.count_enter, total_count.count_exit
FROM total_count
      LEFT JOIN zone_status ON zone_status.id = total_count.zone_status_id
WHERE zone_status.zone_id =
     (SELECT zone.id FROM zone WHERE zone.name = 'YOUR_ZONE_NAME_HERE')
 AND total_count.class_name = 'person'
ORDER BY zone_status.tstamp;

Get the total number of entering and exiting detections of a specific class for all time for a zone

SELECT total_count.count_enter FROM total_count
    JOIN zone_status ON zone_status.id=total_count.zone_status_id
    WHERE zone_status.zone_id=[your zone id here]
    AND total_count.class_name=[your class name here]
    ORDER BY zone_status.tstamp DESC LIMIT 1;

Tables: For Analysis

zone_status

This is an important table for SQL queries. It holds a point in time for a specific stream. The tstamp and zone_id are the key to finding specific detection in a certain place at a certain time.

Column Description
id A unique identifier.
zone_id The zone that this status is for. There is also a zone_id field that is the ID of this zone.
tstamp The Unix timestamp of when this status was recorded.

detection

An object that has been detected in a video stream.

Column Description
parent_id A parent detection, if any. For instance, a face detection might have a parent that is a person detection.
class_name The class name of the detection. it describes what the detection is. Ie, "person", "cat", or "dog"
identity_id The identity that this detection is recognized as, if any. For example, if class_name is "face" and there is a face recognition plugin, and that plugin recognized the detection as someone known, it will be attached with an identity.
extra_data_json This is a json of form {'key': VAL, 'key2': 'VAL'} where the values can be of any json encodable type. It is intended to carry plugin-specific and/or customer-specific information without tying it too closely to the brainframe product.
coords_json A JSON-encoded array of arrays specifying where in the frame the detection is. In the format: [[x1,y1], …]
track_id A nullable UUID string. Detections that have the same track_id refer to the same object according to the tracking algorithm being used. This can be used to find the path of a single object throughout a video stream. If null, then the detection has not been successfully tracked.

identity

A table for storing a known specific person or object, that other tables can link information about.

Column Description
id A unique identifier.
unique_name Some uniquely identifying string of the object, like an employee number or an SSN.
nickname A display name for the identity which may not be unique, like a person’s name.
metadata_json Any additional user-defined information about the identity.

alert

An alert that tells the user an alarm's condition has been met.

Column Description
id A unique identifier.
zone_alarm_id The alarm this alert came from.
start_time The Unix timestamp of when this alarm started.
end_time The Unix timestamp of when this alarm ended. May be null if the alert is still ongoing.
verified_as If True, this alert was verified as legit. If False, the alert was a false alarm. If None, it hasn’t been verified yet.

total_count

The total number of a certain class of object that has entered or exited a zone at some time. There are zero or more of these per ZoneStatus.

Column Description
id A unique identifier.
zone_status_id The zone status that this total count is for.
class_name The name of the class of object that we're keeping count of.
count_enter The amount of objects that have "entered" the zone.
count_exit The amount of objects that have "exited" the zone.

Tables: For Configuration Storage

premises

This defines a physical area with an internal local network of some sort. This could be a Mall, an office building, a shop, etc. The idea of a Premises is to keep track of which local network a camera or edge device might be running in, in order to forward results through a gateway to a central cloud server.

Column Description
id A unique identifier.
name The human readable name for which this premises this table refers to.

stream_configuration

This defines a video stream and how BrainFrame should connect to it.

Column Description
id A unique identifier.
premises_id Nullable. If not null, it represents the premises for which this camera is streaming from.
name The name of the video stream as it appears to the user on the UI.
connection_type The type of connection being defined. This has to do with whether or not the video comes from a file, webcam, or IP camera.
connection_options_json A JSON object that contains configuration information about how to connect to the stream.
runtime_options_json A JSON object that contains configuration information which changes the runtime behavior of the stream.
metadata_json A JSON object that contains any additional information the user may want associated with this stream.

global_plugin_configuration

This table is automatically created when BrainFrame loads a plugin that didn’t exist before.

Column Description
name The (unique) name of the plugin that this configuration refers to.
option_values_json A json with the option values that this plugin exposes Format: { "option_key": "option_value", "other_option": 0.75 }
is_active The default value for this plugin (on or off). It is overridden by the stream_plugin_configuration if the value is not null.

stream_plugin_configuration

This table will be created when the a specific stream has options modified for a plugin. The table is intended to ‘patch’ an existing global_plugin_configuration to modify behavior of a plugin for a specific stream.

Column Description
global_configuration_name The global_plugin_configuration that this stream_plugin_configuration is patching
stream_id The stream_configuration that this stream_plugin_configuration is modifying plugin options for.
option_values_patch_json A json that can be empty, but also can modify the global plugin configuration by simply having a key: modified_value pair. {} or { "option_key": "modified option value" }
is_active Overrides the global_plugin_configuration is_active value if this value is not null. That means that, if is_active is True on the stream_plugin_configuration, then the global_plugin_configuration is ignored. If is_active is null on the stream_plugin_configuration, then the global_plugin_configuration is used.

attribute

An Attribute refers to classifications, and are used to describe detections. For example, there may be a category of classification such as "gender". A particular detection might have an attribute with category "gender" and value "male".

Column Description
category The category of attribute. ("gender", "car_type", etc). This attribute is a key.
value The value of the attribute. ("male", "prius", etc). This attribute is a key.

zone

A space in a video stream to look for activity in.

Column Description
id A unique identifier
name The name of the zone as it appears to the user.
stream_id The ID of the stream that this zone is for.
coords_json Two or more 2D coordinates defining the shape of the zone in the stream. Defined as a two-dimensional JSON array, or "null" if the zone applies to the entire frame.

zone_alarm

Defines a set of conditions that, if they take place in a zone, should trigger an alarm to the user.

Column Description
id A unique identifier
name The name of the alarm as it appears to the user.
use_active_time If true, then alarms only happen between start_time and end_time. If false, then they can happen at any time.
active_start_time The time to start monitoring the stream at every day. Only used if use_active_time is true. Stored in the format "HH:MM:SS".
active_end_time The time to start monitoring the stream at every day. Only used if use_active_time is true. Stored in the format "HH:MM:SS".
zone_id The zone that this alarm is assigned to watch. There is also a zone_id field that is the ID of this zone.

zone_alarm_count_condition

A condition that must be met for an alarm to go off. Compares how many of some object is in a zone against a test value.

Column Description
id A unique identifier
zone_alarm_id The zone alarm that this condition applies to.
test The test condition, either ">", "<", "=", "!=".
check_value The value to apply the test condition to.
with_class_name The name of the class to count in the zone.
attribute_id An optional attribute that the object must have to be counted. (nullable)
window_duration The size of the sliding window used for this condition. A larger sliding window size may reduce false positives but increase latency.
window_threshold A value between 0.0 and 1.0 that controls what portion of the sliding window results must evaluate to true for the alarm to trigger.
intersection_point The point on the detection to use when calculating if the detection is in the zone. Either "bottom", "top", "left", "right", or "center".

zone_alarm_rate_condition

A condition that must be met for an alarm to go off. Compares the rate of change in the count of some object against a test value.

Column Description
id A unique identifier
zone_alarm_id The zone alarm that this condition applies to.
test The test condition, either '>=' or '<='.
duration The time period with which the change in object count happens, in seconds.
change The change in object count that happens within a period of time.
direction The direction of movement, either 'entering' the zone, 'exiting' the zone, or 'entering_or_exiting'.
with_class_name The name of the class of objects to look for in the zone.
attribute_id An optional attribute that the object must have to be counted.
intersection_point The point on the detection to use when calculating if the detection is in the zone. Either "bottom", "top", "left", "right", or "center".

encoding

A vector encoding of some data that defines an identity. For example, an encoding for a human face that can be compared to other encodings to identify if it is the same human face.

Column Description
id A unique identifier
identity_id The identity that this encoding describes
class_name The name of the class that this encoding is of.
vector_json A JSON-encoded array of values. The amount of values will depend on the class name of the identity this encoding is attached to.

Tables: For Linking

alert_frame

Links an alert to a data_storage table containing the first frame in the video where this alert happened.

Column Description
id A unique identifier
alert_id The alert this frame is for.
data_storage_id The data_storage table that contains the frame.

zone_status_alert

Links a zone_status to an alert that was in progress at the time of the zone_status.

Column Description
zone_status_id The zone_status being linked to.
alert_id The alert being linked to.

detection_zone_status

Links zone statuses to the detections that happened in them.

Column Description
detection_id The linked detection.
zone_status_id The linked zone_status.
transition_state The location of the detection relative to the zone

detection_attribute

Links detections to the attributes that describe them.

Column Description
detection_id The linked detection.
attribute_id The linked attribute.

encoding_data_storage

Links encodings to the data that was used to create the vector. This tends to be an image.

Column Description
data_storage_id The linked data_storage
encoding_id The linked encoding

Tables: Miscellaneous

data_storage

References some external file found elsewhere.

Column Description
id A unique identifier.
name The name of the file, used to find it in storage.
hash A SHA256 hash of the data.
mime_type The mime type of the file being stored.

user

Contains information on user accounts.

Column Description
id A unique identifier.
username The user's unique username.
password_hash The user's password, hashed with argon2.
role The user's role, which controls what permissions they have.