DEV Community: Matthew Walther

How to sell open source technology to the government

Matthew Walther — Tue, 29 Jul 2025 18:17:05 +0000

A Billion Dollar Problem

As governments legalize cannabis, they create billion-dollar industries. A trade-off for this massive economic value-add is the need for regulatory tracking technology. This technology is typically dominated by large, proprietary players. In commercial cannabis, competitors come from pharmaceuticals and private equity.

Governments typically get access to this technology through the procurement bid process, where the budget of these firms shines through. However, a powerful alternative exists for savvy entrepreneurs: open source software.

This essay aims to provide a roadmap for how you can leverage open source principles to successfully bid and win these contracts. The OpenTHC team’s experience is in the cannabis industry. We believe our work and products can serve as a model for the next generation of entrepreneurs to collaborate using OpenTHC technology.

The Open Source Advantage

Governments have more problems than they can hope to solve. Governments are composed of people. People value trust, transparency, and collaboration. But, commercial practices often work against these values in order to make vendors more competitive. Everybody knows the “black box” nature of proprietary software gives users little insight to understand how the software works. It's harder to see how this applies to the work that is done inside of the software.

Governments inherit the problems when they buy proprietary software. An extreme example of this is Washington State’s inability to adequately track lab result reports. Without the ability to understand what was missing from their multiple contracts, they created in-house a custom traceability system. This saved them money in the short term. However, their solution still fails to track lab results the right way. That is a serious threat to the public’s health. Many states in the U.S. share the same problem Washington State has, but have not had markets operating for long enough to give government auditors time to catch these crucial issues. Mediocre technology has frustrated many activists, and fosters distrust in the industry.

It would have been a lot easier if the software chosen by the government could be managed or maintained by officials adept in software management rather than the procurement process. Resilience to vendor lock-in is another opportunity open source brings to the table. If another vendor was able to contract with Washington State to fix-forward their existing project, then the State could stop sinking costs before auditors from the Joint Legislative Audit and Review Committee (JLARC) needed to step in. The competitive benefit from open source means lower bids, and accelerated development. Without the need to start over, Washington can put together a more cost effective solution and eliminate disruptive migrations. This is the exact philosophy that drives our work.

OpenTHC is a leader in the cannabis industry as the top open cannabis standard, and the only participant that maintains open technology for all participants in the commercial market. In 2014, we began our journey serving a diverse array of cannabis licensees.

Most licensees have similar regulatory reporting requirements. This can be referred to as “seed-to-sale”, “traceability”, or “compliance” requirements. All these terms mean the same thing: the paperwork a cannabis business must do to meet the legal requirements of the license issued to them. This typically means some degree of inventory, sales, crop, and lab result reporting. The OpenTHC environment handles all of these requirements, and supports more with its flexible architecture.

OpenTHC did not grow solely from the experience working with licensees. Our team participates in the political process of the cannabis industry. We act in alignment with other ethical, trustworthy, professional, and scientific organizations. No matter who we are working with, we make an effort to honor our values. OpenTHC is an open source participant. We observe the good-faith obligations that all open source actors should follow. Additionally, we always act in the best interests of the plant, and its patients.

Three-Leaf Plan

Since 2020, our team has participated in multiple procurement bids. We have not secured a final contract. But, this process gave us an invaluable education. We are sharing this playbook because the final step – winning the contract – is an opportunity for the right partner.

OpenTHC identified three steps to getting involved as your government's traceability vendor:

1. Study the environment.

2. Organize business partners, stakeholders, and public servants.

3. Execute the vision.

Study

Evaluate the political structure
Understand regulatory, technical, and business Requirements
Read published material

You probably know better than most what the value of “standing on the shoulders of giants” is. It is a fundamental idea of open source movements. What value we have been able to create originates from our ability to listen to other people about complex problems. Material investment in this step is the building of subject matter expertise. Expertise that requires a working knowledge of the environment in which cannabis exists.

Building subject matter expertise naturally includes legal and political realities. Internalize the phrase “everything is politics.” These realities mean different things depending on government composition. In the U.S., most traceability projects enjoy a competitive bidding process. The democratic privilege of a competitive bidding process is not available anywhere, and it is becoming more rare. We acknowledge this as a situation where cannabis regulation may be a low priority. Do not underestimate the Overton window. Fight for your values. Let this plant that has helped so many patients find blessings far beyond what we can immediately see.

It is crucial to familiarize yourself with the way your government buys goods and services. We have seen this referred to as "procurement", "bidding", and "contracting". As the regulation materializes through the political process, the government will change in response to legal changes. New government offices may emerge, or existing agencies may see an expansion of their authority. Procurement is an important tool in the regulator’s tool belt. It allows them to access material and expertise not available to them in house. Traceability projects often have inter-agency agreements in their contracts. This is a function of the intelligence value they generate. The primary cannabis regulator may partner with other departments to buy the traceability solution. These agencies may range from general services, I.T., or their department of revenue.

We have observed how the government's ability to buy services has an outsized effect on the industry. The place where “all the bodies are buried” is always the technical requirements. In 2014, BOTEC Analysis delivered governance recommendations to the Washington State Liquor and Cannabis Board (WSLCB). This single piece of intelligence has had a ripple effect on the interfaces of all providers: BioTrack, LeafData, METRC, and OpenTHC. For instance, Product Type conventions resiliently remain close to BOTEC’s original definition in every State. This underscores the importance of understanding the procurement process. For early Washington vendors, BOTEC’s recommendations were a crucial clue as to what success looked like to the State. It gave vendors the ability to dazzle the State with data dashboards, but hid the architecture that kept the State from meeting their legal obligations.

