Schema for kengdb

CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE folderTb (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    folder TEXT NOT NULL, parent_folder_id INTEGER DEFAULT NULL, system_key TEXT DEFAULT '', undeletable TEXT DEFAULT 'F',
    FOREIGN KEY(user_id) REFERENCES userTb(id)
);
CREATE TABLE [userLvlTb] (
   [id] INTEGER PRIMARY KEY NOT NULL,
   [level] INTEGER,
   [levelName] TEXT,
   [limitNote] INTEGER,
   [limitLucy] TEXT
, canuseAPI TEXT DEFAULT '[]');
CREATE TABLE "otherNoteTb" (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source TEXT NOT NULL,
    method TEXT NOT NULL
, user_id INTEGER DEFAULT 1);
CREATE UNIQUE INDEX idx_otherNoteTb_source ON otherNoteTb(source);
CREATE TABLE "teamTb" (
   [id] INTEGER PRIMARY KEY NOT NULL,
   [teamName] TEXT,
   [ownerUsr_id] INTEGER REFERENCES [userTb]([id]),
   [create_at] TEXT,
   [password] TEXT
);
CREATE TABLE "noteTb" (
                    id INTEGER PRIMARY KEY,
                    user_id INTEGER DEFAULT NULL REFERENCES userTb(id),
                    content TEXT NOT NULL DEFAULT '',
                    tags TEXT DEFAULT '[]',
                    created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
                    updated_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
                    enable TEXT DEFAULT 'T',
                    pinned TEXT DEFAULT 'F',
                    folder_id INTEGER DEFAULT NULL REFERENCES folderTb(id),
                    comment TEXT DEFAULT '', position INTEGER DEFAULT 0, visibility TEXT NOT NULL DEFAULT 'private');
CREATE TABLE teamNoteTb (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    team_id   INTEGER NOT NULL REFERENCES teamTb(id),
    author_id INTEGER NOT NULL DEFAULT 1 REFERENCES userTb(id),
    content   TEXT    NOT NULL DEFAULT '',
    tags      TEXT    NOT NULL DEFAULT '[]',
    created_at TEXT   NOT NULL DEFAULT (datetime('now','localtime')),
    updated_at TEXT   NOT NULL DEFAULT (datetime('now','localtime')),
    enable    TEXT    NOT NULL DEFAULT 'T',
    pinned    TEXT    NOT NULL DEFAULT 'F',
    comment   TEXT    NOT NULL DEFAULT ''
, folder_id INTEGER DEFAULT NULL, locked_by INTEGER DEFAULT NULL, position INTEGER DEFAULT 0);
CREATE TABLE teamFolderTb (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            team_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))
        );
CREATE TABLE teamMemberTb (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            team_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            role TEXT NOT NULL DEFAULT 'member',
            joined_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
            UNIQUE(team_id, user_id)
        );
CREATE INDEX idx_team_member_team ON teamMemberTb(team_id);
CREATE INDEX idx_team_member_user ON teamMemberTb(user_id);
CREATE TABLE sessionTb (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                token TEXT NOT NULL UNIQUE,
                expires REAL NOT NULL,
                created REAL NOT NULL,
                updated REAL NOT NULL
            );
CREATE INDEX idx_session_token ON sessionTb(token);
CREATE INDEX idx_session_user ON sessionTb(user_id);
CREATE TABLE smsCodeTb (
                phone TEXT PRIMARY KEY,
                code TEXT NOT NULL,
                expires REAL NOT NULL,
                last_sent REAL NOT NULL
            );
CREATE INDEX idx_sms_expires ON smsCodeTb(expires);
CREATE TABLE oauthAccountTb (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                provider TEXT NOT NULL,
                provider_user_id TEXT NOT NULL,
                email TEXT NOT NULL DEFAULT '',
                created REAL NOT NULL DEFAULT (unixepoch()),
                UNIQUE(provider, provider_user_id)
            );
CREATE INDEX idx_oauth_user ON oauthAccountTb(user_id);
CREATE INDEX idx_note_visibility ON noteTb(user_id, visibility, enable);
CREATE TABLE friendTb (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id    INTEGER NOT NULL REFERENCES userTb(id),
                friend_id  INTEGER NOT NULL REFERENCES userTb(id),
                status     TEXT    NOT NULL DEFAULT 'pending',
                created_at TEXT    NOT NULL DEFAULT (datetime('now','localtime')),
                updated_at TEXT    NOT NULL DEFAULT (datetime('now','localtime')),
                UNIQUE(user_id, friend_id)
            );
