CRM Database Structure

This feature is in active development and may be changed without notice.

Tables List

User

It stores the users for the current domain. Make sure to always wrap the table name in brackets.
For example:
SELECT * FROM [User]; (Notice the brackets around “user”).
It is pretty self-explanatory.
email: The email of the user, unique table.
password: The user’s password, hashed with bcrypt
username: Username, not sure if it is even used.

Customer

This table stores customers, suppliers, employees, and everything else with whom we can make an order. We define the type of current customer by using customerTypeKey.

name: The real, full name of the customer.
email: The email of the customer.
customerTypeKey: Defines the type of customer. References the txtKey of MFlag.

CustomerDetail

Stores any additional details about a customer.

customerId: The ID of the customer, references the id in customer the table.
name: The name of the setting.
value: Value of the setting.

Example:

Select a customer with its salary using Email:

SELECT Customer.name, Customer.email, CustomerDetail.value Salary FROM Customer
LEFT JOIN CustomerDetail ON customer.id = CustomerDetail.customerId
WHERE CustomerDetail.name = 'Salary'
AND Customer.email = '1@gmail.com'

Or a more optimized approach

SELECT customer.name, customer.email, CustomerDetail.value Salary FROM CustomerDetail
LEFT JOIN [Customer] ON [Customer].id = CustomerDetail.customerId
WHERE CustomerDetail.name = 'Salary' AND customerId = 19

Product

This table stores all the products:
name: The user-friendly name of the product.
price: The price of the product (in local currency).
description: Description of the product.
categoryId: The category of the product, references id of category table.
code: Unique ID of the product.

ProductDetails

Stores the details of products, just like the customer details table above:
productId: The id of the product, references the id column of product table.
type: The data type of the products, supports string, int and datetime.
key: The details key
value: The details value.

Internal Tables

These tables should not be accessed directly. They are meant for use in system features and can break the whole system if tampered with.

_prisma_migrations:

Stores the migrations for Prisma.