The hidden complexity behind the procurement process is the technical requirements. Everything that is not about the project subject matter in the requirements is going to be about the government’s needs. From a technical standpoint, it will be about an underlying specification. The government understands the value common specifications have. It is harder to internalize faith in a “new” specification when it does not come from an institution they already respect. 80% of this responsibility falls to actors like the OpenTHC Foundation. Abusing the Pareto Principle, the “other 80%” belongs to the bidding vendors. A straight line exists between formal requirements, the OpenTHC specification, and the code. Any response should demonstrate mastery of those connections. Anything less may lead to an erosion of trust. In the age of A.I., having the ability to demonstrate you are an expert shapes how trust will develop.

Our team continues to learn and study the political process. In alignment with the open source standards of transparency, we publish all responses we make to procurement bids. Our process writing these bids has been iterative. The quality and organization in our most recent responses are worth your time. We mark our responses with a Public Domain license. Our sincere hope is that more entrepreneurs use our responses as a baseline for their own submissions. OpenTHC is committed to continuing to elevate open source technology for the public benefit.

Organize

Find business allies (partners, subcontractors, technology)
Find cannabis stakeholders
Lobby everyone

Earlier you tolerated our musing on “standing on the shoulders of giants.” It can be helpful to remember how relative that can be. The shoulders we stand on also take advantage of another giant. Necessarily, there is always some bigger giant. Before I had formally joined OpenTHC, I met one of David Busby’s mentors, John Sechrest. John is important to our story because he helped inform us that finding the right business partner carries the same peril as finding a life partner. Partnering with the right people will have a huge impact on your business.

Your team has to understand the industry thoroughly. They also need to be the kinds of people that are patient and empathetic to a broad range of end-users. Cannabis stakeholders can be anyone. State government employees, medical patients, salt-of-the-earth farmers, and multi-Ph.D holding scientists. A comprehensive contract should account for these different needs. Furthermore, people that handle their compliance paperwork must understand those needs.

This adds a lot of responsibilities to all levels of your organization. A cash-strapped startup may have trouble finding talent that can perform under that uncertainty. Silicon Valley popularized venture-funded models. These models tend to distract leadership from the core business activities as they chase funding rounds. Additionally, employees take on increased risk because an exit event is unlikely. There is plenty to discuss about the risks associated with traditional business models. In cash-lean scenarios, we envision cooperative business models as a pathway to success.

Procurement projects often allow the use of subcontractors. Subcontracting is a valid way to offset the project's complexity. In exchange, you buy subject matter expertise. In the United States, many state governments provide lists of verified contractors. This may include special classifications like: minority-owned, veteran-owned, or women-owned businesses. None of these special classes necessarily make one contractor better over another, but may add value based on the political or process needs of a state.

Remember, the long history of the industry has been inequitable to many groups. Both throughout prohibition and following commercialization. Government stakeholders have awareness of these kinds of problems. Government bodies sometimes require the use of these verified contractors. Again, we emphasize the importance of finding the right business partner to strengthen your organization. The competitors in this sector are likely to be well-funded pharmaceutical industry players. Position your business to officials as a different organization.

Being seen as an expert by the government means being comfortable talking to them. The cultural baggage behind the word "Lobbying" is out of our scope. Yet, it covers the entire spectrum of activity people use to influence government. Procurement projects typically have strict anti-corruption requirements. This is to protect the evaluation committee from bad-faith, and uncompetitive influence. You need to firmly understand the project's competitive requirements. Competitive and good-faith lobbying strategies seek to influence "higher order" government decision makers. Here are a few high-level strategies already in-play in the cannabis industry:

The first strategy is direct communication with decision makers. These are highly-underused civic activities. Things like making public comments during board meetings, attending meet-and-greets with relevant officials, and writing letters. This level of lobbying is high-effort and low-impact when your contract may not be handled by the officials directly. However, the face-time builds visibility and familiarity with the members of government, giving a face and voice to your company that can help down the road.

Another approach is joining professional organizations that lobby and organize based on shared stakeholdership. Think organizations like local craft cannabis groups, to cannabis advocacy groups, like NORML. The ideological spectrum of these organizations can be broad. Choosing your affiliation is just as important as your choice of business partners. Many of these organizations will take on a broader agenda than the topics you wish to influence. Once publicly aligned with an organization your staff should be prepared to support, or at least acknowledge these other topics. These organizations can hold enormous potential as political incubators once you’re affiliated with them.

The final lobbying scheme takes everything we discussed to a logical conclusion. Equipped with enough capital, willing competitors can target and influence government decision makers. Leveraging professional lobbying firms, competitors will nudge lawmakers to cement the law and project requirements in their favor.

Affiliate with organizations that take stewardship of the plant, consumers, and patients seriously. Stewardship is not a marketing campaign that has a fixed budget. Real stewardship to fellow stakeholders must be elevated to an organizational value. Just like the importance of OpenTHC's stewardship as an open source participant. This is the tried-and-true pathway towards making inroads with cannabis stakeholders.

Execute

Identify and respond to the procurement process
Win the bid
Orchestrate the solutions and technology

In many ways, this is the most straightforward part of the journey. Not because the work is easy, but because studying and organizing will clarify what you need to do. Submitting our first bid felt akin to finishing a heavy load of final exams. But, it is the stage where everything theoretical we have discussed finally becomes concrete. Identify where the committee hosts the bid announcement and documents. Unfortunately many U.S. procurement systems are these fairly byzantine websites only suitable for sending email notifications and hosting Microsoft Word documents. Sometimes states have more than one. We have documented each state's profile on our Wiki and add new profiles of other governments as we learn about them.

Bid responses generally require more than one document. You must study the bid notice and compile a list of all required documents. This will help you strategize the response. Ensure identification of all required responses. Failure to include a single required response document may disqualify your bid as non-responsive. The evaluation committee has a lot of discretion in these judgements.