CREATE INDEX idx_friend_user ON friendTb(user_id, status);
CREATE INDEX idx_friend_friend ON friendTb(friend_id, status);
CREATE TABLE linkTb (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                from_note_id INTEGER NOT NULL,
                to_note_id   INTEGER NOT NULL,
                user_id      INTEGER NOT NULL,
                created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime')),
                UNIQUE(from_note_id, to_note_id)
            );
CREATE INDEX idx_link_from ON linkTb(from_note_id, user_id);
CREATE TABLE "userTb" (
   [id] INTEGER PRIMARY KEY,
   [username] TEXT NOT NULL,
   [nickname] TEXT NOT NULL DEFAULT '',
   [password] TEXT NOT NULL,
   [level_id] INTEGER NOT NULL DEFAULT '2' REFERENCES [userLvlTb]([id]),
   [phone] TEXT NOT NULL DEFAULT '',
   [phone_bind] TEXT NOT NULL DEFAULT '',
   [email] TEXT NOT NULL DEFAULT '',
   [email_bind] TEXT NOT NULL DEFAULT '',
   [created_at] TEXT NOT NULL DEFAULT (datetime('now','localtime')),
   [enable] TEXT DEFAULT 'T',
   [username_locked] TEXT DEFAULT 'F',
   [secre] TEXT DEFAULT 'lucy',
   [persona] TEXT DEFAULT ''
, disposition TEXT DEFAULT '', api_key TEXT DEFAULT '', github_id TEXT DEFAULT '');
CREATE INDEX idx_userTb_level_id ON userTb(level_id);
CREATE UNIQUE INDEX idx_userTb_username ON userTb(username);
CREATE UNIQUE INDEX idx_user_api_key ON userTb(api_key) WHERE api_key IS NOT NULL AND api_key<>'';
CREATE UNIQUE INDEX idx_user_phone_active ON userTb(phone) WHERE phone IS NOT NULL AND phone<>'' AND enable='T';
CREATE TABLE noteShareTb (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                note_id      INTEGER NOT NULL REFERENCES noteTb(id) ON DELETE CASCADE,
                from_user_id INTEGER NOT NULL REFERENCES userTb(id),
                to_user_id   INTEGER NOT NULL REFERENCES userTb(id),
                permission   TEXT    NOT NULL DEFAULT 'read' CHECK(permission IN ('read','write')),
                created_at   TEXT    NOT NULL DEFAULT (datetime('now','localtime')),
                updated_at   TEXT    NOT NULL DEFAULT (datetime('now','localtime')), folder_key TEXT NOT NULL DEFAULT 'friend', dismissed TEXT NOT NULL DEFAULT 'F', position INTEGER DEFAULT 0,
                UNIQUE(note_id, to_user_id)
            );
CREATE INDEX idx_note_share_to ON noteShareTb(to_user_id, note_id);
CREATE INDEX idx_note_share_from ON noteShareTb(from_user_id, note_id);
CREATE TABLE shareLinkTb (
                token        TEXT PRIMARY KEY,
                note_id      INTEGER NOT NULL REFERENCES noteTb(id) ON DELETE CASCADE,
                from_user_id INTEGER NOT NULL REFERENCES userTb(id),
                expires_at   TEXT DEFAULT NULL,
                view_count   INTEGER DEFAULT 0,
                created_at   TEXT NOT NULL DEFAULT (datetime('now','localtime'))
            );
CREATE INDEX idx_share_link_note ON shareLinkTb(note_id, from_user_id);
CREATE TABLE userIntegrationTb (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                source TEXT NOT NULL,
                token TEXT NOT NULL DEFAULT '',
                meta_json TEXT NOT NULL DEFAULT '{}',
                created_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
                updated_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),
                UNIQUE(user_id, source)
            );
CREATE INDEX idx_user_integration_user ON userIntegrationTb(user_id, source);
CREATE INDEX idx_note_share_inbox ON noteShareTb(to_user_id, dismissed, folder_key);
CREATE UNIQUE INDEX idx_folder_user_system_key ON folderTb(user_id, system_key) WHERE system_key IS NOT NULL AND system_key<>''