(λ (x) (create x) '(knowledge))

Why tkts?

Writing a ticketing system from scratch · Feb 10, 2022

The transition from year to year is always fun, it's a time to reflect on what has been accomplished, and start looking towards what you'd like to do. I happily spent the first couple of weeks of this year (and many nights on and off since then!) rewriting tkts so that it could use an sqlite database instead of a big serialized messy lua table. This is pretty abnormal for me in all honesty, I usually don't do rewrites of my tools. I have a burst of creativity and I direct all of it towards the first viable iteration, and after that I use it. Typically I'm satisfied with what I've made and that's the end of it. With tools like toAPK there's some what of a constant moving goal post to motivate me to tweak and add features, but none of that has ever really applied to tkts.

That's because for me, tkts is kind of special. I started the project late in 2020, got it to a point where it "worked" (kind of), and started using it. I added a couple of features to make it actually usable in early 2021, and then proceeded to use it through out the rest of the year. I tracked everything inside of my little ticketing system, from homelab expansion projects, to Alpine package maintenance, to development work and anything I could think of in between, I'd open a ticket in tkts. If I needed to make note of something, it was a tkts comment away, if I needed to put a dollar amount to work done on something, it was right there inside of tkts. It was a wonderful workflow, and all of it lived inside of this ridiculously messy flat file.

Over time while that system worked and even got extended into this absurd semi-usable multi user system via an IRC bot, I started to feel like the foundation was more and more so built on sand. Not only that, I was hobbling new features in with even more sand. I would be remiss if my silly sandcastle ticketing system fell apart, because I hadn't really been treating it like the ephemeral toy it was designed to be for a long time now. I was doing real work, keeping real data, I'd actually be rather upset if everything came crumbling down now. So that's where we're at now, post massive overhaul I finally feel like we have a decent foundation to build on top of. And the sandcastle seems a little bit more stable, even if I don't think it'd act much like a castle still.

So what's the difference?

SQL. SQL everywhere. The biggest difference is we went from this pile of compacted spaghetti (lua tables serialized via lume), to nice neat efficient SQL.


	{["open"]={["T3"]={["issue"]="[H] - Cleanup Data on NAS",["time"]=0,["log"]={},["desc"]=""},["T4"]={["issue"]="[H] - Investigate NFS share for NAS",["time"]=0,["log"]={},["desc"]=""},["T5"]={["issue"]="[H] - NAS critical docs cloud backup",["time"]=0,["log"]={},["desc"]=""},["T6"]={["issue"]="[A] - Package Gemini Server & Client",["time"]=0,["log"]={[2]="Lucidiot requested that I smile because I am a cool person that keeps distros alive",[1]="Lucidiot requested Jetforce for gemini server and av98 for gemini client packages"},["desc"]=""},["T8"]={["issue"]="[LC] - Rewrite RSS Feed Generator",["time"]=0,["log"]={},["desc"]="RSS needs to comply with spec better, date handling particularly"},["T9"]={["issue"]="[LC] - Rewrite LambdaCreate",["time"]=0,["log"]={},["desc"]="LC docker needs to be stand alone and allow some kind of simplified blog post methodology"},["T2"]={["issue"]="[H] - Organize Photos on NAS",["time"]=0,["log"]={},["desc"]=""},["T7"]={["issue"]="[LC] - Add TLS Cert to LambdaCreate",["time"]=0,["log"]={},["desc"]=""}},["closed"]={["T1"]={["issue"]="[H] - Hardwire NAS to Mikrotik Lab",["time"]=180.0,["log"]={[2]="Mounted Mikrotik lab onto the side of the old desk, we've got an isolated, semi neat, setup for our gigabit lan, gonna call that a success",[1]="Hardwire test went excellently. Wifi pins at 32mb/s MT lan pins at 921mb/s"},["desc"]=""}},["total"]=9}
  

Text files are cool and all, but I can't begin to tell you how impossible that jumble of tables is to deal with longer term. That's an itty bitty snippet from an old backup off my NAS. By the time this file grew to a few hundred tkts it was an utter nightmare. Sure the pretty print functions made it usable, but if I needed to edit a comment, or change some internal data I had to manually edit this jumble in emacs. Needless to say, I didn't most of the time unless it was REALLY important.

At the core though the old .tickets file broke down into 3 parts, a table of "Open" tickets, a table of "Closed" tickets, and a "Total" count of tickets.


{
  total=0,
  open={},
  closed={},
}
  

Each tkt is then its own table inside of the open or closed status table, containing the ticket's issue, description, a record log, and the time spent on the ticket. It's all kind of loosey goosey. The pretty printed table below doesn't look too bad, but the order of named keys change in Lua, so there's very little consistency. And each time the table is re-serialized with data it shuffles everything.


["T6"]={
         ["issue"]="[A] - Package Gemini Server & Client",
         ["time"]=0,
         ["log"]={
                   [2]="Lucidiot requested that I smile because I am a cool person that keeps distros alive",
                   [1]="Lucidiot requested Jetforce for gemini server and av98 for gemini client packages"},
         ["desc"]=""
},
  

Despite those problems our schema is simplistic in nature, and honestly at its core, this is all a ticketing system really is. So long as you can track the state of an issue from start to finish and refer to the steps taken to resolve it, you've got a ticketing system! And the resulting SQL schema tkts is just as simple. Following the same logic we break each ticket into two tables. We store the status, issue title, description, and all of the other background information in the tickets table. And then we log all comments into the record table, and associate them with each ticket based on its tkt_num (id). Functionally, the same exact thing.

Our schema is a little bit more verbose.


CREATE TABLE IF NOT EXISTS tickets (
id INTEGER PRIMARY KEY,
status TEXT CHECK(status IN ('Open', 'Closed')) DEFAULT 'Open' NOT NULL,
title TEXT NOT NULL,
desc TEXT,
client TEXT,
project TEXT,
owner TEXT NOT NULL,
time INTEGER DEFAULT 0 NOT NULL,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS records (
id INTEGER PRIMARY KEY,
tkt_num INTEGER NOT NULL,
log TEXT NOT NULL,
user TEXT NOT NULL,
time INTEGER DEFAULT 0 NOT NULL,
created_on DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (tkt_num) REFERENCES tickets (id) ON DELETE CASCADE);
  

But our data is a lot more organized, easier to extend, modify, and maintain long term.


~|>> sqlite3 -header -column .config/tkts/tkts.db 'select * from tickets where id = 5;'
id  status  title                   desc                 client    project            owner      time  created_on         
--  ------  ----------------------  -------------------  --------  -----------------  ---------  ----  -------------------
5   Open    [H] - Implement Restic  workstation backups  Internal  Disaster Recovery  durrendal  6     2022-01-15 01:49:42

~|>> sqlite3 -header -column .config/tkts/tkts.db 'select id, tkt_num, user, time, created_on from records where tkt_num = 5;'
id  tkt_num log  user       time  created_on         
--  ------- ---  ---------  ----  -------------------
9   5       ...  durrendal  3     2022-01-15 01:51:31
12  5       ...  durrendal  0     2022-01-15 18:08:49
13  5       ...  durrendal  3     2022-01-15 22:05:01
  

And at the end of the day when we look at the data through the lens of tkts itself, we get everything in more or less the expected output, plus a bit of additional context.


~|>> tkts -i 5 -v
Issue: [H] - Implement Restic
Status: Open | Owner: durrendal
Client: Internal | Proj: Disaster Recovery
Desc: 
workstation backups
Time: 6min ($5)

1) {durrendal, 3}
        Installed restic on neurolite (apk add restic) and configured it to push via sftp to Horreum (restric -r sftp:hor:/data/Users/Will/restic-repo init).

Backup seems pretty simple, but will need to consider exclusions:
restic -r sftp:hor:/data/Users/Will/restic-repo --verbose backup --exclude /home/durrendal/.cache/ /home/durrendal/
2) {durrendal, 0}
        Reasonably I think it makes most send to backup these for desktops