Generally, "marketing materials" from your business may accompany required response documents. This is an enormous opportunity to speak directly to decision makers. There are many creative strategies to take in this direction. We’ve refined the response prose our team has work-shopped throughout our bid career, and we welcome your team to it. A response should outline the unique vision you and your team have. It is an opportunity to highlight your subject matter expertise, and your community. The prose should describe the system as the government would see it. Use their vocabulary to describe it. This document should also have high-level delivery timetables, and a section called "Requirements." Delivery timetables are centered around the time after the contract is signed. Delivering the core project outside of a 90-day timeframe is generally seen as a non-starter by evaluation committees. However, include support windows and non-core deliverables in your timetable. The requirements section should re-acknowledge everything important the bid response requires. For instance, many bids require a technical requirements spreadsheet included in the response. This prose section would acknowledge that your bid includes that document, and meets those requirements.

Procurement committees generally use a scoring rubric on vendor bids. Committees may invite bidders to present their vision if you score high enough. This face-to-face opportunity gives teams an opportunity to put a voice to vision. By using OpenTHC's Docker environment you can demo a system ready to go off of the shelf. Prioritize two goals for the evaluation: appearing professional, and de-risking your bid.

It is uncertain how long it will take for the committee to reach a conclusion. Sometimes bids never materialize into a contract because government priorities change. Generally, governments follow the same business cycles everyone else does. You can expect a decision somewhere between 90 days, to the end of their fiscal year. The accepted bid's team can look forward to a contract negotiation period of about 30 days. Whether you win the contract or not, just by submitting a professional bid you have entered the arena. You are now a serious contender. Get your demo online, and be ready for their call or email.

A Call for Champions

So much has changed since I joined the industry. What once felt like a party early on in my career exchanged for the everyday and familiar. For years, OpenTHC has dedicated ourselves to this work. Our crew did not build just another product. We built a public utility. Every line of code, every bid document published, and every conversation we had emerged from the primary goal of adding value to our shared community. This foundation exists for you. It exists for everyone. Our team has carried this torch to the base of the mountain. We can see the summit. But, the truth is that we can only go so far. So much has changed in our community. It is hard to say what the summit will look like tomorrow. The final ascent to see an open system used for the public good may require a new team with fresh energy to take the lead.

The right champion of transparency in technology sees us, and understands this is more than another business opportunity. The real goal isn’t the contract itself. It’s about making the government more transparent. It’s about giving people the tools they need to fix their own problems. It’s about stewardship.

So, this is our call to you: Take what we’ve built and make a run for it. Submit a bid. You may not win, and that’s okay. The reality is that changing government is incredibly hard. Every bid which centers on collaborative, open source technology is a victory. By submitting a professional bid, you force a conversation about transparency. You add to the public library of knowledge. You show your government what is possible. You embody the long legacy of open source. Every step adds value to our community. This is a success we would proudly share after having carried the torch with love and effort. Please, help us carry it further.

How to file FOIA / PRR in the US

Matthew Walther — Mon, 08 May 2023 15:11:51 +0000

Freedom Of Information Act (FOIA) and Public Records Requests (PR/PRR) are the workflows by which Public and Government agencies in the United States releases information to the public and press. The role of this document is to show the reader how to investigate an institution's PRR process, and how to execute a novel request for information.

Government Systems of Records

Systems of Records are the artifacts created by technical systems or personnel operations ran by a Government agency.
Typically, lists of systems of records are published by agencies.

Examples:

Discovering the FOIA/PRR Workflow

State of Massachusetts example

General guidance https://www.mass.gov/info-details/massachusetts-law-about-freedom-of-information-and-public-records
Secretary of State (SoS) guidance https://www.sec.state.ma.us/divisions/public-records/public-records-law/public-records-request.htm
A-Z State Organizations https://www.mass.gov/info-details/massachusetts-state-organizations-a-to-z
Fee Schedule https://www.mass.gov/files/2017-06/Public%20Records%20Law.pdf
- FAQ: "What is the cost for copies of public records; what about electronic records?"

Email Draft using SoS template

Commission@CCCMass.Com
Records@cccmass.com

Re: Massachusetts Public Records Request

Dear Cannabis Control Commission:

This is a request under the Massachusetts Public Records Law (M. G. L. Chapter 66, Section 10). I am requesting that I be provided a copy of the following records:

All responses and bid records related to Bid Solicitation ABC-123

All responses and bid records related to Bid Solicitation XYZ-987

All Commission emails and any correspondence containing any of the following terms:

"foo"

"bar"

"baz"

I recognize that you may charge reasonable costs for copies, as well as for personnel time needed to comply with this request.

The Public Records Law requires you to provide me with a written response within 10 business days. If you cannot comply with my request, you are statutorily required to provide an explanation in writing.

Sincerely,

Making a FOIA/PR Request

1. Email draft

Some governments provide their own template for initiating the PRR process. This is helpful to us because it helps the responding agents understand our request easily. If guidance is not available, other resources like the SPLC Letter generator or AI tools can help us create an initial draft.

2. Discover the contacts you should be communicating with

Some governments and agencies provide a specific email for handling PRR workflows. This contact information can be found in several places like contact pages on agency websites, on higher-order governing agencies' directories, and agency contact directories.

3. Be follow-up ready

Agencies often have a minimum timeframe to respond to any given request. Staffing issues can make these windows harder for agencies to make consistently.
It is advantageous to include these timeframes in your request, and reference supporting statute, code, or law.

Non-statute example:

Please reply within the time allowed by law with the estimated timeframe in which this information can be released.

There can be many reasons why a request is denied, even if information held by a public actor. One common factor that would make a responding agent to lean toward denying a request is a high upfront charge the agency would be required to leverage in order to recuperate labor costs.
When a request is denied, responding agents typically will give some reason. Complete requests can extract more value from these types of responces by asking for detailed denial reasoning, and information on appealing the denial in the original request.

