"signals" schema (for motion detection and such)

[scottlamb]

There needs to be some schema for motion detection events, and ideally some similar things like events from my Elk alarm system. I think it'd just say at this wall time, the signal is "on", "off", or "unknown", with support for long-lived and momentary on events. If the system's wall clock is suspect, the data is useless, and that's okay.

It could be used:

• directly by something that does on-camera motion detection with ONVIF
• directly by something that manipulates events with a JSON interface, such as the Elk alarm watcher.
• indirectly by something that does on-NVR motion detection. I think this would just be part of the schema. At least it needs some additional configuration (mask, sensitivity) and state (last recording processed, current reference frame). It might also track motion in a grid on a recording-by-recording basis so you could recompute the mask or something.

Here's my first idea of schema.

create table signal (
  id integer primary key,

  uuid blob primary key check (length(uuid) = 16),

  -- a uuid describing the originating object, such as the uuid of the camera
  -- for built-in motion detection. There will be a JSON interface for adding
  -- events; it will require this UUID to be supplied. An external uuid might
  -- indicate "my house security system's zone 23".
  source_uuid blob not null check (length(uuid) = 16),

  -- a uuid describing the type of event. A registry (TBD) will list built-in
  -- supported types, such as "Hikvision on-camera motion detection", or
  -- "ONVIF on-camera motion detection". External programs can use their own
  -- uuids, such as "Elk security system watcher".
  type_uuid blob not null check (length(uuid) = 16),

  -- a short human-readable description of the event to use in mouseovers or event
  -- lists, such as "driveway motion" or "front door open".
  short_name not null,

  unique (source_uuid, type_uuid)
);

-- Associations between event sources and cameras.
-- For example, if two cameras have overlapping fields of view, they might be
-- configured such that each camera is associated with both its own motion and
-- the other camera's motion.
create table signal_camera (
  signal_id integer references signal (id),
  camera_id integer references camera (id),

  -- type:
  --
  -- 0 means direct association, as if the event source if the camera's own
  -- motion detection. Here are a couple ways this could be used:
  --
  -- * when viewing the camera, hotkeys to go to the start of the next or
  --   previous event should respect this event.
  -- * a list of events might include the recordings associated with the
  --   camera in the same timespan.
  --
  -- 1 means indirect association. A screen associated with the camera should
  -- given some indication of this event, but there should be no assumption
  -- that the camera will have a direct view of the event. For example, all
  -- cameras might be indirectly associated with a doorknob press. Cameras at
  -- the back of the house shouldn't be expected to have a direct view of this
  -- event, but motion events shortly afterward might warrant extra scrutiny.
  type integer not null,

  primary key (signal_id, camera_id)
) without rowid;

-- State of signals as of a given timestamp.
create table signal_state (
  -- seconds since 1970-01-01 00:00:00 UTC.
  time_sec integer primary key,

  -- Changes at this timestamp, if any.
  --
  -- It's possible for a single signal to be in both newly_active and either
  -- newly_inactive or newly_unknown; this means that the signal is
  -- momentarily active.
  --
  -- The blobs each encode a list of signal ids.
  -- They do so as delta-encoded varints. For example,
  -- input signals: 1    3    200 (must be sorted)
  -- deltas:        1    2    197
  -- varint:        \x01 \x02 0xc5 0x01
  newly_active blob,
  newly_inactive blob,
  newly_unknown blob,
);

On startup, we'd do a sequential scan of the whole thing and compute the days map. Need to ensure this doesn't make startup time unreasonable. If it's too bad, we'll need to store the days map instead, which means ensuring the timezone information doesn't change from run to run, which may not be easy to do. (We'd also have to rows indicating the full state of all signals at some interval, but that's relatively easy.) Or we could switch to a faster database (#44).

I put together all the changes at a single timestamp in one row because I'm imagining there being times when many/all signals change at once, such as on startup or shutdown. This would be more efficient in that scenario.

During normal operation, there should be a bounded amount of IO to do on this. The JSON API for manipulating this should probably let you change a signal's state for no more than a day at a time or some such.

There needs to be a frontend interface to make this useful. The simplest one would be to just show you a expandable thing for each event with all the recordings in it +/- a minute or something.

Related schema change: ideally you could annotate a region of video or an signal-on event or some such with a bit of text.

[clydebarrow]

I'm just about to delve into the signals stuff for recording on-camera motion detection (so I have something to show in the UI, apart from anything else.) The cameras I'm testing with mostly don't support HTTP notifications so I will have to use FTP. At a quick look this will involve setting up an FTP server, and a file watcher to gateway changes to the HTTP api, with some assumptions made about motion duration. Any other ideas?

An in-built FTP server in Moonfire would seem like a valuable feature.

[scottlamb]

What brand cameras are you using?

