Skip to main content

MeshDB Schema Design

(updated September 2024 based on schema changes)

Background

MeshDB is an software application which replaces the New Node Responses Google Sheet (the spreadsheet) as the source of truth for NYCMesh member, install, geolocation, device, and connection information via a proper SQL database. It is built in the Django ORM, using Python Model objects to represent underlying database schema structures. In this document, we summarize the database schema and explain a variety of edge case that occur on the mesh, detailing how the edge case is represented under the MeshDB schema

The Schema (simplified)

The following diagram depicts the schema, showing the relationships between models (SQL tables), and some key attributes of each model. For clarity, non-essential attributes are omitted (see appendix A for a comprehensive diagram).

See MeshDB Status Values for more information on the possible values of the status field on each modelmodel. An editable copy of all diagrams on this page can be found here

MeshDB-Simplified Sept 24.drawio.png

We have the following models:

  1. Member - Represents a single NYC Mesh Member (even if they have moved between multiple addresses and therefore have multiple installs or "own" multiple active installs ). Tracks their name, email address, and other contact details
  2. Install - Represents the deployment (or potential deployment) of NYC Mesh connectivity to a single household. This most closely maps to the concept of a row in the spreadsheet. Tracks the unit number of the household, which member lives there, and which building the unit is located within. It also contains an install number, which corresponds to row number on the spreadsheet. With foreign keys to Member, Building, and Node, it acts as the central model, tying most of the schema together. Many objects have a status field, but the install status field maps most closely onto the status tracked in the spreadsheet.
  3. Building - Represents a location in NYC identified by a single street address (house number and street name). In the case of large physical structures with more than one street address, we will store one Building object for each address that we have received Install requests for. Buildings track a primary node, to represent the "colloquial network number" used by volunteers to describe the site. In the case that a building has more than one network number, the primary node will be set based on the network number that volunteers designate as the “primary” (usually the first assigned, busiest router, etc.)
  4. Node - Represents the abstract concept of a an NYC Mesh "site". Most closely corresponds to a dot on the map. Most concretely, this refers to a collection of NYC Mesh devices with the same network number. Each node has a single network number. A node can be assigned to multiple "building" objects, in the case that a single device powers multiple adjacent buildings or in the case that a single physical structure has more than one street address.
  5. Device - Represents a networking device (router, AP, P2P antenna, etc.). Contains a mandatory foreign key to node, which will be set based on the NN of the device, or of the “first hop” router used by this device (for devices like APs which have no NN assigned).
  6. Sector - A special type of device (using Django Model Inheritance to inherit all fields from device) which adds additional fields related to the display of sector coverage information on the map (azimuth, width, and radius)  
  7. Access Point - A special type of device (using Django Model Inheritance to inherit all fields from device) which adds additional fields related to the display of APs on the map. It contains lat/lon override fields, which can be used to refine the exact location of this device for map display
  8. Link - A connection between devices, which represents a cable or wireless link, whether directly between the devices or via other antennas not represented with their own device objects
  9. LOS - Identifies a pair of building objects which can see each other. Renders as a "potential" link on the map display (if not superseded by an active Link object connecting the same dots). This can be populated manually by volunteers, or in the future, automatically by analysis tools

UUID Keys, Omitted Columns & Tables, and State Drift

NB: the examples used in this document reference integer primary and foreign keys. This is for simplicity of understanding only. The real system uses UUID based keys for all tables. For brevity, we also use comma separated foreign keys in place of a join table for the Node <-> Building M2M relationship. We omit tables and columns that are not directly relevant to the edge case (e.g. Member, LOS) but these are likely present in the real dataset. Additionally, these sites may have evolved since this document was written (September 2024). Please use the site descriptions below as the source of truth for the "real world" state of these locations, rather than say, climbing up on a roof and checking for yourself.

Overall, this document is conceptually accurate, but to get the full implementation details for a database migration, code change, etc. please consult the Python model definitions, and the Postgres SQL DDL directly.

Example 1 - NN492 - Typical Multi-Tenant Install


In this simple example, we have two tenants in a single building with a single address, both connected via cables directly to an omni on their shared roof. They are connected to the rest of the mesh via an LBE to Saratoga. The database tables for this scenario look like this:

Installs
Install Number Node Building
13134 492 101
13276 492 101
Nodes
Network Number Buildings Name
492 101 -
Buildings
ID Primary Node Nodes Address BIN
101 492 492 216 Schaefer Street 3079532
Devices
ID Node Name
101 492 nycmesh-492-omni
102 492 nycmesh-lbe-492

Example 2 - NN 4734 - Cross-Building Installs

In this example, members in 3 adjacent buildings, each with their own address, are connected via a single omni, with cable runs across the roofs directly to the member’s apartments. They are connected to the rest of the mesh via an mant 802.11 sector at 4507. The database tables for this scenario look like this:

Installs
Install Number Node Building
4734 4734 201
6972 4734 202
13663 4734 203
Nodes
Network Number Buildings Name
4734 201, 202, 203 -
Buildings
ID Primary Node Nodes Address BIN
201 4734 4734 31 Clarkson Av 3115982
202 4734 4734 25 Clarkson Av 3115985
203 4734 4734 27 Clarkson Av 3115984
Devices
ID Node Name
201 4734 nycmesh-4734-omni

