Skip to content

Direct Database Connection ​

Sync CaoLiao QR codes, form records, statuses and plan data to a database in real time, then build reports with a BI tool or write programs that read the data and connect to other systems. Both options use the same table structure.

Official DatabaseCustom Database
Where it livesAn independent cloud database CaoLiao provides for each accountA MySQL 5.7 database you provide
Read and writeRead-only, cannot be editedCaoLiao creates the tables and fully overwrites them; the tables are maintained by CaoLiao
What you prepareFill in the application formHost, port, database, username and password, with public network access
Best forReports in BI tools, or programs that only read dataKeeping the data in the same database as your other enterprise data

Official Database ​

  • The official database is read-only and cannot be edited.
  • Database type: MySQL 5.7
  • Synchronizes all QR code information, form records, status details, plan completion details, etc., in real time.
  • Data tables are only created when bulk templates and forms are generated with submitted data. Empty templates/forms will not create tables.

How to Enable ​

In the CaoLiao backend navigation bar, go to Data Management > Data API and select Official Database. Fill in the application form and the feature is enabled automatically and starts pushing data. The first push takes 1-3 hours, depending on how much data your account has.

Official database setup

Connection Example ​

plaintext
Type: MySQL5.7
Host: rm-bp1m4fy8d66u3c6xmbo.mysql.rds.aliyuncs.com
Port: 3306
Database: cli_202112111 (example)
Username: cli_202112111 (example)
Password: eiadk289djlgj (example)

Sample Reports ​

If you need to connect to the official database to create custom reports, view sample reports.

Since this involves basic database (SQL) operations and BI tool usage, it requires some technical proficiency. Please ensure you have the necessary skills before proceeding. View report creation tutorial.

file

Custom Database ​

You provide the host/port/database/username/password, and we will push real-time data from your account to this database.

This method will automatically create tables in your database, requiring your database to allow public network access.

  • Data tables are fully overwritten during updates. Partial user-added data in the same table is not supported.
  • Do not create tables in the database with the same names as CaoLiao tables, as they will be identified as CaoLiao tables and fully overwritten.
  • Database type: MySQL 5.7
  • Synchronizes all QR code information, form records, status details, plan completion details, etc., in real time.
  • Tables are only created if there are batch templates or forms with submitted data.
  • If firewall or other access restrictions exist, add the IP whitelist.

Reference: Download MySQL 5.7 Database

How to Enable ​

In the CaoLiao backend navigation bar, go to Data Management > Data API, select Custom Database, and enter your database host, port, and account details.

Custom database setup

Common Field Explanations ​

Note: These fields contain key information primarily used for linking between tables.

Field NameTypeDescriptionExample
code_idintUnique identifier for QR codes54349863
tpl_code_idintUnique identifier for bulk templates
If not generated from a template, tpl_code_id equals code_id
54349863
member_idintUnique identifier for members2091263
auth_idintUnique identifier for form submitters12789054
record_idintUnique identifier for form data16759806

Common Models ​

file

Model 1: Device Status Analysis ​

Link base_codeinfo (QR code basic info table) with code_state (QR code status table) to obtain device status values and update times, then analyze status distribution.

Model 2: Plan Completion Status ​

Left-join code_task_log (plan execution log table) with base_codeinfo (QR code basic info table) to track plan completion status along with corresponding QR code information.

file

Model 3: Bulk Template Analysis ​

Left-join template_XXXXXX (bulk QR code info table) with table_dXX (form data table) and code_state (QR code status table). template_XXXXXX provides variable content details for bulk QR codes.

Model 4: Linking Follow-up Form Records ​

For example, link fault reports with maintenance records to create a complete information chain. Learn about follow-up actions
Left-join table_dxx (fault report table) with table_dxx (maintenance record table) using record IDs and source IDs. Additional follow-up forms can be linked similarly.

file

Field Descriptions for Data Tables ​

1. base_codeinfo - QR Code Basic Info Table ​

