MeshDB Schema Design

Background

(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)

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:

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:

 

Buildings
ID	Primary Node	Nodes	Address	BIN
101	492	492	216 Schaefer Street	3079532

ID

-

			Devices
	ID	Node	Network Number	lat/lon overrides	Name
1 101	492	nycmesh-492-omni
102	492	nycmesh-lbe-492

ID

			Links
	ID		From Device		To Device
1 101	1 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:

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

ID

			Devices
	ID	Node	Network Number	lat/lon overrides	Name
2 201	4734	- nycmesh-4734-omni

ID

			Links
	ID		From Device		To Device
2 201	2 201	< <4507 sector device id>

 

Installs
Install Number	Via Device Node	Building
731	3 731	5 301
12985	4 731	5 301

5

190

East

1086499

Buildings
ID	Primary NN	Address	BIN
Nodes
Network Number	731 Buildings	Name
731	301	7th Street
311	301	-