Initialize SQLite db and create Users table #6

New issue

Closed

opened 2025-10-12 14:19:57 +00:00 by juyung · 0 comments

juyung commented

2025-10-12 14:19:57 +00:00

Owner

Caddy

Install Caddy beforehand.

Create frontend and backend directory:

sudo mkdir -p /var/www/html # frontend files go here
sudo mkdir -p /var/www/app # backend files go here (Flask)
sudo mkdir -p /var/www/db # sqlite db file goes here

SQLite

cd /var/www/db
nano init.sql

init.sql:

-- users table
-- id: internal account identifier from Nextcloud (the Nextcloud account ID used by your backend).
--      This is NOT the value the user types on the login form; keep it stable and canonical.
-- username: the login name the user types on the login form (unique and user-facing).
-- password
-- recovery_key: encrypted/opaque recovery key. Treat as secret.
-- login_verifier: password verifier
CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,            -- Nextcloud internal account identifier (backend ID; not shown to user)
    username TEXT NOT NULL UNIQUE,  -- login name the user types on the login page (user-facing, changeable, unique)
    password TEXT NOT NULL,         -- password encryption is unncessary because files are encrypted
    recovery_key TEXT,              -- encrypted/opaque recovery key (treat as secret; avoid plaintext)
    login_verifier TEXT             -- password verifier
);

-- Index for fast username searches
CREATE INDEX IF NOT EXISTS idx_username ON users(username);

Create SQLite database and test it:

sudo sqlite3 app.db < init.sql
sqlite3
.tables
.schema users

### Caddy Install Caddy beforehand. Create frontend and backend directory: ```bash sudo mkdir -p /var/www/html # frontend files go here sudo mkdir -p /var/www/app # backend files go here (Flask) sudo mkdir -p /var/www/db # sqlite db file goes here ``` ### SQLite ``` cd /var/www/db nano init.sql ``` init.sql: ```sql -- users table -- id: internal account identifier from Nextcloud (the Nextcloud account ID used by your backend). -- This is NOT the value the user types on the login form; keep it stable and canonical. -- username: the login name the user types on the login form (unique and user-facing). -- password -- recovery_key: encrypted/opaque recovery key. Treat as secret. -- login_verifier: password verifier CREATE TABLE IF NOT EXISTS users ( id TEXT PRIMARY KEY, -- Nextcloud internal account identifier (backend ID; not shown to user) username TEXT NOT NULL UNIQUE, -- login name the user types on the login page (user-facing, changeable, unique) password TEXT NOT NULL, -- password encryption is unncessary because files are encrypted recovery_key TEXT, -- encrypted/opaque recovery key (treat as secret; avoid plaintext) login_verifier TEXT -- password verifier ); -- Index for fast username searches CREATE INDEX IF NOT EXISTS idx_username ON users(username); ``` Create SQLite database and test it: ``` sudo sqlite3 app.db < init.sql sqlite3 .tables .schema users ```

juyung self-assigned this 2025-10-12 14:19:57 +00:00

juyung added this to the kubeload project 2025-10-12 14:19:57 +00:00

juyung changed title from ~~Initialize SQLite and create Users table~~ to Initialize SQLite db and create Users table

2025-10-12 14:28:13 +00:00

juyung closed this issue

2025-10-13 22:33:08 +00:00

No labels

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: juyung/kubeload#6

No description provided.