Description: Pushed when QR codes exist in the account, including dynamic QR codes and template sub-codes.

Field NameTypeDescriptionExample
code_idintUnique QR code identifier67654543
QR Code NamestringQR code nameFire Hydrant 01
QR Code TypestringTypes: dynamic QR code, template sub-code, template codeTemplate Sub-code
urlstringQR code URLhttp://qr61.cn/o2eikt/q85Q8KL
tpl_code_idintBulk template ID for sub-codes
Non-template sub-codes match code_id
56335353
Template NamestringBulk template name for sub-codes
Non-template sub-codes match QR code name
Fire Hydrant
DirectorystringName of the lowest-level directory containing the QR code (excludes parent directories)Fire Equipment
Tag SetstringGrouping labels for QR codesFire, Equipment

Directory Info: Shows only the lowest-level directory name, excluding parent directories.

2. code_state - QR Code Status Table ​

Description: Pushed when QR codes have associated statuses. View status feature

Historical status data before database activation is not pushed. Contact support for manual synchronization if needed.

Field NameTypeDescriptionExample
code_idintUnique QR code identifier67654543
member_idintUnique member identifier2091263
auth_idintUnique submitter identifier12789054
Update TimedatetimeStatus update time2020-11-20 15:00:00
Example: Device Status_720stringStatus group name ("_720" is an auto-generated suffix to prevent duplicates)Normal
......stringAll status groups under the account are listed hereAbnormal

3. code_state_log - QR Code Status Change Log Table ​

Description: Pushed when QR codes have associated statuses. Records all status changes. Historical data before activation is excluded.

Field NameTypeDescriptionExample
code_idintUnique QR code identifier67654543
member_idintUnique member identifier2091263
auth_idintUnique submitter identifier12789054
Update TimedatetimeStatus update time2020-11-20 15:00:00
Status GroupstringStatus group nameDevice Status
Status ValuestringCurrent status valueNormal
Change MethodstringHow status was changed: via form submission or manual editRecord/Edit
SourcestringFor form submissions, shows form data ID12220988

4. table_dXX - Form Data Detail Table ​

Description: Pushed when form submissions exist. Tables are named "table_d" followed by a form ID (e.g., table_d45). Each form has its own table. View forms feature

file

Field NameTypeDescriptionExample
record_idintUnique form data identifier12220988
code_idintUnique QR code identifier67654543
tpl_code_idintBulk template ID for sub-codes
Non-template sub-codes match code_id
54349863
QR Code NamestringQR code nameFire Hydrant 01
tpl_idintUnique form identifier346636
Form NamestringForm nameFire Hydrant Inspection
Record TimedatetimeForm submission time2020-11-20 15:20:00
StatusstringCurrent record status: normal/deleted/pending review/approved/rejectedNormal/Deleted/Pending Review/Approved/Rejected
member_idintCollaborator identifier2091263
auth_idintSubmitter identifier12789054
Record IDstringForm data IDL1234
Processing StatusstringRecord processing statusPending/Completed/Not Required/Unmarked
SourcestringSubmission method: mobile scan or follow-up actionMobile Scan/Follow-up Note
Source IDstringFor follow-up notes, shows original record IDL1235
Submission IDstringInternal submission ID11
Status Change TimedatetimeProcessing status update time2020-11-20 15:20:00
Example: Device NamestringForm component nameInspection Item 1
......stringAll form components are listed

Form Component Push Notes: ​

  1. Number components exclude units.
  2. Checkbox values: 0=unfilled, 1=checked (√), 2=crossed (×), 3=unselected. Custom labels are not shown.
  3. Checkbox notes and images are excluded.
  4. Location components push three fields: address text, latitude, longitude.
  5. Description components push content under their title.
  6. Section headers are excluded.
  7. ID card components split into multiple fields: ID info (empty), number, name, gender, ethnicity, birthdate, address.

5. base_table_data - Form Data Summary Table ​

