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:

 

			Devices
	ID

ID

Node

Network Number

lat/lon overrides

Name

1

101

492

nycmesh-492-omni

-

102 492 nycmesh-lbe-492

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:

Devices

ID

Network Number

lat/lon overrides

3Devices

731

-

4

ID

311

Node Name

x,

y 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. 

In this scenario,Here we treatuse the lightAccess polesPoint asobject ifto theyrepresent areeach “apartments”AP, inand we use the Vernonlat/lon building.on Theythese eachrows getto their own install #, but imagining a tenant living inspecify the lightlocation pole, we say that this imaginary install is “connected via” a device object representingfor the AP.map Thedots. networkAlso, numberfor legacy reasons, the installs for these APs isare setincluded to 5916, reflecting their first hop router (andfrom the factspreadsheet they(but are not themselves assigned NNs). Links betweenneeded, the courtyardAP APsdevices and the core routeralone are included so that they are rendered on the map  sufficient)

 

The database tables for this scenario look like this:

Buildings ID Primary Node Nodes Address BIN 401 5916 5916 303 Vernon Avenue 3042881

				Access BuildingsPoints
	ID

ID

Node Lat /

PrimaryLon NN

Address

BIN

Name

6

400

5916

x,

303 Vernon Avenue

y nycmesh-5916-gbep1-roof

3042881

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

Network Number

lat/lon overrides

5Links

5916

-

6

ID From

5916

Device To

x, y

Device

7

401

5916

400

x, y

401

8

402

5916

401

x, y

403

9

403

5916

403

x, y

404

10

404

5916

409 <

x,1934 y

Siklu device id >

11

405

5916

401 <

x,162 y

XR device id > < many more links going all over Brooklyn >

*there

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 NNNN, 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	Via Device Node	Building
3461	3461	7 501
3921	- 3461	7 501
6723	-6723	7 501
11024	377	7 501
14399	- 3461	7 501
14960	- 3461	7 501

Buildings

ID

Primary NN

Address

BIN

Nodes

7

Network Number

3461

Buildings Name

135

Eastern Parkway 3461 501

3029628

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

Network Number

lat/lon overrides

12Devices

3461

-

13

ID

377

Node Name

x,

y 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

10Links

12

<SN3 device id>

11

ID From

13

Device To

12

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	Via Device Node	Building
3606	14 3606	8 601
5933	15 5933	8 601
7177	15 5933	8 601
8152	16 169	8 601
8274	14 3606	9 602
8085	16 169	11 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 NN Node	Address Nodes	Address

BIN

8

601

3606

3606,

5933, 169

476 Jefferson Street

3819572

9

602

3606

3606,

5933, 169

488 Jefferson Street

3819572

10

603

3606

3606,

5933, 169

28 Scott Avenue

3819572

11

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 >

Devices

ID

Network Number

lat/lon overrides

14Links

3606

x, y

15

ID From

5933

Device To

x, y

Device

16

601

169

602 <

x,1417 y

GBELR device ID > 602 601 603 603 603 604 604 604 601 < additional links not shown >

Links

ID

From Device

To Device

12

14

<1417 device id>

13

14

15

14

15

16

15

16

14

Appendix A - Full Schema Diagram

 

The following is a complete schema diagram, showing all fields. New additions from the current implementation are shown in yellow, and removed fields are shown in red