Swipr - Datastore
We need a datastore. Primarily so that we can stow a given user’s Tinder information – Facebooked ID, Facebooked password, Facebooked access token, and Tinder access token, – but also because we now have a complicated application it’s state must be preserved somehow.
We’re going to skip talking about tables relating to how ASP.NET Core projects do identity because there are a lot of tables for that. The only thing worth pointing out with respect to that is many of our tables will keep a foreign key to the autogenerated AspNetUsers.Id field.
Tables
Auth Tokens
Our most dangerous table. Due to the pirate nature of how we’re interacting with Tinder, we need to keep a live reference to the user’s Facebook password, among other things, which we store in the AuthTokens table:
CREATE TABLE `AuthTokens` (
`UserId` TEXT NOT NULL,
`FacebookId` TEXT NOT NULL,
`FacebookAuthenticationToken` INTEGER NOT NULL,
`TinderAuthenticationToken` INTEGER NOT NULL,
`FacebookPassword` TEXT NOT NULL,
`FacebookEmail` TEXT NOT NULL,
FOREIGN KEY(`UserId`) REFERENCES `AspNetUsers`(`Id`) ON DELETE CASCADE,
PRIMARY KEY(`UserId`)
);
SwiprConfigs
This is the meat of the application, and the core of what is considered to be a Swipr user:
CREATE TABLE `SwiprConfigs` (
`UserId` TEXT NOT NULL,
`MustHaveBio` INTEGER NOT NULL DEFAULT 0,
`BioLikeKeywords` TEXT NOT NULL DEFAULT "",
`BioSuperlikeKeywords` TEXT NOT NULL DEFAULT "",
`BioBannedKeywords` INTEGER NOT NULL DEFAULT "",
`AgesEnabled` INTEGER NOT NULL DEFAULT 0,
`MinimumAge` INTEGER NOT NULL DEFAULT 18,
`MaximumAge` INTEGER NOT NULL DEFAULT -1,
`DistanceEnabled` INTEGER NOT NULL DEFAULT 0,
`MinimumDistance` INTEGER NOT NULL DEFAULT -1,
`MaximumDistance` INTEGER NOT NULL DEFAULT -1,
`PicturesEnabled` INTEGER NOT NULL DEFAULT 1,
`MinimumPictures` INTEGER NOT NULL DEFAULT 2,
`MinimumConfidence` INTEGER DEFAULT 40,
`SwiprEnabled` INTEGER NOT NULL DEFAULT 0,
`AcceptAll` INTEGER NOT NULL DEFAULT 0,
`SessionLimit` INTEGER NOT NULL DEFAULT 1000,
FOREIGN KEY(`UserId`) REFERENCES `AspNetUsers`(`Id`) ON DELETE CASCADE,
PRIMARY KEY(`UserId`)
);
TinderUsers
for bureaucratic reasons, we want to keep tally of the Tinder user’s we see. This is independent of any given Swipr user and is instead an application-wide kind of tracking:
CREATE TABLE `TinderUsers` (
`UserId` TEXT NOT NULL,
`Name` TEXT,
`Birthdate` INTEGER,
`Gender` INTEGER,
PRIMARY KEY(`UserId`)
);
TinderUserActions
We want to be able to present a Swipr user with the Tinder users that Swipr has seen, and what action it has taken on their behalf. To do this we keep track of what Tinder profiles each Swipr user has seen here:
CREATE TABLE `TinderUserActions` (
`Id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`SwiprUserId` TEXT NOT NULL,
`TinderUserId` TEXT NOT NULL,
`DateSeen` INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Action` INTEGER NOT NULL DEFAULT 0,
`Reason` TEXT NOT NULL,
`AdjustedAction` INTEGER,
FOREIGN KEY(`TinderUserId`) REFERENCES `TinderUsers`(`UserId`) ON DELETE CASCADE,
FOREIGN KEY(`SwiprUserId`) REFERENCES `AspNetUsers`(`Id`) ON DELETE CASCADE
);
SwiprTimeouts
We keep a small table that keeps track of when a given Swipr user’s Like Count will reset. This keeps us from needlessly dequeuing a presently-timed-out user.
CREATE TABLE `SwiprTimeouts` (
`UserId` TEXT NOT NULL,
`TimeoutExpiresAt` INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY(`UserId`) REFERENCES `AspNetUsers`(`Id`) ON DELETE CASCADE,
PRIMARY KEY(`UserId`)
);
SwiprErrors
Another table that prevents us from dequeuing invalid users, this table tracks things like Facebook token expiration, logon problems, or other such errors that require the actual user to intervene and remedy.
These are presented to the user on their Settings page.
CREATE TABLE `SwiprErrors` (
`UserId` TEXT NOT NULL,
`ErrorMsg` TEXT,
FOREIGN KEY(`UserId`) REFERENCES `AspNetUsers`(`Id`) ON DELETE CASCADE,
PRIMARY KEY(`UserId`)
);
Encryption
Storing passwords in plaintext is super-bad and we’ll never do that.
We create our Database first by using SQLite DBBrowser, and then apply a password to it. Once the database is passworded, we use the SQLitePCLRaw.bundle_sqlcipher NuGet package and the SQLite extension SQLCipher to decode the database at runtime.
See these posts for reference:
https://stackoverflow.com/questions/39903863/password-protected-sqlite-with-entity-framework-core
https://www.bricelam.net/2016/06/13/sqlite-encryption.html
https://github.com/paragpkulkarni/SQLiteEncryptionUsingEFCore
Moving On
In our next post, we’ll discuss the actual SwiprServer logic that unites everything together