Example:

If this request is denied, please issue a formal denial letter within the timeframe allowed by law, the reason(s) this request has been denied, and the official appeals process

"Copying Charges" are sometimes the term referred to that agencies leverage to recuperate public labor expenses from requests that have a burdonsome process to put together. Many states have a program for private citizens to lower to eliminate the cost.

Example:

If there are any exceptions that allow for free or reduced cost copies, please advise on the process for meeting these exceptions.

Resources

https://splc.org/lettergenerator/

Reading

Retrospective: Making a contribution to n8n

Matthew Walther — Wed, 09 Nov 2022 15:04:10 +0000

Contextual Material

Source Code: n8n-io/n8n
Pull Request: n8n-io/n8n#2687

In March of this year, I was working on a Workflow in n8n, and needed to search a Mattermost team for available channels. When I tried to use the existing Mattermost for that n8n supports, I discovered the function that covers the Channel Search API method was missing.

I was able to cobble together a HTTP Request node by duplicating my Mattermost credentials as HTTP credentials and tailoring the request against the Mattermost API's requirements as a work around.
But when I was done, I felt contributing to n8n's Mattermost node would be a rewarding entry point into developing in the ecosystem.

I could not find an engineering or business argument against adding to the Mattermost node, or, against filling-out a Node's capabilities on n8n's public channels. There were a few places I checked for expression of these opinions from n8n members:

n8n documentation
n8n Community forums
GitHub Issues and PRs

Picture: When in Rome, plug and chug? Source: craiyon.com

I was concerned that the risk appetite of n8n - The Business may prevent them from more fully filling out a node's capabilities against a given service's API. What satisfied my concerns were a professionally ran and friendly community in the Pull Requests that happily accepted contributions to nodes. So, I set off ready to cut some code.

I did not give myself much time to work on this, and intended to finish it quickly.
I am mostly a PHP developer, and have not stayed fully up-to-date with many of the developments in the JavaScript/NodeJS/ECMA ecosystem. However, I knew colloquially from listening to my peers that the way many of these frameworks work is by defining modules in specific file paths in the project, and filling in specific files with the right interface. The "Plug and chug" analogy comes to mind, but this time we are chugging code. This is advantageous for someone trying to work fast and format the work according to to the maintainer's conventions and expectations.

I begin to observe the code relevant to the Mattermost module.

I used the trusty ack-grep and discovered where Mattermost was mentioned in the code.

Picture: Output from ack -i mattermost in the root of the n8n project directory

This lead me to the directory packages/nodes-base/nodes/Mattermost. After reading a lot of code in there, I also found packages/nodes-base/nodes/Mattermost/v1/actions/Interfaces.ts which mentioned the actions and capabilities I had found in the existing node as MattermostMap. Digging further into the original directory, I found the packages/nodes-base/nodes/Mattermost/v1/actions/channel directory.

I based the Search capability on the example that the Create capability set. Create is responsible for creating Channels in the Mattermost Node, and both of these methods in the Mattermost API make POST HTTP requests to the target Mattermost environment.

Narrowing all this down, we are left with the following files to consider:

packages/nodes-base/nodes/Mattermost/v1/actions/channel/create/description.ts
packages/nodes-base/nodes/Mattermost/v1/actions/channel/create/execute.ts
packages/nodes-base/nodes/Mattermost/v1/actions/channel/create/index.ts

And I copied them into a new directory:
packages/nodes-base/nodes/Mattermost/v1/actions/channel/search
I appended the channel key in the MattermostMap variable Interfaces.ts with the search capability.

packages/nodes-base/nodes/Mattermost/v1/actions/channel/search/description.ts is the ChannelProperties data structure that defines the User-Interface form-field options for a particular capability. We can find the Search API requirements from the Mattermost API documentation.

The Search API method has two required parameters. A team ID in the URL part of the request, and a search term in the request body.
Fortunately, the Create capability also requires the Team ID. All that is needed to clean up is the references to ’create’ to ’search’.

The search term is a required free-form text field. The closest example Create has is the “Name” option. References to ’create’ are changed to ’search’.

There are probably other more appropriate options for other parameters like displayName, placeholder, default, and description. I tried to keep the choices I made as generic as possible, but I do not have a good reference for why I made the choices I made for these parameters. In retrospect, I would opt to copy the text directly from the Mattermost API documentation.

All of this work takes me to the first commit in the pull request, aeb0cf1.
This commit includes some mistakes, including referential errors to components that are within the Create capability’s scope. These mistakes are part of the risk we assume when we profile and stage components the way we did.

Picture: Robot detective will investigate your code Source: craiyon.com

The way we mitigate against these kinds of mistakes is by testing. I had difficulty running n8n’s tests initially. I was blocked on testing early in my investigation of the environment.

I believed I had some problems installing a dependency the dev environment required. However, I was actually blocked on installing a dependency the environment required during build time. These are the kinds of risks we assume when we work in new environments and make too many assumptions on the current environment from prior experience in other environments.

I committed the work and transferred it to a different development environment. But, I soon felt I was working a little too late for a Sunday, and decided to punt until I could try again. Hoping I would still be interested.
I posted the PR with the branch’s intention, and a status message of where I had left off in trying to validate the work met the intention.

The next day I was still thinking about my experience, and I had been searching the web with follow-up questions. I stubbed my toes on n8n’s excellent official documentation pretty quickly, and had answers to build and test n8n’s environment the way the maintainer’s intended.

Having a more “tactile” experience inspecting the n8n UI led to the realization that there were more missing boilerplate components. These components include the newly written data structures and definitions for Search into the scope of the components under the Mattermost Channel API resources. Components like packages/nodes-base/nodes/Mattermost/v1/actions/channel/index.ts feel very much like a Controller in the MVC paradigm. Given TypeScript’s objectives with object orientation, this may be true.

