MeshDB

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

MeshDB Status Values

This page lists all the possible values for the status field on each MeshDB database object. For more information about the different tables and their meaning, see MeshDB Schema Design

Node Status

Value Description
Planned

A mesh node which has not yet been brought online. This happens commonly when planning for new large hubs, but can apply to smaller installs also. May or may not have a network number assigned. Nodes generated by the NN assignment form start in this status and must be manually marked Active at the time of install
Active A nominally routable network number on the mesh
Inactive A node which was active but is no longer connected to the mesh due to disassembly, abandonment, etc. If a node drops offline for a few days, it does not need to be marked Inactive, but if it is offline for a month or more, or is known to have been taken down, it should be marked Inactive

Install Status

Value Description
Request Received Default status when an install is created. It indicates that the join form has been submitted for the specified unit, but not much more has happened
Pending This install has LOS and an install has been scheduled, or is in the process of getting scheduled. We expect to connect this install in the near future. Installs should not remain in this status longer than 6 months. The NN assignment form automatically transitions installs from "Request Received" to "Pending" when submitted
Blocked We have received some kind of confirmation that the install identified by this row is 1) interested in getting connected and 2) is unable to actually go through with an install. Usually due to a landlord not cooperating
Active A nominally connected unit, with active service
Inactive This is a physically connected unit, with equipment in place but the equipment is not communicating with the mesh. This may be due to a power, cable, or software issue. Installs in this status are expected to reactivate at some point the future. If the install has been physically taken down or completely abandoned, it should be marked as Closed instead

If an install drops offline for a few days, it does not need to be marked Inactive, but if it is offline for a month or more, this status is likely appropriate

Installs should not remain in this status longer than 2 years
Closed This install request is no longer something we are interested in tracking. This could be because the person communicated disinterest, asked to unsubscribe, gave invalid information in the join form. 

This status is also used for installs that have been abandoned, or physically taken down, and are not expected to be reactivated