Example 3 - 7th Street (NN 731) - Multiple Omnis on one building

In this example, we have one regular tenant in a single building with a single address. However there is also a rooftop office with its own omni, connected wirelessly to the primary one. They are connected to the rest of the mesh via a GBELR to Grand. The database tables for this scenario look like this:

Installs
Install Number Node Building
731 731 301
12985 731 301
Nodes
Network Number Buildings Name
731 301 7th Street
311 301 -
Buildings
ID Primary Node Nodes Address BIN
301 731 731, 311 190 East 7th Street 1086499
Devices
ID Node Name
301 731 nycmesh-731-omni
302 311 nycmesh-311-omni

Example 4 - Vernon (NN 5916) - Courtyard APs


In this example, we have a core hub site in a single building with a single address. However, there are many Access Points (APs) on light poles in the building’s courtyard. These light-poles are unquestionably associated with the same building/address as the core router of this hub, but need to be shown separately on the map. 

Here we use the Access Point object to represent each AP, and we use the lat/lon on these rows to specify the location for the map dots. Also, for legacy reasons, the installs for these APs are included from the spreadsheet (but are not needed, the AP devices alone are sufficient)

Installs
Install Number Node Building
5916 5916 401
6345 5916 401
11875 5916 401
11876 5916 401
11877 5916 401
11878 5916 401
11879 5916 401
11880 5916 401
Nodes
Network Number Buildings Name
5916 401 Vernon
Buildings
ID Primary Node Nodes Address BIN
401 5916 5916 303 Vernon Avenue 3042881
Access Points
ID Node Lat / Lon Name
400 5916 x, y nycmesh-5916-gbep1-roof
401 5916 x, y East AP
402 5916 x, y Southeast AP
403 5916 x, y South AP
404 5916 x, y Southwest AP
405 5916 x, y Northwest AP
406 5916 x, y Northeast AP
Devices*
ID Node Name
409 5916 nycmesh-5916-eh8010-1934
410 5916 nycmesh-5916-af60xr-162
< many more devices and sectors going all over Brooklyn >

*there will actually also be entries for each of the APs in the Devices PostgresSQL table and API responses, but we hide these from the admin UI to reduce confusion

Example 5 - Prospect Heights (NN 3461) - Multiple NNs for one building

MeshDB-PH.drawio.png

In this example, we have a core hub site in a single building with a single address. The primary NN, 3461, also serves a member’s apartment as install #3461. However, there is another apartment which could not due to practical considerations be connected via a cable, and had to be connected via an antenna in their window to a sector on the roof. This antenna needed an NN for configen and naming, and so this building received multiple NNs.

The database tables for this scenario look like this:

Installs
Install Number Node Building
3461 3461 501
3921 3461 501
6723 6723 501
11024 377 501
14399 3461 501
14960 3461 501
Nodes
Network Number Buildings Name
3461 501 Prospect Heights (PH)
6723 501 -
377 501 -
Buildings
ID Primary Node Nodes Address BIN
501 3461 3461, 6723, 377 135 Eastern Parkway 3029628
Devices
ID Node Name
501 3461 nycmesh-3461-core
502 3461 nycmesh-3461-southeast
503 3461 nycmesh-3461-af24-713
504 377 nycmesh-nsl-377
< many more devices and sectors going all over Brooklyn >

Example 6 - Jefferson (NN 3606) - Multiple NNs for multiple buildings

In this example, we have a building with 4 addresses and 3 omnis on the roof, each with its own network number. There is no clean mapping between NNs and addresses, since each omni serves installs in multiple buildings. The omni of the primary NN, 3606, provides the uplink to Hex House (NN 1417). There are additional backup links, not shown here for simplicity.

The database tables for this scenario look like this:

Installs (omitting abandoned & potential for brevity)
Install Number Node Building
3606 3606 601
5933 5933 601
7177 5933 601
8152 169 601
8274 3606 602
8085 169 604
Nodes
Network Number Buildings Name
3606 601, 602, 603, 604 Jefferson
5933 601, 602, 603, 604 Jefferson-2
162 601, 602, 603, 604 Jefferson-3
Buildings
ID Primary Node Nodes Address BIN
601 3606 3606, 5933, 169 476 Jefferson Street 3819572
602 3606 3606, 5933, 169 488 Jefferson Street 3819572
603 3606 3606, 5933, 169 28 Scott Avenue 3819572
604 3606 3606, 5933, 169 16 Cypress Avenue 3819572
Devices
ID Node Name
601 3606 nycmesh-3606-omni
602 3606 nycmesh-3606-gbelr-1417
603 5933 nycmesh-5933-omni
604 169 nycmesh-169-omni
< additional devices not shown >

Appendix A - Full Schema Diagram

The following is a complete schema diagram, showing all fields (editable version here)

See MeshDB Status Values for more information on the possible values for the status field on each model

MeshDB-Full Schema Sept 24.drawio.png

Back to top