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).
We have the following models:
- 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
- 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.
- 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.)
- 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.
- 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).
- 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)
- 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
- 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
- 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 |
| Links | ||
| ID | From Device | To Device |
| 101 | 102 | < saratoga sector device id> |
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 |
| Links | ||
| ID | From Device | To Device |
| 201 | 201 | < 4507 sector device id> |
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 |
| Links | ||
| ID | From Device | To Device |
| 301 | 301 | 302 |
| 302 | 301 | < 1932 sector ID > |
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 > | ||
| Links | ||
| ID | From Device | To Device |
| 401 | 400 | 401 |
| 402 | 401 | 403 |
| 403 | 403 | 404 |
| 404 | 409 | < 1934 Siklu device id > |
| 405 | 401 | < 162 XR device id > |
| < many more links 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
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 > | ||
| Links | ||
| ID | From Device | To Device |
| 501 | 502 | 504 |
| 502 | 503 | < SN3 AF24 device ID > |
| < 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 > | ||
| Links | ||
| ID | From Device | To Device |
| 601 | 602 | < 1417 GBELR device ID > |
| 602 | 601 | 603 |
| 603 | 603 | 604 |
| 604 | 604 | 601 |
| < additional links not shown > | ||
Appendix A - Full Schema Diagram
The following is a complete schema diagram, showing all fields