I submitted my changes to the PR, and included a note of what I had done and the principle reason I thought I had pushed my work into acceptability range. After all, it was working on my machine.

Several weeks later the n8n team was able to pick up my branch. They brought it up-to-date with the main n8n branch, and extended the functionality to include API result pagination.

My changes were released in n8n version 0.169.0.

Questions? code@mashio.net

Stock market and crypto tracking with open source tools

Matthew Walther — Wed, 01 Dec 2021 12:14:55 +0000

Executive Overview

This document aims to tour a selection of open source software leveraged for market analysis. The default system samples public cryptocurrency data. The repository, linked below, contains all resources needed to bootstrap this environment.

https://github.com/mashiox/analytics-engine

Requirements

Docker https://docs.docker.com/desktop/
Docker compose https://docs.docker.com/compose/
PostgreSQL https://www.postgresql.org/docs/13/
n8n https://n8n.io/
Metabase https://www.metabase.com/
Git

Basic understanding of these technologies and their fundamental concepts is assumed in the writing of this document.

There are three core requirements for this system. Data storage provided by PostgreSQL, automation by n8n, and dashboard rendering by Metabase.

These services may be deployed on bare-metal servers, virtual machines, and containers. Descriptions of different deployment and installation methods are out of scope for this document. The minimal docker-compose definition, referred to below, comes with several caveats. It is not intended as a production-ready system.

The GitHub repository includes the full docker-compose.yml definition and the minimal version. Q&A support is offered in the repository’s GitHub Issues

#
# Minimal Analytics Engine
# Author: Matthew Walther <code@mashio.net>
#
version: "3.7"
services:
  sql:
    container_name: sql
    image: postgres:13
    volumes:
      - ./sql/initdb.d/:/docker-entrypoint-initdb.d/
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres

  metabase:
    container_name: metabase
    image: metabase/metabase:latest
    ports:
      - "127.0.0.1:3000:3000"
    environment:
      - MB_JETTY_PORT=3000
      - MB_DB_TYPE=postgres
      - MB_DB_HOST=sql
      - MB_DB_DBNAME=metabase
      - MB_DB_USER=postgres
      - MB_DB_PASS=postgres
      - MB_DB_PORT=5432
      - MB_EMOJI_IN_LOGS=false
    depends_on:
      - sql

  n8n:
    container_name: n8n
    image: n8nio/n8n:latest
    ports:
      - "127.0.0.1:5678:5678"
    volumes:
      - ./n8n:/home/node/.n8n
    environment:
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=sql
      - DB_POSTGRESDB_DATABASE=n8n
      - DB_POSTGRESDB_USER=postgres
      - DB_POSTGRESDB_PASSWORD=postgres
    depends_on:
      - sql

Activating Services

Steps

These steps will clone the required code and docker files to start the analytics engine.

PostgreSQL will start before the other services. It will migrate the SQL definitions located in the sql/ directory of the repository. These files define database, table, and function definitions that the environment's services and features require.

After the sql container finishes the first-time run procedure it will begin waiting for connections. At this point, n8n and metabase will begin migrating their schemas to the database.

git clone https://github.com/mashiox/analytics-engine.git /opt/mashiox/analytics-engine

cd /opt/mashiox/analytics-engine

docker-compose pull

docker-compose up -d

PostgreSQL

PostgreSQL is a powerful database engine. The chief goal is to use it to store market data, and perform advanced calculations when the Metabase analytics engine can not help us.

Health Check

Here is how to ensure PostgreSQL has all the necessary definitions for the environment of services.

docker-compose exec sql su - postgres

psql

Check that the tables: n8n, metabase, and finance exist and are owned by the postgres user defined in the docker-compose.yml file.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 finance   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 metabase  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 n8n       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(6 rows)

Note: The metabase service takes the longest time to migrate. The logs will output INFO db.data-migrations :: Finished running data migrations. when it finishes.

\c metabase

postgres=# \c metabase
You are now connected to database "metabase" as user "postgres".

metabase=# \dt
                        List of relations
 Schema |                 Name                 | Type  |  Owner
--------+--------------------------------------+-------+---------
 public | activity                             | table | postgres
 public | card_label                           | table | postgres
 public | collection                           | table | postgres
 public | collection_permission_graph_revision | table | postgres
 public | computation_job                      | table | postgres
...
<cutoff for brevity>

metabase=# \c n8n
You are now connected to database "n8n" as user "postgres".

n8n=# \dt
               List of relations
 Schema |        Name        | Type  |  Owner
--------+--------------------+-------+----------
 public | credentials_entity | table | postgres
 public | execution_entity   | table | postgres
 public | migrations         | table | postgres
 public | tag_entity         | table | postgres
 public | webhook_entity     | table | postgres
 public | workflow_entity    | table | postgres
 public | workflows_tags     | table | postgres
(7 rows)


\c finance

finance=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | equities | table | postgres
(1 row)

finance=# \d+ equities
                                                 Table "public.equities"
  Column   |           Type           | Collation | Nullable |      Default      | Storage  | Stats target | Description
-----------+--------------------------+-----------+----------+-------------------+----------+--------------+-------------
 id        | uuid                     |           | not null | gen_random_uuid() | plain    |              |
 symbol    | character varying(36)    |           | not null |                   | extended |              |
 ts_create | timestamp with time zone |           | not null | now()             | plain    |              |
 price     | numeric(16,4)            |           |          |                   | main     |              |
 meta      | jsonb                    |           |          |                   | extended |              |
Indexes:
    "equities_pkey" PRIMARY KEY, btree (id)
Access method: heap

n8n

https://docs.n8n.io/

https://docs.n8n.io/credentials/postgres/#using-database-connection

https://docs.n8n.io/getting-started/key-components/editor-ui.html#workflows