Description: Pushed when form submissions exist. Provides a summary of common fields across all form records (e.g., QR code name, submitter, form name).
Use Case: Track all check-in/out records without merging separate form tables.

Field NameTypeDescriptionExample
record_idintUnique form data identifier12220988
code_idintUnique QR code identifier67654543
tpl_code_idintBulk template ID for sub-codes
Non-template sub-codes match code_id
56335353
tpl_idintUnique form identifier346636
QR Code NamestringQR code nameFire Hydrant 01
Form NamestringForm nameFire Hydrant Inspection
Record TimedatetimeSubmission time2020-11-20 12:01:02
SubmitterstringSubmitter nameJohn Doe
member_idintMember identifier2091263
auth_idintSubmitter identifier12789054
StatusstringRecord statusNormal/Deleted/Pending Review/Approved/Rejected
Record IDstringForm data IDL1234

6. template_codeinfo_XXX - Bulk Template Sub-code Info Table ​

Description: Pushed when bulk template sub-codes exist. Shows variable content for bulk-generated QR codes. View bulk generation feature

6.1 Naming Rules: ​

  • Format 1: Suffix "dXX" (d + template ID number).
    Example: Template ID M312 → table name template_codeinfo_D312

file

  • Format 2: Suffix = internal template ID (from URL).

file

6.2 Field Descriptions: ​

Field NameTypeDescriptionExample
code_idintUnique QR code identifier67654543
tpl_code_idintBulk template identifier56335353
StatusstringSub-code deletion statusNormal
...stringVariable content fields from the template

7. record_review_data - Follow-up Actions Table ​

Description: Pushed when follow-up actions or processing status updates exist. View follow-up feature

Field NameTypeDescriptionExample
review_idintUnique follow-up record ID67654543
record_idintLinked form record ID12220988
StatusstringFollow-up record deletion statusNormal/Deleted
SourcestringAction sourceManual Entry/Record Forward/WeCom Forward/Status Update/Form Submission/Notification/Comment Reply
Text ContentstringComment details/status update textUpdated to [Completed]/@John/[Image]...
Key ContentstringUpdated status or linked form record IDCompleted/12220988
AttachmentsstringImages, audio, signatures, etc.
SubmitterstringSubmitter nameJohn
Submission TimedatetimeAction timestamp2020-11-20 15:20:00

8. base_task - Plan Basic Info Table ​

Description: Pushed when plans are created and executed. View plan management

Field NameTypeDescriptionExample
task_idintUnique plan identifier33886
Plan NamestringPlan nameMonthly Fire Extinguisher Inspection
DescriptionstringPlan description

9. code_task_log - Plan Execution Log Table ​

Description: Pushed when plans are executed. Only includes current/future cycles; historical data is excluded.

Link with base_codeinfo or template_XXX to add QR code details.

Field NameTypeDescriptionExample
log_idintUnique task ID239168567
task_idintPlan ID33886
Plan NamestringPlan nameMonthly Fire Extinguisher Inspection
code_idintQR code ID67654543
StatusstringExecution statusOptions:
Completed/Incomplete/Near Deadline/
Overdue Completed/Overdue Incomplete
Start TimedatetimeCycle start time2020-12-10 12:00:00
Execution TimedatetimeStatus update time2020-12-10 13:00:00
DeadlinedatetimeCycle end time2020-12-10 18:00:00
FilteredstringWhether excluded by conditionsYes/No
Change MethodstringHow status was updatedRecord
SourceintFor form submissions, shows record ID12220988
Source ResultstringForm submission resultDevice Normal

10. code_tags - QR Code Grouping Table ​

Description: Pushed when QR codes are grouped. View grouping feature

Field NameTypeDescriptionExample
code_idintQR code ID67654543
Example: Building 1 Fire ExtinguishersintGroup name (1 if QR code belongs to this group)1
Example: Building 2 Fire ExtinguishersintGroup name1
......intAll account groups are listed

11. base_members - Member Info Table ​