A built-in FTP server is an interesting idea I hadn't considered. How does the notification via HTTP FTP work? I was aware some cameras supported uploading a snapshot jpg or mp4 clip (after the fact) or some such. Is that what you're thinking of, or is there something else? Do they include any metadata? Would we interpret the filename in like a configurable/camera-brand-specific way?

[clydebarrow]

I have Hikvision and Anpviz, and a couple of other off-brand units. (If you have any good suggestions for other brands I'd love to hear them - I understand your concerns about the major Chinese brands, but I've struggled to find anything else.)

The FTP notification works by uploading an image to a designated FTP server/path. There would be little metadata available other than the time of the upload, but given the "always-recording" approach of Moonfire this isn't a big deal. It might be possible to distinguish types of events (motion, alarm input etc.) by the filename or path. Of the cameras I've seen you configure a path and/or a filename, and a username and password. The username could be used to identify the camera, and possibly the path or filename could distinguish event types.

[scottlamb]

The Hikvision ones—despite my ethical concerns—are technically pretty good. They support notification via either their proprietary API or ONVIF. ONVIF is tedious to deal with, but it's on my to-do list anyway. I don't know about Anpviz (which I'd never heard of prior to your bug report earlier) or the other brands.

I've really struggled to find a brand I can whole-heartedly recommend. So far Geovision is the best I've found. They're fairly reasonably priced, don't have the same ethical problems, and seem to have decent standards compliance (some bugs I can work around) and configurable streams. They don't have an inexpensive large sensor camera like Dahua's 1/1.8" models but oh well, I guess.

[clydebarrow]

Anpviz claim to be Hikvision compatible, and certainly you can plug them into a Hikvision NVR and they work just fine, including motion detection, so I would guess they emulate the proprietary APIs. The build quality is decent - the only major issue I've seen so far is lack of a way to disable the IR leds other than by physically disconnecting them.

Supporting ONVIF and/or the proprietary Hikvision notification API would be good, but the FTP support shouldn't be too hard, especially since there look to be FTP server implementations for Rust.

[clydebarrow]

I'm now getting notifications from cameras (via Hikvision Event stream) and want to insert signal records into the database. The POST api call requires signalIds: a list of signal ids to change. Must be sorted. - presumably these ids correspond to the ids of the signals in the api call. But this list is empty, and there is no API call to create them, nor any apparent UI in nvr config. Do I have to manually create database entries for now?

Clearly there needs to be some API endpoints to manage the signal list, and a UI to interact with those.

[clydebarrow]

There is a table in the DB signal_type_enum which has fields that are fairly self explanatory (though I wonder why some tables are related by id and others by uuid) but one column is not documented:

CREATE TABLE signal_type_enum (
  type_uuid blob not null check (length(type_uuid) = 16),
  value integer not null check (value > 0 and value < 16),
  name text not null,

  -- true/1 iff this signal value should be considered "motion" for directly associated cameras.
  motion int not null check (motion in (0, 1)) default 0,

  color text
);

What's the value column for?

[clydebarrow]

The api doc says that in the top level /api call the signal_types should have this format:

  "signalTypes": [
    {
      "uuid": "ee66270f-d9c6-4819-8b33-9720d4cbca6b",
      "states": {
        0: {
          "name": "unknown",
          "color": "#000000"
        },
        1: {
          "name": "off",
          "color": "#888888"
        },
        2: {
          "name": "on",
          "color": "#ff8888",
          "motion": true
        }
      }
    }
  ],

In fact what is being delivered is:

  "signalTypes": [
    {
      "uuid": "b5b0e204-dac5-46b8-9963-5b38b1d52bb7",
      "states": [
        {
          "value": 1,
          "name": "Motion",
          "motion": true,
          "color": "#ff9900"
        }
      ]
    }
  ]

i.e. states is being delivered as an array of objects instead of a map.

[clydebarrow]

Next question - to authenticate a POST request (e.g. to update a signal) is the only way to do a login and capture the session token?

Also, the GET /api/signals endpoint needs some filter parameters to limit which signals are returned.

[scottlamb]

Do I have to manually create database entries for now?

For now, yes. I think it shouldn't be hard to add the missing API endpoints. (Although I likely won't do any coding tomorrow. Jury duty caught up to me...)

What's the value column for?

The numeric value to use for this state. 0 is unknown; the values you choose should start at 1 and be dense, ideally with the most common states having the lowest numbers. (The in-memory representation of a signal-day uses an array of time in each state; if you have a value of 100, it will have 101 elements in it.)

Next question - to authenticate a POST request (e.g. to update a signal) is the only way to do a login and capture the session token?

Yes, for now it has to be by token, obtained by either the login HTTP endpoint or (when the system is down) by the nvr login subcommand. Eventually I'd like to also have a Unix-domain socket so local programs can authenticate by uid, as mentioned in #133.