n8n is an enterprise-grade automation platform, capable of handling a broad range of tasks. Our goal is to use it to poll a web resource for live market data, and persist the results to PostgreSQL.

The first step is to configure the service for use. The docker-compose file gave the n8n system the credentials needed to store system data in the n8n database. n8n’s web interface also requires its own database connection configuration to the finance database.

The default docker-compose configuration provided in the minimal configuration makes http://127.0.0.1:5678 available to connections on the docker host.

Add a new Credential to n8n for the finance Postgres database. And example using the default minimal configuration is given in Figure n8n00.

Figure n8n00

Now we can build a n8n workflow to poll Coincap.io for their listed BTC price, and save it to the database. In n8n, click “Upload a file” under the Workflow menu. Upload the BTC_Poll.json file found in the n8n directory of the repository.

Click the “Postgres” tile on the workflow board. Change the “Credential for Postgres” option to the new Credentials to connect to the finance database.

Clicking “Execute Workflow” will demonstrate a successful execution in n8n. You may check the results of the insert in the database. Name the workflow, and this enables the workflow status. This workflow marked active will poll and save the market BTC price every 5 minutes.

Note: This is not the only way to get data into the database. This workflow only polls the live environment for new data. More work is needed to organize and insert historic data for the equities and their value.

Metabase

https://www.metabase.com/docs/latest/

https://www.metabase.com/docs/latest/users-guide/04-asking-questions.html

https://www.metabase.com/docs/latest/users-guide/custom-questions.html

https://www.metabase.com/docs/latest/users-guide/07-dashboards.html

Metabase is an analytics tool that features a user-friendly UI for analytics and report building. Our goal is to use its native functions to build simple reports on market data.

The default docker-compose definition makes metabase available at http://127.0.0.1:3000. On the initial run, Metabase will guide the user through the onboarding process. Step 3, “Add your data” gives the user the opportunity to connect to the finance database. This step is like the one encountered on n8n to add Credentials.

Metabase makes graphs based on a concept called “Questions.” Questions are Metabase’s way of abstracting database queries. Often, Metabase’s “Custom Question” feature is enough to guide the user through the process of filtering data and rendering it into a graph.

Figure Metabase01

Additionally, Questions may be native SQL queries. Native SQL queries may take advantage of PostgreSQL's advanced tools.

Metabase features Dashboards, which will render several results from saved Questions. Dashboards have a broad range of visualization customization. Dashboards can render more advanced artifacts of question, like linear regressions, and overlaid graphs.

Figure Metabase03

Custom Analysis

https://www.postgresql.org/docs/13/tutorial-views.html

https://www.postgresql.org/docs/13/functions-aggregate.html

PostgreSQL Functions provide a method to perform calculations on a data source. In a way, this is like what Metabase is doing in Figure Metabase02. Postgres not only provides more advanced functions, but gives users flexible languages options.

A common method for sampling the average price of an equity is the Exponential Moving Average (EMA) function. The EMA function is similar to the result of a regression function in the sense that it is a curve-fitting algorithm. Our goal will be to build a graph that visualizes the prior 200-day’s BTC price, and an EMA fit of the underlying BTC result.

To do this, Steve Haslam’s implementation of the EMA function written in PL/pgSQL is included in sql/02-finance.sql. This function is loaded into the finance database, and can be called from Metabase using the “Native Query” Question feature.

Figure Metabase02

SELECT date_trunc('DAY', ts_create) as time
    , ema(price, 2) AS price
FROM equities
WHERE symbol = 'BTC'
    AND ts_create between now() - interval '200 days' and now()
GROUP BY date_trunc('DAY', ts_create)
ORDER BY time DESC

Note the parameters used in this particular aggregation are: α=2, Days=200

Create a new Dashboard and add the 200-day BTC price question found in Figure Metabase01. Resize according to personal taste. Click the “Add Series” button found when mousing over the chart on the Dashboard. Find and add the 200-day EMA aggregation, and click done.

Figure Metabase04

Closing Thoughts

We have demonstrated a system which polls live BTC data, and analyzes the results. It is flexible enough to poll and analyze any market equity. Indeed, this system is capable of tackling many numerical analysis problems.

PostgreSQL and n8n carry two features that may make this system desirable to a broad range of users. Users may define Postgres functions in the user’s language of choice. Depending on the nature of the numeric problem, using the right tools may make the implementation better or worse for a lot of reasons.

n8n allows users to run arbitrary commands on the system. This makes n8n carry clear risks for the host system. Those risks may be mitigated by dialing in the system and application environments to isolate the range of damage it may do.

A benefit of this feature is that it may execute application scripts. This enables the user to configure n8n to perform any task that any other component in this system is not suited for. These applications may be built in any environment the user desires, and that can be executed in a POSIX environment.

It should also be said that this environment is minimal, and may not be enough tools for an analyst or data scientist to meet all their goals. There are several other open source tools that may help produce data insights:

A spreadsheet engine, i.e. LibreOffice Calc
Jupyter Notebook
GNU Octave

Looking ahead, there is more to this environment than meets the eye. n8n is a deceivingly powerful tool. Leveraged correctly it can deliver qualitative analytics on top of the quantitative analytics discussed in this document. A system constructed as such could deliver insights previously inaccessible to most public audiences.

This post originally published on Mashio.NET, "Getting Started with Open Source Market Analysis".

Cloud Providers Considered Harmful Part 1

Matthew Walther — Mon, 02 Mar 2020 00:00:00 +0000

The Ad Business Model

Cloud providers, like Google, Facebook, Amazon, and Apple wield an incredible amount of control on their user's personal data. Most times they behave as though they actually do own their user's data, and they engage in practices to track them even using anonymous settings. They do this so they can run the most amount of user data in an algorithm that gives them behavioral results. After they have these analytics, they sell this knowledge in the form of advertising.

