project1

Tables (14)

bank

  • bank_id — BIGSERIAL
  • bank_name — VARCHAR(100) — NOT NULL
  • bank_code — VARCHAR(20)
  • branch_name — VARCHAR(100)

chat_users

  • chat_user_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • nick_name — VARCHAR(50) — NOT NULL
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

donation

  • donation_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • amount — NUMERIC — NOT NULL
  • donation_date — TIMESTAMP
  • message — TEXT
  • status — VARCHAR(20)
  • payment_id — BIGINT — Foreign key → payment.payment_id
  • is_cancelled — BOOLEAN
  • cancelled_by — BIGINT
  • cancelled_date — TIMESTAMP
  • reason_for_cancellation — TEXT
  • last_updated_date — TIMESTAMP
  • bank_id — BIGINT — Foreign key → bank.bank_id

donation_history

  • donation_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • amount — NUMERIC(10, 2) — NOT NULL
  • donation_date — TIMESTAMP
  • message — TEXT
  • payment_method — VARCHAR(50)
  • status — VARCHAR(20)

donation_log

  • log_id — BIGSERIAL
  • donation_id — BIGINT — Foreign key → donation.donation_id — NOT NULL
  • changed_date — TIMESTAMP
  • changed_by — BIGINT
  • previous_status — VARCHAR(20)
  • new_status — VARCHAR(20)
  • previous_amount — NUMERIC
  • new_amount — NUMERIC
  • action — VARCHAR(50) — NOT NULL
  • notes — TEXT

messages

  • message_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • message_content — TEXT — NOT NULL
  • sent_date — TIMESTAMP

payment

  • payment_id — BIGSERIAL
  • bank_id — BIGINT — Foreign key → bank.bank_id
  • payment_reference — VARCHAR(100)
  • amount — NUMERIC — NOT NULL
  • payment_date — TIMESTAMP
  • payment_status — VARCHAR(20)

receipts

  • receipt_id — BIGSERIAL
  • donation_id — BIGINT — Foreign key → donation_history.donation_id — NOT NULL
  • issued_date — TIMESTAMP
  • amount — NUMERIC(10, 2) — NOT NULL
  • payment_reference — VARCHAR(100)
  • status — VARCHAR(20)

roles

  • role_id — BIGSERIAL
  • role_name — VARCHAR(50) — NOT NULL
  • description — VARCHAR(255)

thank_you_messages

  • message_id — BIGSERIAL
  • donation_id — BIGINT — Foreign key → donation_history.donation_id — NOT NULL
  • message_content — TEXT — NOT NULL
  • sent_date — TIMESTAMP
  • status — VARCHAR(20)

transaction

  • transaction_id — BIGSERIAL
  • payment_id — BIGINT — Foreign key → payment.payment_id — NOT NULL
  • amount — NUMERIC(10, 2) — NOT NULL
  • transaction_date — TIMESTAMP
  • payment_method — VARCHAR(50)
  • status — VARCHAR(20)

user_audit_logs

  • log_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.user_id
  • action — VARCHAR(100)
  • description — VARCHAR(255)
  • action_time — TIMESTAMP

user_sessions

  • session_id — BIGSERIAL
  • user_id — BIGINT — Foreign key → users.user_id — NOT NULL
  • ip_address — VARCHAR(45)
  • user_agent — VARCHAR(255)
  • login_time — TIMESTAMP
  • logout_time — TIMESTAMP
  • session_uuid — UUID

users

  • user_id — BIGSERIAL
  • login_id — VARCHAR(50) — NOT NULL
  • username — VARCHAR(100)
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • salt — VARCHAR(255)
  • phone_number — VARCHAR(20)
  • gender — VARCHAR(10)
  • date_of_birth — DATE
  • address_line1 — VARCHAR(255)
  • address_line2 — VARCHAR(255)
  • city — VARCHAR(100)
  • state — VARCHAR(100)
  • postal_code — VARCHAR(20)
  • country — VARCHAR(50)
  • profile_image_url — VARCHAR(255)
  • status — VARCHAR(10)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • last_login — TIMESTAMP
  • email_verified — BOOLEAN
  • phone_verified — BOOLEAN
  • failed_login_attempts — INTEGER
  • last_failed_login — TIMESTAMP
  • is_two_factor_enabled — BOOLEAN
  • role — VARCHAR(20)
  • terms_accepted — BOOLEAN
  • terms_accepted_at — TIMESTAMP
  • user_uuid — UUID