WebPasswordSafe Data Model

audit_log
id	long	pk
eventdate	datetime
username	varchar(100)
ipaddress	varchar(50)
action	varchar(50)
target	text
success	char(1)	Y/N
message	text

groups
id	long	pk, fk subjects
name	varchar(100)	unique

ip_lockout
id	long	pk
ipaddress	varchar(50)	unique
fail_count	int
lockout_date	datetime	null = not blocked

password_access_audit
id	long	pk
password_id	long	fk passwords
user_id	long	fk users
date_accessed	datetime

password_data
id	long	pk
password_id	long	fk passwords
password_position	int	0 = current
password	varchar(300)	encrypted of 100
date_created	datetime
user_created_id	long	fk users

password_tags
password_id	long	pk, fk passwords
tag_id	long	pk, fk tags

passwords
id	long	pk
name	varchar(100)
username	varchar(100)
target	varchar(64)
notes	text
max_history	int	-1 = infinite
active	char(1)	Y/N
date_created	datetime
user_created_id	long	fk users
date_last_update	datetime
user_last_update_id	long	fk users

permissions
id	long	pk
password_id	long	fk passwords
subject_id	long	fk subjects
access_level	varchar(5)	READ,WRITE,GRANT

subjects
id	long	pk
type	char(1)	U/G

tags
id	long	pk
name	varchar(100)	unique, immutable

template_details
id	long	pk
template_id	long	fk templates
subject_id	long	fk subjects
access_level	varchar(5)	READ,WRITE,GRANT

templates
id	long	pk
user_id	long	fk users
name	varchar(100)	unique
shared	char(1)	Y/N

user_authn_password
id	long	pk
user_id	long	fk users, unique
password	varchar(100)	hash encrypted of 100

user_groups
user_id	long	pk, fk users
group_id	long	pk, fk groups

user_lockout
id	long	pk
user_id	long	fk users, unique
fail_count	int

users
id	long	pk, fk subjects
username	varchar(100)	unique, immutable
fullname	varchar(100)
email	varchar(100)
active	char(1)	Y/N
date_created	datetime
last_login	datetime