Their goal is to make targeted advertising cheaper, and this means making it easy to access user data. I've written about the control cloud providers have over access to essential services. Now, we'll explore the dangers they create under the advertising business model.

All of this is about showing you ads

All cloud providers use bulk data from their collective of users on their platform for a lot of reasons. One lucrative case is showing targeted advertising to individuals. Sometimes, this business model works so well, there is no charge for access to the service.

The ad model is so effective because it relies on a lot of details from your personal life and habits. Cloud providers are able to get this information from users on their apps giving it to them. They need to have a lot of data from enough users. Once they do, they process the data into behavioral analytics.

How ads are bought and sold

The analytics shows which topics, products, and messages someone would most likely respond to. A common example of this at work is grocery stores buying this kind of advertising from an ad network to target pregnant women[1].

First, a grocery store customer uses a search engine to look up diapers. The search engine keeps track of everything the user searches for, and what they click on. Since this hypothetical person also uses the search engine's email service, it knows enough about them to fit them into several categories. As the ad algorithm processes the users, it assumes the grocery store customer fits in the parental category.

Second, the grocery store buys advertising from the cloud provider. The grocery store tells the provider what their customer's demographics are.

Last, the provider uses their analytics and the store's demographics to decide which users to show ads to. The issue is that this example is simplistic, and a traditional view of advertising. It does not show the risks of what is happening behind the scenes. Targeted advertising is highly effective at its goal. But, there are very few guidelines for using it, and no user protections.

Digital ad networks have already had a severe impact on a large population of people. In 2016, Facebook Ads had a heavy influence on the US Presidential election[2]. Google puts a unique and permanent tracking code on all computers and phones on the installation of Google Chrome. Google uses the tracking code to record all Chrome browsing history[3]. Because people use Facebook and Google for so many different things, providers have high fidelity data about people. This is data about people's real lives, business, and their internet habits. It is the core data that powers their behavioral analytics. Facebook has even gone so far to create "shadow" or "dark profiles" for people who do not, or have ever, used their apps. The goal behind their shadow profiles initiative is to optimize the way the ad algorithm shows ads[4].

How this affects you

Cloud providers combine their user's data together. Using this set they create new data and analytics about groups of people. Advertisers pay for access to various categories from some of the groups. Cloud providers and advertisers then work together to figure out which messages are most effective to create a reaction from a person[5]. There are few protections from bad actors spreading harmful messages on an ad. This makes it difficult to say what any ad is trying to draw someone toward.

None of this may be surprising to some people. The advertising industry has worked to better understand its audience since it began running ads. These days, the way they do that is by using a huge data set that includes people's personal details. This data includes sensitive information that in other contexts receive special privacy protections. HIPAA gives companies that work with sensitive information clear guidelines about what they can do with health data. Bad actors are already using ad technology to undermine democratic proceedings. It's difficult to say where other attacks have happened, or how to gauge their success. What is clear is that the tools to create misinformation is in the cloud provider's hands. The power of those tools comes from people's personal data. and nothing is being done to protect people from misinformation.

This post originally published on Mashio.NET.
Subscribe to Matthew on twitter.com to get updates on privacy, technology, and the cannabis industry.

References

[1] How Companies Learn Your Secrets http://archive.is/Wibtv

[2] Facebook and Cambridge Analytica: What You Need to Know as Fallout Widens https://www.nytimes.com/2018/03/19/technology/facebook-cambridge-analytica-explained.html

[3] Is Chrome really secretly stalking you across Google sites using per-install ID numbers? We reveal the truth https://www.theregister.co.uk/2020/02/05/google_chrome_id_numbers/

[4] Shadow profiles: Facebook has information you didn't hand over https://www.cnet.com/news/shadow-profiles-facebook-has-information-you-didnt-hand-over/

[5] This is how Facebook uses your data for ad targeting https://www.vox.com/2018/4/11/17177842/facebook-advertising-ads-explained-mark-zuckerberg

Cloud Providers are a danger to Individual Autonomy

Matthew Walther — Thu, 06 Feb 2020 18:15:09 +0000

Cloud providers pose an immediate danger to the end-users, and the organizations that rely on their uninterrupted use. People and businesses organize themselves in many unique ways, but many features are common to their goals. Access to services like email, file-sharing, and maintaining backups are as critical as transportation, housing, and food.

Providers like Google, Apple, Amazon, and Microsoft can, and have, cut people off from the data and services that they rely on to run their lives, businesses, and communities. This circumstance often leaves people with no recourse for recovery.

Withholding access to files and data that people own can have disastrous consequences. Many people use cloud providers to store a digital archive of their entire lives. The things people save is broad. This archive can range from family photos, tax records, banking information, and crucial communications.

Users are at their most vulnerable when cloud providers disrupt user's access to their accounts. When crucial information is unavailable to people that need it, it becomes difficult to work on the tasks they need to finish. A blocked individual is not able to communicate with the other people they need to reach. This is how autonomy breaks down, and underscores the risk to individual autonomy cloud providers pose.

There are a range of reasons providers can cut off access to your data. The reasons vary between legal obligations that providers have to obey under warrant.[1] But their reasoning can be a lot more arbitrary. Shutting down an account for breaking Terms of Service agreements due to a mistake made by the user,[2] or the user's name failing to meet the cloud provider's "real-name standards"[3] are some examples that have happened in the past. Google’s zealous pursuit of profile fraud has locked some people out when they have simply experienced problems with their payment methods.[4] With selective means of recovery and no oversight, chances of recovery are slim.

When cloud providers suspend users, they become the judge, jury, and executioner on whether the user will ever be able to access that data again. There is no government oversight or bill of rights that give users any protections here. Cloud providers also claim some level of ownership of the data when you upload it to their servers, violating user’s personal privacy. This affords them the position that an individual never owned their data, to begin with.

