Client Database

For client database, we will be using sqlite for the desktop client and other options for web app or chrome extension such as local storage and IndexDB.

Read Client Design first before continuing, otherwise you won't understand the decisions made.

Sqlite DB

• account table: Since we want to support multiple profiles on the same client app, the database should have an account table containing all account-specific information, such as access token, refresh token, feature enabled, etc.
• user table: Thus we should have an user table to store all users and the server url. The server url indicates which server they belong to. There can be users with the same email and username, but different servers.
• CrossCopy will also support friend sharing feature (user can have a contact list to facilitate data transfer)
  • The user table not only stores user that is logged in, but also all users that is a friend of any of the logged in users.
  • From the user table, we don't know who the current logged in users is. The account table will have a user_id foreign key, pointing to the current logged in users.
  • The user table won't contain much information as friends are also included in it. Just basic information enough for identifying the users, such as server, username, email.
• friendship table: Since the user table has a many-to-many relationship (i.e. each logged in user can have many friends), we have to have a separate friendship table to store the friend relationship.

drop table user;
drop table friendship;
drop table account;
drop table device;
drop table profile;
drop table rec;


create table user
(
    local_db_id  integer not null
        constraint user_pk
            primary key autoincrement,
    remote_db_id integer not null,
    email        text,
    username     text,
    server       text
);

create table friendship
(
    user1_id integer not null
        constraint friendship_user1___fk
            references user,
    user2_id integer
        constraint friendship_user2___fk
            references user
);


create table account
(
    id                     integer not null
        constraint account_pk
            primary key autoincrement,
    user_id                integer
        constraint account_user___fk
            references user
            on update cascade on delete cascade,
    access_token           text,
    refresh_token          text,
    clipboard_sync_enabled integer default 1,
    mouse_share_enabled    integer default 1,
    file_transfer_enabled  integer default 1
);


create table profile
(
    id          integer            not null primary key,
    profileName varchar            not null,
    preferences varchar default '' not null,
    user_id     integer            not null
        constraint profile_user___fk
            references user
            on update cascade on delete cascade
);


create table device
(
    id          integer            not null
        primary key,
    deviceName  varchar            not null,
    preferences varchar default '' not null,
    user_id     integer            not null
        constraint user___fk
            references user
            on update cascade on delete cascade
);


create table rec
(
    id        integer,
    uuid      varchar                            not null
        primary key,
    createdAt datetime default (datetime('now')) not null,
    type      varchar  default 'TEXT'            not null,
    userId    integer,
    value     text                               not null,
    expired   tinyint  default 0                 not null,
    deleted   tinyint  default 0                 not null,
    deletedAt datetime,
    insync    tinyint  default 0                 not null,
    deviceId  integer                            not null
        constraint rec_device___fk
            references device,
    profileId integer                            not null
        constraint rec_profile___fk
            references profile
);