Part 4 – Online and Offline Storage Location(s). How, When and Where Data is Collected and its Usage

Application Developed by: Parmeet Singh Ghai
Developer Home Page: https://parmeetsinghghai.github.io
Application Home Page: https://parmeetsinghghai.github.io/psgdrivesync
Application Github Page: https://github.com/parmeetsinghghai/psgdrivesync
Developer Github Page: https://github.com/parmeetsinghghai

Chapters in this Demo
===================
00:00 Introduction on Online and Offline Storage Locations namely Online Spreadsheet, Local Database and ‘Signatures’ Folder
01:02 Online Spreadsheet
03:41 Online Spreadsheet Opened up in Google Account
04:27 Online Spreadsheet ‘Customers’ Tab
05:36 Entering an Existing Master Key in the Application and Importance of Not Forgetting the Master Key When its Set
07:00 ‘Change Master Key’ Option in Help Tab
07:50 Introduction on ‘Delete License’ Functionality Present in the ‘Licenses’ Tab
08:38 Online Spreadsheet ‘Transactions’ Tab
10:34 Online Spreadsheet ‘Licenses’ Tab
10:47 Online Spreadsheet ‘VerificationCode’ Tab
12:01 Local Database in the form of SQLite Database file ‘Database.db’
12:25 Accessing Database using SQLite Tools
14:27 Showing All Database Tables using SQLite Tools
15:10 ‘Drives’ Table in Database
16:20 ‘Licenses’ Table in Database
19:20 ‘SyncItems’ and ‘SyncContainers’ Tables in Database
23:25 ‘FilteredFiles’ Table in Database
24:30 ‘HashTable’ Table in Database
25:09 ‘Drives’ Table – Correction Made – Drive Column in this table refers to the Unique Identifier that you Assign to the Drive and not your Google Drive Email Address
26:32 ‘Signatures’ Folder

Important Topics Discussed in this Demo
===================
The Application Has 3 Persistent Storage Locations Where Data Is Saved. The First Is An Online Storage In The Form Of a Spreadsheet That Is Hosted On One Of My Google Accounts. Its The Same Google Account That Hosts My Google Web App As Well. The Second Is The Local SQLite Database In The Form Of “Database.Db” File Saved In The Application Directory And The 3rd Is The “Signatures” Folder Located In The Application Directory.

Online Spreadsheet
The Spreadsheet Has 4 Tabs Namely Customers, Transactions, Licenses And Verification Code.

a) The “Customers” Tab Is Used To Save
1) End Users Gmail Address
2) PayPal Transaction ID
3) The Name Of The License That The End User Paid For.
4) a Unique Identifier For The End User Called Access Key.

b) The “Transactions” Tab Is Used To Save
1) The Email Address Linked To End User’s PayPal Account
2) The Transaction ID Linked To PayPal Payment
3) The Date When The Payment Was Processed
4) The Payment Amount
5) A Reference To The Email That Was Sent To The End User To Verify Their PayPal Account.

c) The “Licenses” Tab Shows
All The Licenses Which Are Available To The End User. This Is a Read Only Tab And Does Not Contain Any User Specific Information

d) The “Verification” Tab Is Used To Save
1) The Email Address That Is Linked To An End User’s PayPal Account
2) The Verification Code That Was Sent To Them During The Verification Process
3) Reference To The Verification Email That Was Sent Out
4) Indicator On Whether The Verification Code Was Verified By The User
5) Expiration Date Of The Verification Code

Database
Application Database Contains 9 Different Tables Each Serving Its Own Unique Purpose. Let Me Discuss Few Of The Important Tables Present In This Database And The Kind Of Google Account Data That Is Stored In Them

a) Drives Table Contains The Gmail Address Linked To Your Google Account. Rest Of The Information Is Not Related To Your Google Account.

b) Licenses Table Contains
1) Your Google Email Address
2) Name Of The License
3) The Number Of Days Its Valid For
4) The Purchase Price Of The License
5) Paypal Email Address Used To Pay For This License,
6) Transaction Id Linked To The Paypal Payment,
7) The Date On Which Payment Was Processed
8) Access Token, Refresh Token and Access Key

c) Sync Items And Sync Containers Tables Both Are Used To Setup Files And Folders For Automated Sync Operations. When You Setup a File Or Folder For An Automated Sync Operation Then The Following Data Is Saved In These Tables:
1) The Name Of The File Or Folder
2) Path (If Its a Local File Or Folder) Or ID (If Its a Google File Or Folder)
3) Parent Folder Path(If Its Local Folder) Or ID ( If Its Google Folder)

d) FilteredFiles Table Is Used When a File Or Folder Is Setup To Be Filtered During a Sync Operation. The Path Or ID Of A File Or Folder Gets Saved In This Folder.

e) Hashtable Is Used To Save The Sha256 Hash Of Files Which Were Synced During Automated Sync Operations. This Goes For All Files And Not Just Encrypted Files.

Signatures Folder
This Folder Contains The HMAC-SHA256 Hash Of All Those Files Which Get Encrypted Either During Real Time File Operations And Sync Operations. This Helps In Verifying The Authenticity And The Integrity Of The Content Of Files During Decryption.