To mitigate the looming risk, people must own copies of their data, and they must be able to access it without the need for a cloud provider. However you calculate the risk factor that cloud providers introduce into your life or business, what remains clear: data ownership ensures resilient autonomy.

References

[1] Your account is disabled https://support.google.com/accounts/answer/40695

[2] Tips On How Not To Get Your AdSense Account Banned https://www.shoutmeloud.com/10-simple-mistake-which-violate-google-adsense-policies-and-get-banned.html

[3] Facebook real-name policy controversy https://en.wikipedia.org/wiki/Facebook_real-name_policy_controversy

[4] Why I Can No Longer Recommend Google Fi https://onemileatatime.com/google-fi-review/

This post originally published on Mashio.NET.
Subscribe to Matthew on twitter.com to get updates on privacy, technology, and the cannabis industry.

So you wanna build software in cannabis

Matthew Walther — Fri, 02 Aug 2019 15:05:35 +0000

Technology problems that cannabis producers and retailers ("licensees") care about aren’t that complicated. Anyone that has so much as worked at a store may already be familiar with the fundamentals. That is, moving and keeping track of inventory from a seller to a buyer. Assets must get tracked somehow. On top of this, different governments have different requirements that impact cannabis licensees in many unique ways.

So why aren't we seeing more technology innovation in this space? If the problems aren't hard, why is innovation hard to come by?

At WeedTraQR we engage with and deploy business solutions for cannabis licensees on a daily basis. We are very familiar with the challenges business stakeholders are having in the cannabis industry. Because of those issues, innovation and diversity are being held back from their true potential in this space.

The goal of this post is to confront and reduce the barriers of entry to making innovative technology in the cannabis space. Without getting lost on each state’s regulatory and business requirements, we'll start with where things stand.

The Status Quo

Cannabis licensees have 4 primary missions: selling their commodity, tracking inventory, tracking money, and tracking payroll. None of these problems are unique to cannabis, they are the bureaucratic drivers of a business.

For a lot of different reasons, many states that have legalized cannabis under medical or adult-use programs have mandated a gradient of requirements on the inventory management problem.

These requirements can range from requiring that the licensee simply own some inventory management system, up to submitting its movement through physical and logical zones. In some cases, unique and expensive RFID tags and used to associate the production lifecycle of batches of inventory. Each state also has Quality Assurance requirements on cannabis commodities, too, but there is no singular golden standard that distinguishes good QA and safe product.

If we ignore what some folks call traceability or “seed-to-sale” tracking, that still leaves your average entrepreneur with a lot of problems to solve. Businesses still have to complete their other missions and have other unique technology needs that will keep many unbalanced engineers in their offices late.

Yet, traceability is exactly the thing holding participants in the cannabis industry from being able to invest in many of these solutions. There’s only a couple of reasons for this, but they are hard problems.

Closed systems stop business when they break

I have this out-of-body like experience when I'm reporting traceability issues on Twitter. Other open-source maintainers have been in a similar position that being at OpenTHC put me in. But, oftentimes I feel I'm only coming across as a hater on some other small business for an outage. Or, something equally simple because Things Go Wrong™.

The reality is, many of the businesses we serve at WeedTraQR are mandated by their state's requirements to maintain certain levels of tracking in the state's central inventory system. Not simply report where it is. The reasoning from the state's perspective, is that they want transparency. They get to know exactly when money trades hands for their tax purposes, they maintain a repository of quality assurance records relevant to the public health, and they can stay in compliance with the Cole memo's stipulations. Or whatever comes next from Uncle Sam.

There are several ways this distinction blocks business operations. In cases ranging from normal operating conditions to catastrophic downtime, licensees are often blocked from trading their product because the central system cannot accept inventory transfers.

Blind vendor migrations are painful and expensive

Unexpected modification to licensee inventory data is the most frequent blocking case WeedTraQR encounters in Washington state. We have seen businesses forced to adopt new inventory tracking strategies overnight, just to stay within state compliance. Licensees face the enormous business risk of government agencies destroying inventory and hefty fines when non-compliance is discovered.

The most appalling breaking case I’ve personally worked on have been bad mutations to laboratory results by the state’s vendor. This has caused a total loss of faith in what is supposed to be a trusted system for housing data relevant to public health in both businesses and consumers. Additionally, this side-effect has compounded problems related to inventory transfers to another licensee under some conditions; i.e. when passing inventory is wrongly changed to failing.

The “workarounds” that licensees get forced into, go far beyond the operations they do in WeedTraQR, or with any other integrator. They are being forced to make seemingly arbitrary changes to their fundamental mission.

Last month we saw people missing payroll because Washington state’s system continued to flounder after a highly anticipated and well-delayed update. I personally spent a lot of time on the phone with bleeding businesses instead of building features for them. With no real visibility or transparency into the mandated system, integrators only have their unit tests, heuristics, and weekly integrators meetings to interface with their primary upstream vendor.

How can we help fix these problems?

OpenTHC, WeedTraQR, and others are removing gatekeeper-ish competing interfaces with a community-developed standard. We’re basing our work on licensee experience, government needs, and our experience in the industry. We continue to work with all industry stakeholders to discover the right solution.

Having integrated with three closed-source vendors to provide the same value to all of our customers across state lines was an extremely challenging effort. There are even greater challenges to overcome when this industry introduces interstate commerce.

This barrier only prevents more innovation in this new industry. Its tolerated existence will also present continuing diversity challenges.

We believe in order to invite the next level of innovation in the cannabis industry is to create the base level that anyone can start from. But we must start by empowering the core stakeholders with the resiliency they need to grow.

We are proposing the adoption of the OpenTHC API Specifications.

Please consider supporting our cause, and check us out at our website, and our GitHub.