[clydebarrow]

Yes, for now it has to be by token, obtained by either the login HTTP endpoint or (when the system is down) by the nvr login subcommand

Are the tokens permanent? Perhaps there should be a way of creating login tokens associated with the uuid for a signal source (camera UUID or UUID for some external device) through the API.

[scottlamb]

They have no fixed expiration time, so permanent unless you pass them to /api/logout.

Perhaps there should be a way of creating login tokens associated with the uuid for a signal source (camera UUID or UUID for some external device) through the API.

Yeah, maybe. I have the the permissions column in the session table that just has a couple booleans now but could be extended to scope a session to a specific signal or some such.

[clydebarrow]

I'm struggling a little to map the signals schema onto common use cases. Basically I've ended up doing this:

insert into signal select camera.id, camera.uuid,  signal_type_enum.type_uuid, camera.short_name || ' ' || signal_type_enum.name from camera left join signal_type_enum;
insert into signal_camera select id, id, 0 from camera;

(I only have one signal type defined right now, so the id is not duplicated.)

What this means is that there is now a signal defined for every camera for every signal type. At this point my gut feel is that rather than having a table to enumerate all permitted source/type combinations it would make more sense to just have a set of signal types defined (some predefined ones plus custom - so this is the existing signal_type_enum table) but allow any source (camera or another device) to announce signal changes as a (source, type, state, timestamp) tuple.

The signal_camera table potentially duplicates some information (presumably in the majority of cases a direct association implies that the associated camera is also the source) and I wonder if it is necessary, or whether selecting which signals to display at any given time could just be done in the UI. I don't have a clear position on that yet, probably will once I get further with the UI.

Finally, I'm not clear about the varint implementation in the signal table (I've got no idea right now how that's encoded) but I am skeptical that combining simultaneous changes is much of a benefit. Other than startup (which is a corner case) I don't see that there is a great likelihood of different signals changing simultaneously (especially at a 90kHz resolution.)

I would have thought that a table indexed by timestamp (which guarantees locality of reference), source and type would be pretty fast to query, given that most queries are going to be for periods of less than 24 hours range.

Something like this (not sure this is valid Sqlite syntax)

id int not null primary key auto_increment,  // perhaps redundant but little overhead
time_90k int not null,
source_id int not null,                                  // implies a table that describes both cameras and other signal sources.
type_id int not null references signal_type_enum(id),
current_state int not null,
previous_state int not null default 0,
unique (time_90k, source_id, type_id)

When a signal event is posted, the system would have to query the current state of that source/type to populate the previous_state field. The source reference should be an int for speed, but this would have to be able to be mapped to either a camera or another input source.

[scottlamb]

Trying to catch up on all your questions. Sorry if I missed something.

I'll try to address these in the documentation sooner or later. I probably should write a whole design doc on signals, as well as add more to schema.sql and api.md.

though I wonder why some tables are related by id and others by uuid

I tried to use uuid in places where something outside the database would want to choose an identifier that it'd be confident wouldn't conflict with anything in the database:

• types: you can basically make up your own type and know that any time you see that in any database, it has the meaning you defined. You own the enum id space associated with that type also, so no need to use uuids for those.
• sources: similarly, you can define uuids for some external source like a security system zone, or have them match cameras in the database, without having to introduce some namespace to disambiguate ids.

I used small-integer ids where that doesn't apply and/or I wanted a small value for efficiency. Some things then have both.

The signal_camera table potentially duplicates some information (presumably in the majority of cases a direct association implies that the associated camera is also the source) and I wonder if it is necessary, or whether selecting which signals to display at any given time could just be done in the UI. I don't have a clear position on that yet, probably will once I get further with the UI.

Yeah, I took a stab at what information we'd want about signals, and it's possible I guessed wrong. I think in schema version 7 I'll move some stuff from fixed columns to json blobs to give us more agility as we flesh it out.

What this means is that there is now a signal defined for every camera for every signal type. At this point my gut feel is that rather than having a table to enumerate all permitted source/type combinations it would make more sense to just have a set of signal types defined (some predefined ones plus custom - so this is the existing signal_type_enum table) but allow any source (camera or another device) to announce signal changes as a (source, type, state, timestamp) tuple.

So essentially the signal is created on demand? Hmm, seems possible. We'd lose the short_name though. In my setup, the source type is like "motion zone" or "line crossing rule", and the name is more specific like "courtyard line crossing" vs "front door line crossing".

Finally, I'm not clear about the varint implementation in the signal table (I've got no idea right now how that's encoded) but I am skeptical that combining simultaneous changes is much of a benefit. Other than startup (which is a corner case) I don't see that there is a great likelihood of different signals changing simultaneously (especially at a 90kHz resolution.)

