--- Driven by the goal of building a more robust version of my existing [[A Deep Dive into the 'Automate Your Gains' Workout App UI & Features|Workout Logger Web App]], I recently installed [SQLPage](https://sql-page.com/) on my homelab. My current objective involves implementing a user login and session management system within this new application. Here's an account of my progress so far. </br> ## Part 1: Lets Get Cooking (Planning) The plan for this project was simple. Following the SQLPage documentation, I would create an authentication system using session tokens. This involved the following: 1. A `users` table to store usernames and hashed passwords. 2. A `sessions` table to track active logins. 3. A series of `.sql` files to handle user signup, login, and logout. The intended login flow seemed straightforward: ```mermaid flowchart TD A["User visits Login Page"] --> B["Submits Username/Password"] B --> C@{ label: "Direct to 'create_session.sql'" } C -- Password Correct --> D(["Create Session Token"]) D --> E(["Set Cookie in Browser"]) E --> F(["Redirect to Main App"]) C -- Password Incorrect --> G(["Redirect back to Login Page w/ Error"]) B@{ shape: rounded} C@{ shape: stadium} style A fill:#FFF9C4 style B fill:#FFF9C4 style C fill:#FFFFFF ``` </br> Satisfied with this plan I went ahead and created the necessary tables and the `.sql` files for `login.sql`, `signup.sql`, and `create_session.sql`. ```sql -- login.sql -- Display user login form SELECT 'form' as component, 'Login' as title, 'Log in' as validate, 'auth_login_action.sql' as action; SELECT 'text' as type, 'username' as name, 'Username' as label, 'Enter username' as placeholder, true as required; SELECT 'password' as type, 'password' as name, 'Password' as label, 'Enter password' as placeholder, true as required; -- Link to the signup page SELECT 'text' as component, 'Don''t have an account? [Sign up here](auth_signup_form.sql).' as contents_md; ``` ```sql -- create_session.sql SELECT 'authentication' as component, 'login_form.sql?error=Invalid username or password. Please try again.' as link, ( SELECT password_hash FROM users WHERE username = :username ) as password_hash, :password as password; -- Create the session in the database, including the expiry date. SET session_id = sqlpage.random_string(32); INSERT INTO sessions (session_token, username, expires_at) VALUES ( $session_id, :username, DATETIME('now', '+1 day') ); -- Set the session cookie in the user's browser. SELECT 'cookie' as component, 'session_token' as name, $session_id as value; -- Redirect to the main application page. SELECT 'redirect' as component, '/' as link; ``` </br> ## Part 2: The Silent Redirect After creating my first “test user”, I attempted to log in with the new username and password and then hit "Submit.” ![[IMG-SQLPage Authentication on a Homelab - A Cookie Conundrum-20250624232946785.png|600]] </br> This resulted in an immediate returned to the login page. ![[IMG-SQLPage Authentication on a Homelab - A Cookie Conundrum-20250624232946848.png|600]] No error messages, just a fresh login form. ![[IMG-SQLPage Authentication on a Homelab - A Cookie Conundrum-20250624232946946.gif|200]] A silent failure can be one of the most frustrating bugs to experience. My immediate assumption was that I had mistyped the password or username. Why was the application redirecting me? To find out what was really going on, I turned `create_session.sql` into a temporary debugging page. Instead of trying to log the user in, it would simply display the data it received from the form. </br> ```sql -- create_session.sql (Debug Version 1) SELECT 'alert' as component, 'Debug Information' as title; SELECT 'text' as component, 'Username Submitted:' as title; SELECT 'text' as component, :username as contents; SELECT 'text' as component, 'Password Submitted:' as title; SELECT 'text' as component, :password as contents; -- ... ``` </br> I attempted another login in again, and this time, I saw something new. ``` +------------------------------------------+ | Debug Information | +------------------------------------------+ | Username Submitted: test_user_1 | | Password Submitted: test | | Password Hash Found: $argon2id$v=19... | +------------------------------------------+ ``` </br> The login page was correctly submitting data and it was being received by the `create_session.sql` site. My setup was working??? ![[IMG-SQLPage Authentication on a Homelab - A Cookie Conundrum-20250624232947047.gif|300]] </br> ## Part 3: Cookie Crumble? So the data was correct, but the site wasn’t loading, what could be the issue? The environment… The culprit ended up being something super simple, which was all my fault. I had been doing all my testing locally, accessing my app via its local IP address: `http://192.168.1.249:8080`. Normally this would be ok, if the site was ONLY being hosted locally, but it wasn’t. The browser, following modern security practices, was refusing to properly set a cookie for a bare IP address. The entire login flow was breaking because the most critical piece, the session cookie, was never being saved. **The Fix:** I switched to using the public-facing Cloudflared Tunnel url I had set up earlier in the week: **`https://workout.lilnasx.win`**. Immediately, everything started working. The login was successful, the cookie was set, and I was redirected to the main application. ![[IMG-SQLPage Authentication on a Homelab - A Cookie Conundrum-20250624232947158.gif|300]] </br> ## Part 4: Side Quest - The Foreign Key Frustration During the debugging process, I frequently needed to reset my database tables. This led to a maddening side quest with a recurring villain: `FOREIGN KEY constraint failed` being shown as a error on pages I had recently debugged. ![[IMG-SQLPage Authentication on a Homelab - A Cookie Conundrum-20250624232947269.gif|300]] </br> I was trying to `DROP TABLE users;`, but the `sessions` table had a foreign key relationship pointing to it, and the database refused to let me break that link. My initial attempts to drop `sessions` then `users` failed, likely due to a caching or transactional issue within the SQLPage interface I was using. > [!WARNING] The Power of PRAGMA > Disabling foreign key checks is a powerful tool for administrative tasks, but it should be used with extreme caution. It temporarily removes the safety nets that protect the integrity of your data. </br> The definitive solution was to connect directly to the database file with **DB Browser for SQLite** and run a script that temporarily disabled the constraint checks. ```sql -- The script that finally worked PRAGMA foreign_keys = OFF; DROP TABLE IF EXISTS sessions; DROP TABLE IF EXISTS users; -- Re-create tables here... PRAGMA foreign_keys = ON; ``` </br> This allowed me to reset the schema cleanly and get back to the main quest. </br> ## Part 5: The Final Polish ![[IMG-SQLPage Authentication on a Homelab - A Cookie Conundrum-20250624232947380.gif|250]] </br> With the login system now working over a public URL, one final feature request came to mind: could local users on the `192.168.*.*` network access the app _without_ needing to log in? This turned out to be a surprisingly easy update that could all be handled within the `shell.sql` that wraps every page. Diagram of how to force a login. ```mermaid flowchart TD A["User Visits Site"] --> B{"Is IP local: '192.168.x.x'" } B --> C(["YES"]) & D(["NO"]) C --> E["Show Page"] D --> F("Does User have a valid cookie?") F --> G(["No"]) & I(["YES"]) G --> H("Redirect to Login Page") I --> E A@{ shape: rounded} B@{ shape: rounded} E@{ shape: rounded} style A fill:#FFFFFF style B fill:#FFFFFF style C fill:#C8E6C9 style D fill:#FFCDD2 style E fill:#FFFFFF style F fill:#FFFFFF style G fill:#FFCDD2 style I fill:#C8E6C9 style H fill:#FFFFFF ``` </br> This implementation uses the `sqlpage.client_ip()` function to create a conditional authentication check. ```sql -- shell.sql SELECT 'authentication' as component, 'login.sql' as link WHERE -- Only check auth if the IP is NOT local sqlpage.client_ip() NOT LIKE '192.168.%' AND -- AND the user doesn't have a valid session cookie NOT EXISTS (...); ``` </br> ## Conclusion Here’s some of things I learned from this debugging journey: - **Silent failures often hide environmental problems.** When the code seems right, check the context it's running in (aka my browser) - **Direct database access is an essential admin tool.** Sometimes you need to go straight to the source to solve schema problems. - **SQLPage is incredibly powerful.** The ability to write complex, conditional application logic directly in SQL is what made the final, elegant solution possible. In the end, I not only built the login system I wanted, but I also gained a much deeper understanding of the interplay between the browser, the server, and the database. And like any good adventure, the treasure I found was the knowledge I gained along the way. For an in-depth view of this project and its current status, check out my GitHub repository:[SQLPage-Workout-Logger](https://github.com/drusho/SQLPage-Workout-Logger). </br> --- ## Resources - [**SQLPage**](https://sql.page/ "null"): The low-code web framework at the heart of the project. - **Docker Containers**: - `lovasoa/sqlpage`: The official SQLPage container. - `cloudflare/cloudflared`: The container for creating the secure tunnel to the Cloudflare network. - [**Cloudflare Tunnels**](https://www.cloudflare.com/products/tunnel/ "null"): Provided a secure public URL and handled HTTPS, which was crucial for getting cookies to work correctly. - [**DB Browser for SQLite**](https://sqlitebrowser.org/ "null"): An indispensable tool for connecting directly to the `.db` file to fix schema issues like the `FOREIGN KEY` constraint failures. - [**Visual Studio Code**](https://code.visualstudio.com/ "null"): Used for editing all the `.sql` files.