feat: database schema

[SohamRatnaparkhi]

User Table

• user_id (Primary Key)
• local_username (for local authentication)
• oauth_provider (e.g., google, github, gitlab, bitbucket)
• oauth_id (ID provided by the OAuth provider)
• oauth_username (Username provided by the OAuth provider)
• email
• account_type (e.g., free, premium)
• wallet_address (Cryptocurrency wallet address)
• profile_picture_url (URL for the user's profile picture)
• oauth_access_token (Access token provided by the OAuth provider)
• oauth_refresh_token (Refresh token for OAuth providers that support it)
• oauth_expires_at (Expiration timestamp for the access token)
• rsa_public_key (User's public RSA key)
• hashed_secret (Hashed secret for authentication)

Repository Table

• repo_id (Primary Key)
• user_id (Foreign Key referencing User Table)
• name
• url
• platform (e.g., github, gitlab, bitbucket)
• visibility (public/private)
• description
• created_at

Release Table

• release_id (Primary Key)
• repo_id (Foreign Key referencing Repository Table)
• version
• changelog
• release_date
• is_paid
• price
• transaction_mode (e.g., traditional, crypto)
• short_description
• long_description
• images (Array or reference to images)
• average_rating (calculated field)
• release_type (e.g., production, development, testing)

Comments Table

• comment_id (Primary Key)
• release_id (Foreign Key referencing Release Table)
• user_id (Foreign Key referencing User Table)
• comment_text
• comment_date

Likes Table

• like_id (Primary Key)
• release_id (Foreign Key referencing Release Table)
• user_id (Foreign Key referencing User Table)
• like_date

Purchases Table

• purchase_id (Primary Key)
• release_id (Foreign Key referencing Release Table)
• buyer_user_id (Foreign Key referencing User Table)
• purchase_date
• amount
• currency (e.g., USD, BTC, ETH)
• smart_contract_address
• transaction_mode (e.g., traditional, crypto)

Ratings Table

• rating_id (Primary Key)
• release_id (Foreign Key referencing Release Table)
• user_id (Foreign Key referencing User Table)
• rating_value
• rating_date

Backup Table

• backup_id (Primary Key)
• repo_id (Foreign Key referencing Repository Table)
• platform (e.g., github, gitlab, bitbucket)
• commit_sha (SHA hash of the commit)
• public_private (Public or private backup)
• name
• encrypted_url_cid (Encrypted URL or Content ID for the backup)
• backup_date

IAMRolesTable

• role_id (Primary Key)
• organization_id (Foreign Key referencing Organization Table)
• user_id (Foreign Key referencing User Table)
• role_name (e.g., admin, member)

[SohamRatnaparkhi]

Think about sharing backups across organisation

[SohamRatnaparkhi]

Separate backup type might be required for orgs

Crud access using IAM roles

Each role has a key pair and secret.

TODO - think more