SQL database schema

The docassemble web application requires a central SQL server. The db directive in the configuration tells docassemble where to find this SQL database.

The necessary data tables are created with the docassemble.webapp.create_tables module. If new versions of docassemble add new tables or modify the columns of existing tables, the docassemble.webapp.create_tables module uses alembic to make these changes to the database.

The docassemble.webapp.create_tables module runs automatically within the Docker container whenever the container starts up or when the server restarts (e.g., when a package is installed or upgraded).

The following table lists all of the tables and columns that docassemble uses. (This is output from PostgreSQL, but the same concepts exist in most SQL database systems.)

table_name column_name data_type character_maximum_length column_default
speaklist upload integer    
speaklist encrypted boolean   true
package active boolean   true
machinelearning modtime timestamp without time zone    
package_auth id integer   nextval('package_auth_id_seq'::regclass)
package_auth package_id integer    
package_auth user_id integer    
install id integer   nextval('install_id_seq'::regclass)
shortener id integer   nextval('shortener_id_seq'::regclass)
machinelearning active boolean   false
install version integer    
globalobjectstorage id integer   nextval('globalobjectstorage_id_seq'::regclass)
shortener user_id integer    
shortener temp_user_id integer    
install package_id integer    
shortener index integer    
shortener modtime timestamp without time zone   now()
role id integer   nextval('role_id_seq'::regclass)
jsonstorage id integer   nextval('jsonstorage_id_seq'::regclass)
email datetime_received timestamp without time zone    
supervisors id integer   nextval('supervisors_id_seq'::regclass)
machinelearning id integer   nextval('machinelearning_id_seq'::regclass)
jsonstorage data jsonb    
supervisors start_time timestamp without time zone   now()
globalobjectstorage encrypted boolean   true
uploads indexno integer   nextval('uploads_indexno_seq'::regclass)
jsonstorage modtime timestamp without time zone   now()
jsonstorage persistent boolean   false
objectstorage id integer   nextval('objectstorage_id_seq'::regclass)
uploads private boolean   true
uploads persistent boolean   false
uploadsroleauth id integer   nextval('uploadsroleauth_id_seq'::regclass)
uploadsroleauth uploads_indexno integer    
uploadsroleauth role_id integer    
uploadsuserauth id integer   nextval('uploadsuserauth_id_seq'::regclass)
uploadsuserauth uploads_indexno integer    
uploadsuserauth user_id integer    
uploadsuserauth temp_user_id integer    
tempuser id integer   nextval('tempuser_id_seq'::regclass)
user_auth id integer   nextval('user_auth_id_seq'::regclass)
user_auth user_id integer    
globalobjectstorage user_id integer    
globalobjectstorage temp_user_id integer    
user_invite id integer   nextval('user_invite_id_seq'::regclass)
chatlog id integer   nextval('chatlog_id_seq'::regclass)
user_invite role_id integer    
user_invite invited_by_user_id integer    
package id integer   nextval('package_id_seq'::regclass)
user_roles id integer   nextval('user_roles_id_seq'::regclass)
user_roles user_id integer    
user_roles role_id integer    
userdict indexno integer   nextval('userdict_indexno_seq'::regclass)
attachments question integer    
attachments id integer   nextval('attachments_id_seq'::regclass)
chatlog user_id integer    
userdict user_id integer    
userdict encrypted boolean   true
userdict modtime timestamp without time zone    
userdictkeys indexno integer   nextval('userdictkeys_indexno_seq'::regclass)
chatlog temp_user_id integer    
chatlog owner_id integer    
userdictkeys user_id integer    
userdictkeys temp_user_id integer    
user id integer   nextval('user_id_seq'::regclass)
chatlog temp_owner_id integer    
chatlog open_to_peer boolean   false
chatlog encrypted boolean   true
user confirmed_at timestamp without time zone    
user active boolean   false
chatlog modtime timestamp without time zone    
emailattachment id integer   nextval('emailattachment_id_seq'::regclass)
emailattachment email_id integer    
emailattachment index integer    
attachments encrypted boolean   true
email id integer   nextval('email_id_seq'::regclass)
emailattachment upload integer    
speaklist id integer   nextval('speaklist_id_seq'::regclass)
package upload integer    
package version integer   1
email datetime_message timestamp without time zone    
speaklist question integer    
machinelearning create_time timestamp without time zone    
user modified_at timestamp without time zone    
user last_login timestamp without time zone    
package dependency boolean   false
package core boolean   false
speaklist dialect character varying 10  
attachments key character varying 250  
attachments dictionary text    
attachments filename text    
email short character varying 250  
email all_addr text    
email to_addr text    
email cc_addr text    
email from_addr text    
email reply_to_addr text    
email return_path_addr text    
email subject text    
machinelearning group_id character varying 1024  
machinelearning key character varying 1024  
machinelearning independent text    
machinelearning dependent text    
machinelearning info text    
globalobjectstorage key character varying 1024  
globalobjectstorage value text    
package name character varying 255  
package type text    
package giturl character varying 255  
package gitsubdir text    
package packageversion text    
package limitation text    
package gitbranch character varying 255  
install hostname text    
install packageversion text    
jsonstorage filename character varying 255  
jsonstorage key character varying 250  
jsonstorage tags text    
objectstorage key character varying 1024  
objectstorage value text    
chatlog filename character varying 255  
chatlog key character varying 250  
chatlog message text    
emailattachment content_type text    
emailattachment extension text    
speaklist filename character varying 255  
speaklist key character varying 250  
speaklist phrase text    
speaklist type character varying 20  
speaklist language character varying 10  
alembic_version version_num character varying 32  
speaklist digest text    
speaklist voice character varying 20  
package_auth authtype character varying 255 'owner'::character varying
shortener short character varying 250  
shortener filename character varying 255  
shortener uid character varying 250  
shortener key character varying 255  
role name character varying 50  
role description character varying 255  
supervisors hostname text    
supervisors url text    
supervisors role text    
uploads key character varying 250  
uploads filename character varying 255  
uploads yamlfile character varying 255  
user_auth password character varying 255 ''::character varying
user_auth reset_password_token character varying 100 ''::character varying
user_invite email character varying 255  
user_invite token character varying 100 ''::character varying
userdict filename character varying 255  
userdict key character varying 250  
userdict dictionary text    
userdictkeys filename character varying 255  
userdictkeys key character varying 250  
user social_id character varying 255  
user nickname character varying 255  
user email character varying 255  
user first_name character varying 255 ''::character varying
user last_name character varying 255 ''::character varying
user country character varying 3  
user subdivisionfirst character varying 255  
user subdivisionsecond character varying 255  
user subdivisionthird character varying 255  
user organization character varying 255  
user timezone character varying 64  
user language character varying 64  
user password character varying 255 ''::character varying
user otp_secret character varying 255  
user pypi_username character varying 255  
user pypi_password character varying 255