Description: Pushed when advanced members are added (basic members excluded). View members feature

Field NameTypeDescriptionExample
member_idintUnique member ID2091263
NamestringMember nameJohn
PhonestringMember phone135xxxx8934

12. base_auth_msg - Submitter Info Table ​

Description: Pushed when forms/status updates are submitted. Shows submitter details. View submitter component

  1. If no form submitted, only shows auth_id.
  2. If form submitted without submitter component, shows auth_id and default name ("WeChat User XXXX").
  3. If submitter components (name/phone/ID/etc.) are used, corresponding fields are populated.
Field NameTypeDescriptionExample
auth_idintUnique submitter ID12789054
NamestringSubmitter nameJane Doe
PhonestringSubmitter phone135xxxx8934
Employee IDstringSubmitter IDD234
License PlatestringVehicle plateZJ-A12342
ID CardstringSubmitter ID number343xxxxxxxxxxxxx

13. record_audit_data - Approval Records Table ​

Description: Pushed when records require approval or are approved/rejected. View approval feature

Field NameTypeDescriptionExample
auth_idintUnique approval ID9001
record

14. base_todo — current to-do items ​

Record-based to-do items sync within the configured form and QR code scope. Each todo_id identifies one row. Later changes update the current state of that row. See the official database guide (Chinese).

15. table_relation — forms linked to QR codes ​

FieldTypeMeaning
idBIGINTAuto-increment primary key
tpl_idBIGINTForm ID
tpl_nameVARCHAR(127)Form name
tpl_numberVARCHAR(127)Form number
code_idBIGINTQR code ID
tpl_code_idBIGINTBatch template QR code ID

FAQs ​

1. Why doesn't the QR code name update in form data tables after the QR code is renamed? ​

QR code names in form data are not updated. To get the latest name, left join the base_codeinfo table on code_id.

2. After renaming a form component, why does the data table have two fields, one before and one after the change? ​

This is expected. The original component field is kept after a component is changed.

3. A plan is set to filter out codes with abnormal status values, but they still appear in the data table. Why? ​

The data table contains all plan task data. The Filtered field shows whether a plan entry was filtered. A filter also only takes effect in the next cycle after the status changes.

4. Why is the pushed data incomplete after enabling the database? ​

After first enabling it, all historical data has to be pushed, which takes about 1-3 hours depending on how much data the account has. If the data is still incomplete after a day, contact online customer service.

5. Why don't database fields update after I edit form fields? ​

Editing a form does not trigger a database update. The data table updates only when form data is added or changed, so submit one new record.

6. How do I connect Power BI to the database? ​

The database is MySQL, so select MySQL as the database type when connecting. See the steps (Chinese)

7. Multiple image URLs in an image component are joined together. How do I get a single image URL? ​

The image component's data format is urls_1,urls_2,..., and the number of URLs varies. Use the following functions to get a single image URL, replacing Photo_233233 with your own field name.

sql
urls_1: SUBSTRING_INDEX(`Photo_233233`, ',', 1)
urls_2: if( LENGTH(`Photo_233233`) - LENGTH(REPLACE(`Photo_233233`, ',', '')) >= 1, SUBSTRING_INDEX(SUBSTRING_INDEX(`Photo_233233`, ',', 2), ',', -1) ,'')
urls_3: if( LENGTH(`Photo_233233`) - LENGTH(REPLACE(`Photo_233233`, ',', '')) >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(`Photo_233233`, ',', 3), ',', -1) ,'')
urls_4: if( LENGTH(`Photo_233233`) - LENGTH(REPLACE(`Photo_233233`, ',', '')) >= 3, SUBSTRING_INDEX(SUBSTRING_INDEX(`Photo_233233`, ',', 4), ',', -1) ,'')

Continue the same way for further URLs.

8. Why isn't data reaching my custom database? ​

The table encoding is a likely cause. Change it to utf8mb4.

9. What if my server uses an IP whitelist? ​

Contact technical support to get the CaoLiao IP range.