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.
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 capsule, and that capsule 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 capsule-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. |
capsule¶
A capsule loaded through the REST API.
| Column | Description |
|---|---|
| name | The unique name of the capsule |
| data_storage_id | The data storage row that holds the capsule data |
| source_path | Path to the capsule's source code on the developer machine, or null if no source is available |
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_capsule_configuration¶
This table is automatically created when BrainFrame loads a capsule that didn’t exist before.
| Column | Description |
|---|---|
| name | The (unique) name of the capsule that this configuration refers to. |
| option_values_json | A json with the option values that this capsule exposes Format: { "option_key": "option_value", "other_option": 0.75 } |
| is_active | The default value for this capsule (on or off). It is overridden by the stream_capsule_configuration if the value is not null. |
stream_capsule_configuration¶
This table will be created when the a specific stream has options modified for a capsule. The table is intended to ‘patch’ an existing global_capsule_configuration to modify behavior of a capsule for a specific stream.
| Column | Description |
|---|---|
| global_configuration_name | The global_capsule_configuration that this stream_capsule_configuration is patching |
| stream_id | The stream_configuration that this stream_capsule_configuration is modifying capsule options for. |
| option_values_patch_json | A json that can be empty, but also can modify the global capsule configuration by simply having a key: modified_value pair. {} or { "option_key": "modified option value" } |
| is_active | Overrides the global_capsule_configuration is_active value if this value is not null. That means that, if is_active is True on the stream_capsule_configuration, then the global_capsule_configuration is ignored. If is_active is null on the stream_capsule_configuration, then the global_capsule_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. |