Edit

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