You might be right that it's overkill. I was trying to keep database size and write amplification under control if there's frequent restarts of Moonfire itself, of a camera motion agent, the security system, etc. Some of those can happen because eg my security system's wireless receiver is struggling, in which case 20 zones can simultaneously go back and forth between "unknown" and "normal". In any case, this is a detail that you don't have to worry about as a consumer of the API, unless I've screwed up the implementation.

[clydebarrow]

I tried to use uuid in places where something outside the database would want to choose an identifier that it'd be confident wouldn't conflict with anything in the database

That would make sense if the "outside thing" could choose any string, e.g. using the Hikvision VMD identifier to map directly to a database entry for a motion signal type, but enforcing it to be a UUID limits the usefulness IMHO. In practice all I can see is that there will end up being some automatic process to create a new UUID which then offers no benefit over a row ID.

Assigning UUIDs to cameras and to other signal sources initially makes sense since these are things that might need to be persisted across system regeneration - but that's no help unless there is an easy mechanism to transport these between database incarnations. A SQL dump is one way - but IDs can be preserved in that case anyway. Using the same source for two different simultaneously operating NVRs would also make UUIDs useful, but I see that very much as a corner case.

Not a big deal, but blobs are more expensive to index, and can't be automatically generated by the database.

So essentially the signal is created on demand? Hmm, seems possible. We'd lose the short_name though

I'd just do what I already did - concatenate the source and signal type shortnames, e.g. signal "Motion" on camera "Entry" becomes "Entry Motion". Easy peasy.

In any case, this [signal database schema] is a detail that you don't have to worry about as a consumer of the API, unless I've screwed up the implementation.

Quite true. I got into that simply because I was already wrapping my head around the schema. That's not an issue for the UI.

[scottlamb]

That would make sense if the "outside thing" could choose any string, e.g. using the Hikvision VMD identifier to map directly to a database entry for a motion signal type, but enforcing it to be a UUID limits the usefulness IMHO.

I certainly could allow it to be any string. uuids rather than human-readable names mean there doesn't have to be any policy to avoid conflicts, though.

Assigning UUIDs to cameras and to other signal sources initially makes sense since these are things that might need to be persisted across system regeneration - but that's no help unless there is an easy mechanism to transport these between database incarnations.

One reason to have camera uuids is if you want to have one integrated UI for multiple Moonfire backends. Eg one person I talked with was interested in having a single UI for a bunch of family businesses across a city. I think the ideal setup would have several recording backends. Camera ids might conflict between them; uuids wouldn't.

Of course my UI is completely unsuitable for this use case now, but Moonfire right now can be thought of as two parts: a solid "NVR engine/DBMS", and my crappy prototype UI that you're replacing (or at least providing an alternative to). There could be a UI more appropriate for this task.

Assigning UUIDs to cameras and to other signal sources initially makes sense since these are things that might need to be persisted across system regeneration - but that's no help unless there is an easy mechanism to transport these between database incarnations.

I'd just do what I already did - concatenate the source and signal type shortnames, e.g. signal "Motion" on camera "Entry" becomes "Entry Motion". Easy peasy.

But I want more than one motion or line crossing zone per camera. So that only works for me if the source is finer-grained than camera, in which case I think we've basically shifted which object the caller deals with rather than reduced the number of objects.

[clydebarrow]

I think the ideal setup would have several recording backends. Camera ids might conflict between them; uuids wouldn't.

Well, no, because if the integration is done at the UI level any ID is valid only within the context of the API for a single backend. So in that case a camera is identified by a (backend_id, camera_id) tuple.

[scottlamb]

True, that's possible. But it might also end up with a variation of what you mentioned earlier:

Using the same source for two different simultaneously operating NVRs would also make UUIDs useful, but I see that very much as a corner case.

like sub stream on a "main" NVR, more detailed view on individual NVRs.

In any case, if camera UUIDs are causing a problem for you, I can probably fix it. Like I could make the API endpoints in terms of id instead, and keep around the camera uuid just in case separately. They should be fairly harmless; I don't expect one installation to have so many cameras that their uuids are a significant cost in terms of bytes or anything.

[clydebarrow]

In any case, if camera UUIDs are causing a problem for you, I can probably fix it.

It's not a problem as such. The only real problem right now is the lack of an API (and thus a UI) to manage the signal definitions. So I'm really just struggling with a schema that looks complicated to me, and trying to envisage what the external API based on that might look like, and then figuring out how to simplify it enough to present a naive (and impatient) user with a simple process to add a camera and activate motion detection.

I have started getting signal events into the database using a simple Golang gateway server here: https://github.com/clydebarrow/moonglass/tree/trunk/nvrEvents

The FTP strategy did not work with Hikvision - no idea why - but the proprietary API was easy enough to consume.