Spaces:
Running
Running
Ctrl+K
- 1.52 kB initial commit
- 212 Bytes would you build a SAAS name FINAYITI on top of that Sql schema:// --------------------------------------------- // Companies & Related Tables // --------------------------------------------- Table companies { id uuid [pk] name text logo_url text address text phone text email text website text subscription_plan text [note: "Legacy field - consider moving to company_subscriptions"] subscription_status text [note: "Legacy field - consider moving to company_subscriptions"] trial_end_date timestamptz billing_cycle_anchor timestamptz created_at timestamptz [default: `now()`] updated_at timestamptz owner_id uuid [ref: > users.id] deleted_at timestamptz } Table company_usage_stats { id bigserial [pk] company_id uuid [ref: > companies.id] metric_name text usage_count int usage_limit int last_updated timestamptz } Table subscription_plans { id serial [pk] name text description text max_branches int max_departments int max_employees int monthly_cost decimal(10,2) annual_cost decimal(10,2) features json created_at timestamptz [default: `now()`] } Table company_subscriptions { id serial [pk] company_id uuid [ref: > companies.id] plan_id int [ref: > subscription_plans.id] start_date timestamptz end_date timestamptz subscription_status text last_updated timestamptz } Table plan_changes { id serial [pk] company_subscription_id int [ref: > company_subscriptions.id] from_plan_id int [ref: > subscription_plans.id] to_plan_id int [ref: > subscription_plans.id] change_date timestamptz reason text } Table profiles { id uuid [pk] company_id uuid [ref: > companies.id] full_name text role text avatar_url text created_at timestamptz [default: `now()`] updated_at timestamptz deleted_at timestamptz } // --------------------------------------------- // Notifications & Audit Logs // --------------------------------------------- Table notifications { id bigserial [pk] company_id uuid [ref: > companies.id] user_id uuid [ref: > users.id] message text notification_type varchar(50) link text read boolean [default: false] created_at timestamptz [default: `now()`] } Table audit_logs { id bigserial [pk] user_id uuid [ref: > users.id] action text table_name text record_id text old_data jsonb new_data jsonb ip_address text user_agent text created_at timestamptz [default: `now()`] } // --------------------------------------------- // Employees, Branches, Departments // --------------------------------------------- Table branches { id serial [pk] company_id uuid [ref: > companies.id] name text address text phone text created_at timestamptz [default: `now()`] deleted_at timestamptz } Table departments { id serial [pk] company_id uuid [ref: > companies.id] name text created_at timestamptz [default: `now()`] updated_at timestamptz } Table id_types { id serial [pk] type_name varchar(50) } Table employees { id serial [pk] user_id uuid [ref: > users.id] employee_no int [unique] branch_id int [ref: > branches.id] department_id int [ref: > departments.id] first_name varchar(255) last_name varchar(255) id_type_id int [ref: > id_types.id] id_number varchar(100) phone varchar(15) email varchar(255) role text password varchar(255) [note: "SECURITY: Consider moving to auth table"] date_employment date created_at timestamptz [default: `now()`] updated_at timestamptz deleted_at timestamptz } // --------------------------------------------- // Users Table // --------------------------------------------- Table users { id uuid [pk] username varchar(255) email varchar(255) role text created_at timestamptz [default: `now()`] updated_at timestamptz } // --------------------------------------------- // Teller & Transaction Tables // --------------------------------------------- Table teller { transaction_id varchar(50) [pk] current_date date daily_txns text related_bank_txns text current_day date } Table daily_txns { id serial [pk] teller_transaction_id varchar(50) [ref: > teller.transaction_id] employee_office text transaction_date date transaction_type text transfer_type text quantity int system_amount decimal(12,2) daily_rate decimal(12,4) local_htg decimal(12,4) local_htd decimal(12,4) percent_30 decimal(12,4) percent_70 decimal(12,4) category_txn text cumulative_balance decimal(12,2) employee_no int employee_role text category varchar(50) total_amount decimal(12,4) total_transaction decimal(12,4) current_balance decimal(12,4) circulaire_30 decimal(12,4) circulaire_70 decimal(12,4) total_us decimal(12,4) envoi decimal(12,4) last_balance decimal(12,4) teller_name text client_name text receiver_name text sender_name text payment_method text reference_no text transfer_company text transfer_agent text cashier_name text created_at timestamptz [default: `now()`] created_by_user_id uuid [ref: > users.id] updated_at timestamptz updated_by_user_id uuid [ref: > users.id] daily_reference_rate decimal(12,4) transaction_status text note text is_approved boolean [default: false] } Table bank_names { id serial [pk] name varchar(255) } Table currencies { id serial [pk] name varchar(50) } Table rates { id varchar(50) [pk] date date rate_item varchar(255) rate decimal(10,2) photo blob role varchar(50) update_date date } Table bank_txns { id serial [pk] teller_transaction_id varchar(50) [ref: > teller.transaction_id] bank_name text bank_account varchar(100) txn_type varchar(100) currency_id int [ref: > currencies.id] daily_rate_id varchar(50) [ref: > rates.id] amount decimal(18,2) description text local_bk_htg decimal(18,2) local_bk_ht decimal(18,2) historic_bk_txn text employee_name varchar(255) current_balance decimal(18,2) employee_no int total_amount decimal(18,2) current_date date related_tellers text } Table brh_rates { id serial [pk] description text achats decimal(10,4) ventes decimal(10,4) spread decimal(10,4) } Table txn_types { id serial [pk] name varchar(255) type_txn int category varchar(255) subcategory varchar(255) main_category varchar(255) } Table tsf_types { id serial [pk] name varchar(255) } Table categories { id serial [pk] category varchar(255) subcategory varchar(255) main_category varchar(255) } // --------------------------------------------- // Fix references (cleaned and clarified) // --------------------------------------------- Ref: txn_types.type_txn < bank_txns.txn_type Ref: tsf_types.name < daily_txns.transfer_type Ref: txn_types.type_txn < daily_txns.transaction_type Ref: bank_names.name < bank_txns.bank_name Ref: categories.category < daily_txns.category Ref: brh_rates.achats < daily_txns.daily_reference_rate // --------------------------------------------- // Index Suggestions (dbdiagram.io doesn't support index syntax) // --------------------------------------------- // Companies: idx_companies_owner (owner_id) // Employees: idx_employees_company (company_id), idx_employees_deleted (deleted_at) // Notifications: idx_notifications_user_read (user_id, read) // Audit Logs: idx_audit_logs_user_time (user_id, created_at) // Subscriptions: idx_subscription_company (company_id) - Initial Deployment
- 38 kB would you build a SAAS name FINAYITI on top of that Sql schema:// --------------------------------------------- // Companies & Related Tables // --------------------------------------------- Table companies { id uuid [pk] name text logo_url text address text phone text email text website text subscription_plan text [note: "Legacy field - consider moving to company_subscriptions"] subscription_status text [note: "Legacy field - consider moving to company_subscriptions"] trial_end_date timestamptz billing_cycle_anchor timestamptz created_at timestamptz [default: `now()`] updated_at timestamptz owner_id uuid [ref: > users.id] deleted_at timestamptz } Table company_usage_stats { id bigserial [pk] company_id uuid [ref: > companies.id] metric_name text usage_count int usage_limit int last_updated timestamptz } Table subscription_plans { id serial [pk] name text description text max_branches int max_departments int max_employees int monthly_cost decimal(10,2) annual_cost decimal(10,2) features json created_at timestamptz [default: `now()`] } Table company_subscriptions { id serial [pk] company_id uuid [ref: > companies.id] plan_id int [ref: > subscription_plans.id] start_date timestamptz end_date timestamptz subscription_status text last_updated timestamptz } Table plan_changes { id serial [pk] company_subscription_id int [ref: > company_subscriptions.id] from_plan_id int [ref: > subscription_plans.id] to_plan_id int [ref: > subscription_plans.id] change_date timestamptz reason text } Table profiles { id uuid [pk] company_id uuid [ref: > companies.id] full_name text role text avatar_url text created_at timestamptz [default: `now()`] updated_at timestamptz deleted_at timestamptz } // --------------------------------------------- // Notifications & Audit Logs // --------------------------------------------- Table notifications { id bigserial [pk] company_id uuid [ref: > companies.id] user_id uuid [ref: > users.id] message text notification_type varchar(50) link text read boolean [default: false] created_at timestamptz [default: `now()`] } Table audit_logs { id bigserial [pk] user_id uuid [ref: > users.id] action text table_name text record_id text old_data jsonb new_data jsonb ip_address text user_agent text created_at timestamptz [default: `now()`] } // --------------------------------------------- // Employees, Branches, Departments // --------------------------------------------- Table branches { id serial [pk] company_id uuid [ref: > companies.id] name text address text phone text created_at timestamptz [default: `now()`] deleted_at timestamptz } Table departments { id serial [pk] company_id uuid [ref: > companies.id] name text created_at timestamptz [default: `now()`] updated_at timestamptz } Table id_types { id serial [pk] type_name varchar(50) } Table employees { id serial [pk] user_id uuid [ref: > users.id] employee_no int [unique] branch_id int [ref: > branches.id] department_id int [ref: > departments.id] first_name varchar(255) last_name varchar(255) id_type_id int [ref: > id_types.id] id_number varchar(100) phone varchar(15) email varchar(255) role text password varchar(255) [note: "SECURITY: Consider moving to auth table"] date_employment date created_at timestamptz [default: `now()`] updated_at timestamptz deleted_at timestamptz } // --------------------------------------------- // Users Table // --------------------------------------------- Table users { id uuid [pk] username varchar(255) email varchar(255) role text created_at timestamptz [default: `now()`] updated_at timestamptz } // --------------------------------------------- // Teller & Transaction Tables // --------------------------------------------- Table teller { transaction_id varchar(50) [pk] current_date date daily_txns text related_bank_txns text current_day date } Table daily_txns { id serial [pk] teller_transaction_id varchar(50) [ref: > teller.transaction_id] employee_office text transaction_date date transaction_type text transfer_type text quantity int system_amount decimal(12,2) daily_rate decimal(12,4) local_htg decimal(12,4) local_htd decimal(12,4) percent_30 decimal(12,4) percent_70 decimal(12,4) category_txn text cumulative_balance decimal(12,2) employee_no int employee_role text category varchar(50) total_amount decimal(12,4) total_transaction decimal(12,4) current_balance decimal(12,4) circulaire_30 decimal(12,4) circulaire_70 decimal(12,4) total_us decimal(12,4) envoi decimal(12,4) last_balance decimal(12,4) teller_name text client_name text receiver_name text sender_name text payment_method text reference_no text transfer_company text transfer_agent text cashier_name text created_at timestamptz [default: `now()`] created_by_user_id uuid [ref: > users.id] updated_at timestamptz updated_by_user_id uuid [ref: > users.id] daily_reference_rate decimal(12,4) transaction_status text note text is_approved boolean [default: false] } Table bank_names { id serial [pk] name varchar(255) } Table currencies { id serial [pk] name varchar(50) } Table rates { id varchar(50) [pk] date date rate_item varchar(255) rate decimal(10,2) photo blob role varchar(50) update_date date } Table bank_txns { id serial [pk] teller_transaction_id varchar(50) [ref: > teller.transaction_id] bank_name text bank_account varchar(100) txn_type varchar(100) currency_id int [ref: > currencies.id] daily_rate_id varchar(50) [ref: > rates.id] amount decimal(18,2) description text local_bk_htg decimal(18,2) local_bk_ht decimal(18,2) historic_bk_txn text employee_name varchar(255) current_balance decimal(18,2) employee_no int total_amount decimal(18,2) current_date date related_tellers text } Table brh_rates { id serial [pk] description text achats decimal(10,4) ventes decimal(10,4) spread decimal(10,4) } Table txn_types { id serial [pk] name varchar(255) type_txn int category varchar(255) subcategory varchar(255) main_category varchar(255) } Table tsf_types { id serial [pk] name varchar(255) } Table categories { id serial [pk] category varchar(255) subcategory varchar(255) main_category varchar(255) } // --------------------------------------------- // Fix references (cleaned and clarified) // --------------------------------------------- Ref: txn_types.type_txn < bank_txns.txn_type Ref: tsf_types.name < daily_txns.transfer_type Ref: txn_types.type_txn < daily_txns.transaction_type Ref: bank_names.name < bank_txns.bank_name Ref: categories.category < daily_txns.category Ref: brh_rates.achats < daily_txns.daily_reference_rate // --------------------------------------------- // Index Suggestions (dbdiagram.io doesn't support index syntax) // --------------------------------------------- // Companies: idx_companies_owner (owner_id) // Employees: idx_employees_company (company_id), idx_employees_deleted (deleted_at) // Notifications: idx_notifications_user_read (user_id, read) // Audit Logs: idx_audit_logs_user_time (user_id, created_at) // Subscriptions: idx_subscription_company (company_id) - Initial Deployment
- 388 Bytes initial commit