MySQL, Redis, Vault: how OrbitSoft saves and protects data in the PosiFlora accounting system

Kind of tool

  • Data base MySQL, Redis, Vault

Where we use it

  • System of accounting and sales control in PosiFlora the flower business

Tasks required

  • Keep different types of data separate from each other
  • Protect confidential data from hacking
  • Allow for the execution of complex analytical requests
  • Reserve capacity for when case load increases

Results

  • Data for over 8,500 users securely protected
  • Processes up to 15 million API queries daily, and 10 times that on holidays
  • Saves data even when it’s entered offline
  • Creates different kinds of reports to assist with business development

Flower store income depends on data storage and analysis

PosiFlora is a system of accounting and sales control for the flower business. The majority of customers of PosiFlora are small flower shops.

It’s important for florists to save and properly analyze sales data: which flowers sell better, and which worse, what the average sale total comes to, statistics regarding the average customer, etc. If a mistake is made with the data, the business won’t be able to plan sales and purchases, and will lose money. We tell you how OrbitSoft organized the data storage system for PosiFlora, and how we help streamline and improve the flower business.

Separate data by type, and use different storage for each

There are two ways to store data: put it into one database, or separate it into different databases. Keeping all data in one database is convenient for developers: they need to spend less time updating and searching for the information they need. But this can be unsafe for clients: if you store data for different clients in one database, it’ll be easier to steal; if the database crashes, the system will stop working for all users.

At PosiFlora, the most important thing for us is the reliability of customer data. That’s why we separated data by type, and designed the architecture so that each type is stored separately:

  • Client data is stored in a MySQL database
  • Confidential/sensitive data is stored in a special Vault database
  • Operational data is stored in a Redis database

Some more detail regarding each solution:

MySQL for storing customer data

The biggest group of data we store is data from PosiFlora clients regarding daily work tasks. The data is entered by florist employees, and they can perform different actions based on it.

How MySQL helps address client needs

We chose MySQL for storing customer data for several reasons. MySQL:

  • Allows different types of client data to be kept separated, eliminating the possibility of getting it mixed up.
  • Allows for the creation of complex analytical queries, so that clients can analyze data and plan their work.
  • Is easier for analysts to use, as it understands SQL query language.
  • Data storage slots are easy to expand as more clients are added.
  • Database is free to use, so it economizes on storage resources.

Today, we keep the data of over 8,500 customers safe, and process over 100,000 requests daily.

Vault from HashiCorp for storing sensitive data

In addition to user data, we store sensitive data that needs a higher degree of protection against hacking. For greater security, we store such data in a special database called Vault.

How Vault saves sensitive data

We chose Vault for increased security for the following reasons:

  • Several levels of data protection.
  • Even if the data is downloaded, it will be in a form such as «785kjjhgdwyegnsSJDC3523JLWK». An intruder cannot decrypt it without the key.
  • The key consists of three parts, and is kept by three people.

The database with sensitive data works separately from other systems, substantially increasing security. Even if something happens to the rest of the data, the sensitive data is protected.

Redis for cached data storage

In addition to user data and sensitive data, we store cache data. We store cache data separate from other data, helping maintain higher system speeds.

How Redis helps to speed up the system

We chose Redis because it reacts faster to the opening of a page if the database is given the right address. For example, when a salesperson wants uses the stock tab to check if a desired item is in stock, it takes less than a second. If the operative data were stored in MySQL, output would take longe

Advantages of a distributed storage system

  1. Security. Eliminates the possibility that data can overlap due to code errors. One client’s data will never be available to others. Increased security for sensitive data.
  2. Scalability. Some of the data is stored on one server, some on another. This reduces development costs.
  3. Data is easy to update. If we add new functions, e.g., if we limit access to data for employees, we can try this out on one client, and then later expand this to other clients. If something goes wrong, we’ll notice the error immediately and fix it. This keeps everything running smoothly for the vast majority of clients.
  4. High speed of the system. The accounting system doesn’t freeze while operating, even with an influx of holiday clients.

Technical Block

Frameworks:

  • Back-end: Symfony
  • Web front-end: Angular
  • Mobile/desktop app: Qt

Programming languages:

  • Back-end: PHP 7.3−7.4
  • Web front-end: TypeScript
  • Mobile/desktop app: C++

Libraries:

  • Neomer/JSON:API
  • Doctrine ORM
  • Enqueue
  • League/Flysystem
  • Monolog
  • SwiftMailer
  • Twig

Data Base:

  • MySQL
  • Redis
  • Apache Kafka
  • RabbitMQ
  • Couchbase
  • SQLite
  • ElasticSearch

Platforms and third-party software:

  • Docker, Docker Compose, Docker Swarm
  • Consul, from HashiCorp
  • Vault, from HashiCorp
  • Kafka Connect
  • Graylog
  • Grafana, Graphite, StatsD

Get a free consultation

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store