DB
Database Model
The MemberServices SQL tables, built from create_tables.sql below plus project_services, which only MemberServices.sjs references. Colours: member (blue), services (violet), groups (emerald), accounts (amber), legacy (rose). The keys and links are logical only, because the SQL declares no PK or FK constraints. Drag table cards to rearrange them; scroll the box to see the whole model.
member-services-model.dbmv.json
Database Model View PaperScape App (.giraffe.js)
To use it in PaperScape, drag the .dbmv.json link onto the PaperScape canvas, or open it with Open Schema in the DB Model View app.
create_tables.sql
<![CDATA[
<!--OF insert (/OpenForum/AddOn/MemberServices/DB/create_tables.sql) start -->
CREATE SEQUENCE member_id_seq START 1234;
#DROP TABLE member IF EXISTS;
CREATE TABLE member(
id smallint DEFAULT nextval('member_id_seq'::regclass),
first_name text,
last_name text,
alias text,
email text,
password text,
created_date numeric,
registered_date numeric,
verified_date numeric,
last_password_reset numeric
);
CREATE SEQUENCE service_id_seq START 1;
#DROP TABLE member_service IF EXISTS;
CREATE TABLE member_service(
member_id smallint,
service_id smallint DEFAULT nextval('service_id_seq'::regclass),
service_alias text,
used_count numeric,
available_count numeric,
start_date numeric,
end_date numeric,
access_token text,
last_used_date numeric
);
#DROP TABLE member_service_description IF EXISTS;
CREATE TABLE member_service_description(
service_id smallint,
service_name text,
service_home_page text
);
CREATE SEQUENCE group_id_seq START 1;
#DROP TABLE member_group IF EXISTS;
CREATE TABLE member_group(
group_id numeric DEFAULT nextval('group_id_seq'::regclass),
name text,
owner_id numeric,
join_code text
);
#DROP TABLE group_membership IF EXISTS;
CREATE TABLE group_membership(
group_id numeric,
member_id numeric
);
#DROP TABLE group_admin IF EXISTS;
CREATE TABLE group_admin(
group_id numeric,
member_id numeric
);
#DROP TABLE group_join_request IF EXISTS;
CREATE TABLE group_join_request(
group_id numeric,
member_id numeric,
status text,
created_date numeric
);
#DROP TABLE account IF EXISTS;
CREATE TABLE account(
account_id numeric,
owner_id numeric,
name text,
balance numeric
);
#DROP TABLE account_ledger IF EXISTS;
CREATE TABLE account_ledger(
account_ledger_id numeric,
account_id numeric,
owner_id numeric,
amount numeric,
description text,
source text,
entry_date numeric,
entry_type text
);
#MEM-T-012: pending registration verification codes. One row per (lower-cased) email.
#Only a hash of the code is stored. The row is deleted once registration completes.
#DROP TABLE member_verification_code IF EXISTS;
CREATE TABLE member_verification_code(
email text,
code_hash text,
first_name text,
last_name text,
alias text,
remote_ip text,
created numeric,
expires numeric,
attempts numeric,
sent_count numeric,
last_sent numeric,
used numeric,
verify_token_hash text,
verify_token_expires numeric
);
select * from member;
select * from member_service;
select * from member_service_description;
select * from member_group;
select * from group_membership;
select * from group_admin;
select * from group_join_request;
select * from account;
select * from account_ledger;
select * from member_verification_code;
<!--OF insert (/OpenForum/AddOn/MemberServices/DB/create_tables.sql) end -->
]]>