/home/user/*
> except for .cache
/etc/wireguard

For the LXC nodes we could include the whole zfs image and lxc config
For the NAS it wouldn't be more than the ZFS config I think
3) {durrendal, 3}
        Successfully backed up the droid, went very smoothly. usage is very specific, but easy to understand.

If I wanted to extend this to include all systems in the house I would need helper scripts and to store a per system db encryption key. Not hard to do though. 
  

By migrating to a robust, better supported, and more common data storage backend it becomes easier for me to extend tkts, and easier for other people to understand what it's doing behind the scenes. Adding new feature functionality can be as simple as adding a new table, linking it to relevant data, and then creating what are in essence views via simple SQL queries. This is after all, more or less, is the way every other ticketing system in existence handles things. At the end of the day ticketing information fits extremely well into the relational data schema that SQL provides.

Next Steps

So that's the core of what has changed. Functionally tkts is the same little ticketing system, but we're building on a reliable foundation now. If you've used tkts in the past you can probably tell at a glance that there's new features already, like ticket & log ownership, better time tracking, and client/project information. I've started adding ways to handle client and company information, and better interactive editing of the existing data, and tkts actually gives returns when it does things, albeit not much, but enough contextual information to know that a change was seen and made.

Because of the change to SQL we now also have the room to explore opportunities to handle remote connections, multiple clients, and so much more. I've personally been rsyncing my tkts.db between multiple systems for offline reference, but when I'm at home I usually mount my ~/.config/tkts directory via NFS, that way all of my system can reference a single shared db file. This paradigm works perfectly fine over sshfs as well. I suppose you could have done this with the lua based .tickets file, but it was by design messier. What we couldn't accomplish (because I sincerely don't want to wrap my head around it) is the possibility of a remote listener, or some sort of sync client for the tkts.db. Since it's all SQL on the backend it's much simpler to translate the values in the sqlite db and modify them into MSQL or PSQL format and inject the data into a centrally housed database (technically we could just used sqlite for this as well). I'm particularly keen on this idea, since most of my use case is syncing multiple systems that I take with me offline, and might have different conflicting states. In the mean time, rsync has sufficed though.

Epilogue

I started writing this post midway through January, and it's obviously gotten a bit away from me. But things have been running smoothly with this newest iteration of tkts.

I've been using it to track Homelab changes and big projects and it has been rock solid. Using it remotely, offline, and between multiple systems has been a breeze. Execution times even on old hardware like my Droid4 have been snappy and responsive. And I feel a little better about the overall data resiliency of the system now. I've ironed out some rough edges that I've found as I use it day to day, and will happily continue to tinker away at it as I work my way towards that 2.0 tag.

If you happen to find tkts interesting and wish to give it a shot, suggest features or need a fix, or just want to chat feel free to reach out! I've gotten some surprising feedback on this project after presenting it at Fennelconf 2021, and really appreciate the constructive criticism and suggestions everyone has provided, as its helped inform where the project is headed today.

Bio

(defparameter *Will_Sinatra* '((Age . 31) (Occupation . DevOps Engineer) (FOSS-Dev . true) (Locale . Maine) (Languages . ("Lisp" "Fennel" "Lua" "Go" "Nim")) (Certs . ("LFCS"))))

"Very little indeed is needed to live a happy life." - Aurelius