This could also be a duplicate of another install request
NN Reassigned A special status for low-numbered installs indicating that this install number has been re-used as a network number for another node and is not safe to use as a network number for this install, should the unit associated with this install seek to get connected
Value Description
Planned A future link that might be added to the mesh, if the specified devices are deployed. Does not need to be actively scheduled, but should have some chance at feasibly being completed (don't enter planned links that clearly have no LOS for example)
Active A nominally online link that is able to pass traffic between its specified devices. That does not necessarily mean it is a primary path for any internet traffic (e.g. WDS backup links)
Inactive A link which was active but is no longer able to pass traffic due to disassembly, abandonment, loss of LOS, etc. If a link drops offline for a few days, it does not need to be marked Inactive, but if it is offline for a month or more, or is known to have been taken down, it should be marked Inactive

Device Status

Value Description
Planned A future device that might be added to the mesh. Does not need to be actively scheduled, but should have some chance at feasibly being completed
Active A nominally online device that is reachable on the mesh
Inactive A device which was active but is no longer online due to disassembly, abandonment, power issue, etc. If a device drops offline for a few days, it does not need to be marked Inactive, but if it is offline for a month or more, or is known to have been taken down, it should be marked Inactive

How to onboard API clients to MeshDB

These instructions are for creating API access for automated systems. For human users, see How to Onboard Users to MeshDB

Adding a new user for an application

Navigate to the admin portal at db.mesh.nycmesh.net/admin/ and select add user

Screenshot 2024-04-09 at 12.17.00.png

Make a new user specifically for the application, not just the author of the application. For example, if Andy is creating an application to measure member distance to link NYC kiosks, don't create a user called AndyB, create a user called AndyB-LinkNYCKioskTool. For the password, enter something secure, like a random password generated by your browser, but there is no need to save this password, we will use a token to authenticate this user.

Screenshot 2024-04-09 at 12.23.56.png    Screenshot 2024-04-09 at 12.28.34.png

Save the user, and then click on the username in the Users list to add the necessary permissions directly on the user object. Do not add the user to any groups. Do not grant the user Staff or Superuser permissions

Screenshot 2024-04-09 at 12.30.20.png

Use the arrows or double click to select permissions from the list of all possible permissions the application could be granted. Most applications do not need change/delete/add permissions. In this example, we grant Andy's tool "view" access to the Install, Building, and Member tables. Save the changes you've made to the user object.

Adding an API token

Follow the instructions under Adding a new user for the application above. Then select "Add" next to Tokens. Select the user you just created in the dropdown provided

Screenshot 2024-04-09 at 12.33.39.png         Screenshot 2024-04-09 at 12.34.39.png

Save the new token, then send it to the author of the application. For more information on using this token to query the API, see the API docs here: https://db.mesh.nycmesh.net/api-docs/swagger/

Adding a new web hook recipient

Follow the instructions under Adding a new user for the application above. You may use the same "User" object for both tokens and web hooks if they are for the same application. 

Select the "Add" button next to Webhook Targets, then use the magnifying glass icon to select the user you created for this application. Enter the target URL for the notification delivery (will be provided by the application owner). This URL will receive an HTTP POST request every time the selected event is fired.

Screenshot 2024-04-09 at 12.51.23.png.     Screenshot 2024-04-09 at 12.53.19.png

Select the appropriate event in the dropdown based on the event the application needs to receive, and save. If the application needs to receive more than one event type, add a separate webhook target for each event they need to receive. 

How to Onboard Users to MeshDB

These instructions are for creating administrator console access for human users. For automated systems, see How to onboard applications to MeshDB

Adding a new user for admin console access

Navigate to the admin portal at db.mesh.nycmesh.net/admin/ and select add user

image.png

Every individual person should receive their own unique user object. User objects should not be shared among multiple volunteers. We have established an informal convention of using the volunteer's first name followed by their last initial. For example if the volunteer's name is John Doe, we would create their username as johnd. For the password, enter something secure, like a random password generated by your browser. Save this password and send it to the volunteer over slack (and encourage them to change it immediately using the link in the top right).

Screenshot 2024-07-07 at 12.10.42.png

Click Save and continue editing to proceed to the full user editor

Screenshot 2024-07-07 at 12.15.16.png

Here, set the volunteer's first and last name and email (if available). Then enable Staff status, which allows them to log in to the admin site.

Email address is not a required field. However, if you do not set an email address for the user, they will not be able to utilize the "Reset my Password" feature. So it is recommended that you set an email for all users

Finally, you need to add the new user to the appropriate group based on their needed permissions. The following table summarizes the difference in permissions between the various groups:

Group Permissions Example User
Read Only View-only permissions to all MeshDB core data such as Member Contact info, install details, buildings, etc

No access to authentication information such as lists of MeshDB users or API tokens		 A junior installer
Installer All permissions from Read Only, plus the ability to edit all MeshDB core data (buildings, install details, member contact info, etc.) but no permission to add or delete these objects

No access to authentication information such as lists of MeshDB users or API tokens
 Install leaders
Admin Full access to every table in the the core MeshDB database. Add, Edit, Delete permissions on these tables.

Full view access to all tables, including authentication related information: Groups, Users, API Tokens. 

Ability to create and modify users.

No access to modify the underlying structure of groups or add new ones.		 Mesh administrative staff
Superuser Full access to do anything in the application. Bypasses all permissions checks, for safety reasons, this should only be applied to devs working on the maintenance of MeshDB itself

The main thing this unlocks is the ability to modify the structure of the group permissions themselves, which should not be done lightly		 MeshDB Core Devs

Do not grant permissions directly on the user object. Add the user to the appropriate group instead. Since the permissions are cumulative, a user should only need to be in a single group. To reduce confusion, this is strongly recommended.

Screenshot 2024-07-07 at 12.35.19.png

Add the user to the appropriate group and save

SQL Explorer (formerly PGAdmin)

image.png

For more in depth access to MeshDB's datebase, you can use the SQL Explorer tool to perform complex queries and operations on the the MeshDB dataset. The tool is read-only, however queries can be stored for later use by yourself or others.

Try it out at https://db.nycmesh.net/explorer/play/.

You can use it by logging in with your MeshDB account! If you don't have access to the tool or need a Mesh DB account, reach out in #meshdb on slack.

How to bulk create installs for large buildings

These instructions detail how to provision many Install requests at once. Usually this is done at large buildings where the mesh serves many apartments. Often we install the equipment all at once during a renovation or construction without an explicit request from the resident of the apartment. In this scenario we need to create install numbers in our systems without asking each resident to submit the join form.

Create/Locate the Supporting Objects

Since MeshDB uses a relational model to store data, multiple objects need to be created to represent the equivalent of a single row in the "New Node Form" spreadsheet. Before we can create Install objects, we must first create Member and Building objects, and assign a network number. For more information about the various tables included in MeshDB, see MeshDB Schema Design.

The easiest way to do this is to submit the join form once on behalf of the building (if not already done previously). Once created, open this first install object in the MeshDB admin UI:

Screenshot 2024-09-07 at 22.22.45.png

Use the eye icon next to the building and member entries to open the linked objects. On each object, note the object ID from the URL. For example, in the screenshots below, the meshdb member ID is 5b9c3cde-bbec-4b1a-8267-f1783c78b8c3, and the meshdb building ID is 4e9da8b9-f068-499f-9672-49e03a2c9834

Screenshot 2024-09-07 at 22.24.49.pngScreenshot 2024-09-07 at 22.24.35.png

At this point, you should also create a Node for this location using the NN assignment tool or vanity NN process. Use the "first" install number we created above to assign the NN. Then use the network number to locate the node object, and copy the node ID from the URL similar to the building and member IDs:

Screenshot 2024-09-07 at 22.31.14.png

Build the import CSV

In order to create the install requests in bulk, we need to place the necessary information in a tabular format. Several formats are supported, but for these instructions we will use CSV as it is the simplest to work with using standard spreadsheet applications such as Excel or Google Sheets. To start, open the following CSV template in your favorite spreadsheet application (you will need to paste this into a text editor and save it as a .csv file):

Edit the following columns based on the information collected above:

node,status,request_date,install_date,abandon_date,building,unit,roof_access,member,referral,notes,diy
,Pending,07/19/2024,,,,2A,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,2B,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,2C,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,3A,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,3B,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,3C,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,4A,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,4B,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,4C,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,5A,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,5B,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,5C,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,6A,1,,Large Buidling Install,Building Name,0
,Pending,07/19/2024,,,,6B,1,,Large Buidling Install,Building Name,0

The following columns can be optionally added to the csv, but should not be unless there is a good reason, and you know what you're doing:

Import your CSV into MeshDB

Navigate to the install list page in the MeshDB admin UI and select the "Import" option:

Screenshot 2024-07-19 at 22.34.24.png

Select your CSV file, and select CSV as the format in the dropdown:

Screenshot 2024-07-19 at 22.36.01.png

Review the information available on the confirmation screen for correctness, note the generated install numbers if desired and select "confirm import" to create the new installs in the database:

Screenshot 2024-09-07 at 22.40.39.png

Check to make sure the correct number of installs was created, and observe the newly created install rows in the "all installs" view:

Screenshot 2024-07-19 at 22.39.22.png

Bulk Add Members

The instructions above assume that contact information is not available for the members that will reside in the created install objects. However, if this information is available, and you'd like to create install objects with the correct contact information associated with each, first create a CSV for the member objects using the following template (you will need to paste this into a text editor and save it as a .csv file):

name,primary_email_address,stripe_email_address,additional_email_addresses,phone_number,additional_phone_numbers,slack_handle,notes
Tami Gibson,tamigibson@gmail.com,,,+1 555 555 5555,,,
Jean Williams,jeanwilliams@gmail.com,,,+1 555 555 5555,,,
Shari Martinez,sharimartinez@gmail.com,,,+1 555 555 5555,,,
Melinda Martin,melindamartin@gmail.com,,,+1 555 555 5555,,,

 Edit the following columns based on the information available for each member:

The following columns can be optionally added to the csv, but should not be unless there is a good reason, and you know what you're doing:

Import this CSV on the members screen, following the same instructions provided above for installs. Just take note of the member IDs provided on the import confirmation screen, and use these IDs in the member column in the installs CSV

How to avoid manually entering latitude/longitude/altitude when constructing Node objects

When constructing or editing Node objects, users are presented with the following fields:

Screenshot 2024-09-07 at 19.26.44.png

Since manually locating these coordinates is tedious, MeshDB is able to automatically populate these fields from the Building object. To use this feature, simply scroll down to the "Buildings" heading below, and click "Show" next to "Edit Related Buildings":

Screenshot 2024-09-07 at 19.33.50.png

Select "Add another Building"

Screenshot 2024-09-07 at 19.38.11.png

In the building selection dropdown, enter the address of the building you are trying to assign this node to:

Screenshot 2024-09-07 at 19.37.17.png


Once a building is selected, the "Autocomplete from Building" button will become enabled. Click it to automatically populate the coordinates based on the information available for this building:

Screenshot 2024-09-07 at 19.39.37.png

How to assign a "vanity" network number

These instructions are only needed when precise control of the exact NN is required. Most installs should just use the standard network number assignment form 

Network numbers uniquely identify devices on the NYC Mesh network. They are used to compute IP addresses and in device names to locate and organize things. In some cases, mesh volunteers prefer a specific NN be allocated to a given install number, to allocate sequential numbers for a series of nodes, to match the street address, or some other significant number for the building.

To allocate a specific network number for a given install, first validate that the desired network number is unused. Navigate to the Install search page and enter the network number you'd like to use:

Screenshot 2024-09-07 at 18.39.00.png

Click "Search" and then find the row where the install number corresponds to the desired network number. Check the status of this row. To recycle the install # as a vanity NN, the status must not be Active. In this case, the Install shows as Request Received, so we are good to proceed.

Next, check for any pre-existing nodes with the desired network number. Open the Node search page and enter the desired network number:

Screenshot 2024-09-07 at 18.50.39.png

Click "Search" and then confirm that there are no nodes matching this network number

The two above checks are considered best practice to locate a valid network number. However, they are not strictly necessary. MeshDB will perform these checks on your behalf to ensure NN allocation safety as needed in the next step.

Finally, select Add Node to enter the node creation form

Screenshot 2024-09-07 at 18.55.52.png

 

Enter the desired NN in the Node Creation form:

Screenshot 2024-09-07 at 18.58.01.png

Fill out the remainder of the node creation form based on the details of your node. In this case, we are assigning a network number to a Planned Hub, so we select Planned for the status and Hub for the type.

Pro tip: to avoid having to manually enter the latitude, longitude and altitude information, you can automatically populate this information from the Building object. See How to avoid manually entering latitude/longitude/altitude when constructing Node objects for more information

We complete the rest of the fields as appropriate, and click "Save" at the bottom of the form:

Screenshot 2024-09-07 at 19.02.20.png

Next, navigate back to the Install search page and locate the install number that you would like to attach to your vanity network number. Click the link to enter the Install details page:

Screenshot 2024-09-07 at 19.23.54.png

On the install details page, select the node we just created:

Screenshot 2024-09-07 at 19.25.07.png

Finally, scroll to the bottom and click "Save"

How to add potential Hubs and Links to the map

To add potential hubs and links to the map using MeshDB, you must create the appropriate database objects. The right way to do this depends on the current state of the data in the database, and the desired outcome. This page summarizes how to do various common tasks:

How to add a big dot for a potential hub or supernode

Screenshot 2024-09-07 at 19.49.25.png

Submit the join form for the location in question, if not already done. Then open the "Add Node" form in the admin panel:

Screenshot 2024-09-07 at 19.51.42.png

Fill out the information in the form, selecting "Planned" for the status and "Hub" for the type. You do not need to enter a network number if not desired. However if you do not enter a network number at this point, you will not be able to assign a vanity number in the future without re-creating the Node object (automatic NN assignment can still be performed)

Setting the status to "Active" will automatically assign a network number for any Node that doesn't already have one, don't do this until the network number is needed for equipment config, etc.

Screenshot 2024-09-07 at 19.53.53.png

Fill out the remainder of the node creation form based on the details of your node.

Pro tip: to avoid having to manually enter the latitude, longitude and altitude information, you can automatically populate this information from the Building object. See How to avoid manually entering latitude/longitude/altitude when constructing Node objects for more information

Complete the rest of the fields as appropriate, and click "Save" at the bottom of the form:

Screenshot 2024-09-07 at 19.02.20.png

Next, navigate back to the Install search page and locate the install number that you would like to attach to your planned Node. Click the link to enter the Install details page:

Screenshot 2024-09-07 at 19.23.54.png

On the install details page, select the node we just created:

Screenshot 2024-09-07 at 19.25.07.png

Finally, scroll to the bottom and click "Save". Your new dot should be present on the map (after a brief waiting period to allow the map to refresh)

How to add a grey line representing a potential link... 

Screenshot 2024-09-07 at 20.04.54.png

...when the two endpoints of the link are active and the devices to be used are known

When grey new line is to be drawn between active nodes, and the devices at both ends are known, you can use a standard "Link" object, but set the status to "Potential". For example if an SXT is going to be added to an existing node to enable a connection to an existing omni at another node. Perhaps currently the line of sight or antenna gain prevented such a connection between the omnis directly, so there is no existing active link object, despite active nodes at both ends of the potential link.

Use the Device add form to add a device for each side of the link (as needed). Set the status to "Potential" for any devices that do not yet exist in the real world:

Screenshot 2024-09-07 at 20.08.34.png

Save each of these devices, then use the Link add form to create the new potential link:

Screenshot 2024-09-07 at 20.10.32.png

Select "Planned" for the status, so that this link doesn't show as a blue active link on the map.

Save the new link object. Your new line should be present on the map (after a brief waiting period to allow the map to refresh)

...when there are not node objects yet, or the devices are not known


If either end of the link does not have a node object (very likely for potential installs), there is a simpler approach to adding a potential link. We can add this link as a "Line of Sight" (LOS) instead of making it a full fledged link. To do this, open the LOS add form:

Screenshot 2024-09-07 at 20.13.32.png

Select the two buildings you would like to draw a line between, by searching using their addresses. Select "Human Annotated" as the source (since you are manually entering the LOS entry).

Screenshot 2024-09-07 at 20.15.25.png

Finally, click "Save". Your new line should be present on the map (after a brief waiting period to allow the map to refresh)

How to update MeshDB when an install is completed

When an install is completed, there are a few items to confirm are correct in MeshDB so that we have accurate information and an up-to-date map:

  1. Confirm that a network number has been assigned to the install (this is probably already done by the installer, either via the NN assignment form or the vanity NN process in most cases). Do this by searching for the network number in the Nodes search page, and then selecting the correct Node. On the node details page, check that the install that was just completed is listed under "Installs". If it is not listed, correct it on the Install detail page.
  2. Check that the Node details are correct. Validate that the node status is set to "Active", and that the Node's building list includes the building the install is contained in
  3. Click the link to the install details page and mark the install status as "Active"
  4. Update the Install Date field on the install (and Node if not pre-existing) to today's date
  5. Set the Is DIY? field based on if this install was conducted by a volunteer or directly by the member themselves
  6. To add a link to the map, create the appropriate device objects if needed using the device add form. However, these devices are also automatically imported from UISP, so check if the devices already exist by searching for them by name before creating duplicates. Next, add a link between the devices using the link add form if needed. Again, links are automatically imported from UISP, so take care not to duplicate existing link objects

How to update MeshDB to indicate the loss of an install

When an install leaves the mesh, MeshDB needs to be kept up-to-date to ensure we have accurate member count and contact information. 

If an install has just dropped offline for a few hours, no action is needed. MeshDB does not attempt to track the status of the mesh that finely. See node explorer for up-to-the minute information. However, if an install has been offline for a month or more, it should really be marked as "Inactive"

See MeshDB Status Values for more information about the meaning of each status option.

Marking objects as "Inactive"

To update MeshDB for an install that has dropped offline for a few weeks but has the potential to return at a later date, do the following.

  1. Set the status of the Install object to Inactive
  2. Set the status of the Node object to Inactive
  3. Set the status of any devices on the Node to Inactive
  4. Set the status of any links connected to those devices as Inactive

Marking objects as "Closed"

If an install has been confirmed to be abandoned or taken down by talking with the member, or an on-site visit from a volunteer, and is not expected to return, then it should be marked as "Closed"

  1. Set the status of the Install object to Closed
  2. Set the status of the Node object to Inactive
  3. Set the status of any devices on the Node to Inactive
  4. Set the status of any links connected to those devices as Inactive

Multi-install nodes

If the install that as gone offline is a part of a node with additional installs, and those other installs are still online, then only the offline install needs to be marked Inactive or Closed. Nothing else needs to be changed

New Application Integration Guide

So you want to build an application which interacts with MeshDB data. Great! 

First, familiarize yourself with the data schema that we use at a high level. You likely don't need to get into the intricacies of the various edge cases, but read the first section where we describe the high level relationship between the various tables. You probably also want to gain at least read-only access to the admin console so that you can see examples of the schema implemented for real data. Have a friendly admin follow this guide to give you access (or ask in #meshdb on slack). 

Next, review our API docs to get a sense of the endpoints available and the format of the data your application can expect to receive. In order to get credentials, Have a friendly admin follow this guide to create the credentials (or ask in #meshdb on slack)

Environments

We run several instances of MeshDB. This is so that we can do development and testing, gate our changes, and ensure the quality of the code and configurations we ship.

Find our infrastructure at https://github.com/nycmeshnet/k8s-infra

Production (prod1)

https://db.mesh.nycmesh.net

Our main instance.

Preproduction (gamma)

Planned instance. Does not currently exist.

Used to run tests against before changes go out. Designed to be as close to prod as possible.

Development (dev3)

https://devdb.mesh.nycmesh.net

Development instance. Probably broken a lot, used to test infrastructure changes and develop new features. Does not represent prod 1:1.

 

 

Maintenance Mode

MeshDB has a maintenance mode. It prevents all access to the site, UI, and API while active.

To enable it, run `manage.py enable_flag MAINTENANCE_MODE` or POST to the `/maintenance/enable/` route

To disable it, run `manage.py disable_flag MAINTENANCE_MODE` or POST to the `/maintenance/disable/` route

There is also a view in the Admin UI that can enable it, though it will not work for disabling.

Observability

We use Datadog to monitor MeshDB.

Dashboard

https://p.us5.datadoghq.com/sb/4614e554-06a1-11ef-b43a-da7ad0900005-ba514715f9683f103794ee705df1db95

datadog2.png

APM & Tracing

Presently, MeshDB is instrumented at the deployment level with Single Step APM. Meshforms is instrumented from the datadogagent itself using the enabledNamespaces feature.

datadog1.png

Admin Panel

image.png

MeshDB Domains

Environment Service Link
Production
 MeshDB https://db.nycmesh.net
Mesh Forms https://forms.nycmesh.net
LOS Tool https://los.nycmesh.net
LOS Backend https://los-backend.db.nycmesh.net
Admin Map https://adminmap.db.nycmesh.net
Map https://map.db.nycmesh.net
Development
 MeshDB https://devdb.nycmesh.net
Mesh Forms https://forms.devdb.nycmesh.net
LOS Tool https://los.devdb.nycmesh.net
LOS Backend https://los-backend.devdb.nycmesh.net
Admin Map https://adminmap.devdb.nycmesh.net
Map https://map.devdb.nycmesh.net

Feature Flags

We use a library to control whether certain features are enabled.

 

To enable a feature, go to Flag States, create a new state, or edit the existing one, and fill it out like so.

image.png

The feature will then be enabled.

Panoramas

The way that panoramas currently work in MeshDB sucks. They are uploaded to a github repo (https://github.com/nycmeshnet/node-db/tree/master/data/panoramas) manually. Once every now and then, MeshDB runs a celery job to query this GitHub and mutate the contents of this huge directory into _netlify_ URLs to actually serve. How do images get from GitHub to Netlify? I dunno, Magic, I guess.

Presently, they are stored in a single directory

nnXXX(a-z).jpg

XXX(a-z).jpg

Where XXX is either the NN or the Install number.

Proposed solution

Instead of doing All That Nonsense, we should take better advantage of our Dropbox storage and store panoramas in there durably. Upload panoramas (and install photos!) form Meshforms. Then, we can actually serve them from a MinIO instance. The MinIO instance could be ker-ploded and rebuilt from Dropbox, which is cloud-based, HA, and durable.

Add a portal to Meshforms that allows people to upload panoramas and install photos

https://www.dropbox.com/developers/reference/webhooks

MeshDB receives webhooks from Dropbox, syncing panoramas to MinIO, then updates the Building models to point at their location on MinIO.

We get real-time panorama updates :)

(This could also be done with a separate microservice if we want to keep the functionality out of meshdb, but I would want to get the client lib working)

image.png

How to Import/Export CSV files of MeshDB data


The data in MeshDB is best viewed using the tooling available at db.nycmesh.net:

However, for some applications these resources may not provide all of the required query and filtering functionality. In these cases, users may want to export meshDB data for direct editing/analysis in spreadsheet editors or other local tools. This page summarizes the options available to do that.

CSV Export in MeshDB Format

To export individual MeshDB tables in the format used by MeshDB, such as the Members table, first open the MeshDB Admin Panel. Next select the table of interest, Members in this case:

Screenshot 2024-10-21 at 23.44.29.png

On the next page, select "Export" to enter the export dialog:

Screenshot 2024-10-22 at 01.51.26.png


Finally, select "csv" as the output format, click "SUBMIT", and the data will be downloaded:

Screenshot 2024-10-21 at 23.53.13.png

Since we chose to export the data in MeshDB format, it does not include related columns such as install number or node number. These other columns would need to be downloaded separately from their respective tables an joined together with the id column on the member table. This is not trivial, so for common cases, like looking up a member's install by their email address, we provide CSV exports that combine these tables automatically, see below.

CSV Export in Legacy Spreadsheet Format

For convenience and compatibility with legacy scripts and workflows, MeshDB provides a CSV export capability which approximates the columns available in the "New node form responses" spreadsheet

The CSV exports provided in this section are an approximation of the legacy spreadsheet, but are not a perfect recreation. In particular, the notes fields do not match 1:1 with the original spreadsheet due to the way that the notes are parsed, split, and automatically created during the spreadsheet import process. Some smaller details like the exact install used for the `X-` pointer on NN re-use may also vary a bit. This query should not be used to replace the contents of the existing spreadsheet wholesale without careful consideration

To access this capability, use the following links, or navigate via the SQL Explorer homepage:

Screenshot 2024-10-22 at 00.04.08.png

These queries can also be accessed by the following direct links:

Use the "Download" button and select "CSV" as the type:

Screenshot 2024-10-22 at 00.06.56.png

CSV Import in MeshDB Format

These instructions cover the general process for importing a CSV into MeshDB, for a bulk import of partner data for example. For creating many installs in a large building, there is a more specific guide here

Editing large quantities of MeshDB data using CSV import can unintentionally have a large impact on the quality of MeshDB data. Though edits using this feature are supported, this process should primarily be used for imports of new data, rather than edits to existing data. For bulk edits to existing data, a Django Migration in the source code should probably be used instead

To import data to individual MeshDB tables in the format used by MeshDB, such as the Members table, first open the MeshDB Admin Panel. Next select the table of interest, Members in this case:

Screenshot 2024-10-21 at 23.44.29.png

On the next page, select "Import" to enter the import dialog

Screenshot 2024-10-22 at 01.51.51.png

 

In the import dialog, select the CSV file you would like to import from (likely an edited export created using the instructions above):

Screenshot 2024-10-22 at 00.12.13.png

On the following screen, confirm the accuracy of the data, including that the rows are showing as either "Update" or "Create" as expected based on the desired editing:

Screenshot 2024-10-22 at 00.23.22.png

How to View the Edit History for an Object in MeshDB

MeshDB tracks the changes to all objects from all actors. Any change via the admin panel, API, or any automated process, will trigger an entry in the History table. To view the history for an object, navigate to the edit view for that object in the admin panel. In the top right, select the "History" button:

Screenshot 2024-10-22 at 00.37.51.png

The history page shows every time the object was changed, what fields were edited, and who made the change:

Screenshot 2024-10-22 at 00.29.10.png

Restoring an Older Object Version

To revert a change to a specific object, navigate to the history page as described above, then select the revision that you are interested in restoring by clicking the name of the object in the "Object" column:

Screenshot 2024-10-22 at 00.29.10.png

On the revision page, you can see the state of this object at the selected revision. Confirm that the fields are as expected, and then click "REVERT" to restore this version of the object

Screenshot 2024-10-22 at 00.42.54.png

For transparency, the history page reflect the change that you just made:

Screenshot 2024-10-22 at 00.45.25.png